question of style 1

[robwistar]

I am writing some code in which I am emptying a particular field in every record in a given table. My code works fine, but I don't like the fact that I have to rely on an error to break out of my while loop. Is there another way to determine when I am at the end of the table?

On Error GoTo Err_Form_Open
'set toShip column to zero for all records

DoCmd.GoToRecord acDataForm, "ShipComponentsForm", acFirst

While (True)
Forms!ShipComponentsForm.ToShip = Empty
DoCmd.GoToRecord acDataForm, "ShipComponentsForm", acNext
Wend


Err_Form_Open
Exit Sub

 

[Norris68]

Why not use a SQL statement on the underlying table? e.g.

DoCmd.RunSQL "UPDATE ShipComponents SET ToShip = NULL"

It will be far quicker than using VBA - this single line will update all records in the table. Use a WHERE clause if you want to limit it to a specific subset.

 

[robwistar]

Thanks! Is SQL generally considered the proper way to edit tables, forms, etc.?

And how do you execute large blocks of SQL code as opposed to just one line as you showed? Can you make SQL modules, or do you just run it from within the VBA modules?

Rob