Microsoft Access VBA Macro Programming

Because it does not return records, it is run as a command. In its simplest form, it

appears as:

UPDATE MyTable SET CustomerName = "Richard"

This updates the table MyTable and changes the value of the field CustomerName to

Richard throughout the table. Normally in this query you would use a criterion:

UPDATE MyTable SET CustomerName = "Richard"

WHERE (((CustomerName)="Shepherd"))

This assumes that MyTable has a field called CustomerName. This will change the

CustomerName field to Richard where the CustomerName field has a value of Shepherd.

You can also update multiple fields using multiple criteria:

UPDATE MyTable SET CustomerName= "Richard", Company = "MGH"

WHERE (((CustomerName)="Shepherd") AND ((Company)="RBS"))

This assumes that MyTable has fields called CustomerName and Company. The query will

set the CustomerName field to Richard and the Company field to MGH where the

CustomerName has an existing value of Shepherd and the Company field has an existing

value of RBS.

You can use the Query Design window to build an Update Table query. It is the same as

the earlier Select query, but you must click the Append query icon in the Query Type group

of the ribbon.

It is a good idea to look at the results in a data sheet view (by clicking the View icon in the

Results group of the ribbon) before you run the query. This will show you what will be

updated without actually enacting the query. If anything is wrong at this point you have not

damaged the database.

To run this from within VBA, you can use the DoCmd object (see Chapter 16 for more

information on this object):

Sub MakeTableQuery()

DoCmd.SetWarnings False

DoCmd.OpenQuery "MyUpdateQuery"

DoCmd.SetWarnings True

End Sub

Notice that the DoCmd object has been used to switch the warning messages off and then

switch them back on afterward. This is because by running an Update query the warning

message will normally be displayed to tell you that you are about to delete records. If you are

running this as part of a VBA procedure, you will not want this message appearing and

halting the execution of your code.

162 Microsoft Access 2010 VBA Macro Programming