Mass Update of RecordSet

[FarmboyEsq]

Hi,

Once upon a time there was a form, based on a multi-table (but updateable) query, that includes a "check-box" on each row and a button on the form buttons that is labeled "Check All".

Clicking "Check All" spins through all the rows of the recordset (until eof) marking each check box as "True". But, because there are 8000+ rows in this somewhat large recordset, it takes a long time to spin through them.

So, I modified the VBA code to use a RecordSetClone object to do the update. Now the form doesn't spin through the rows. Instead, it sort of flickers and flashes until (I presume) the work is done. This takes too long as well (in my estimation).

What I would love to have is a single "Update" statement that I can run against the recordset, instead of looping through the rows.

Any suggestions? The chkbox field is in one of tables behind the query. Perhaps I should just update the table and then do a requery? (This would lose any pending work on the screen, I suppose.)

This is my current, somewhat lame solution...

Dim rstc As DAO.Recordset
Set rstc = Me.RecordsetClone

rstc.MoveFirst

Do Until rstc.EOF
rstc.Edit
rstc!ChkBox = False
rstc.Update
rstc.MoveNext
Loop

rstc.MoveFirst
rstc.Close

Thanks.

Stg,

 

[TheAceMan1]

How are ya FarmboyEsq . . .

An [blue]Update[/blue] query should do:

[blue]   Dim db As DAO.Database, SQL As String
   
   SQL = "UPDATE [purple][B][I]YourTableName[/I][/B][/purple] " & _
         "SET [purple][B][I]YourTableName[/I][/B][/purple].ChkBox = False;"
   db.Execute SQL, dbFailOnError
   Me.Requery[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PWise]

missing line

Dim db As DAO.Database, SQL As String
Set db = currentdb
SQL = "UPDATE YourTableName " & _
"SET YourTableName.ChkBox = False;"
db.Execute SQL, dbFailOnError
Me.Requery

 

[FarmboyEsq]

Thanks! I will try this tonight when I get back to the problem, and let you know.

Stg