Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Mass Update of RecordSet

Status
Not open for further replies.

FarmboyEsq

Programmer
Apr 2, 2007
17
US
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,
 
How are ya FarmboyEsq . . .

An [blue]Update[/blue] query should do:
Code:
[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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
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
 
Thanks! I will try this tonight when I get back to the problem, and let you know.

Stg
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top