Access Cookbook - Ken Getz [19]
Execute the query by selecting Query → Run or by clicking on the exclamation point icon.
TIP
You can preview the selected records in an action query by choosing View → Datasheet or by clicking on the Datasheet icon. The query will not be run, but you'll be able to see which records would be updated had you run the query.
For an example of updating a field in a table based on the value of a field in another table, open the tblAlbums table found in the 01-06.MDB database. Note that most of the purchase prices are null (see Figure 1-15). Open tblAlbumsUpdated, and you'll see that many of the purchase prices for the same albums have been entered (see Figure 1-16).
Figure 1-15. Many of the purchase values in tblAlbums are null
Figure 1-16. tblAlbumsUpdated contains updated purchase prices for several albums in tblAlbums
Now run the qryUpdateAlbumPrices query found in the same database (see Figure 1-17). This action query will take the PurchasePrice values from tblAlbumsUpdated and copy it into the Purchase Price field in tblAlbums for each record where the two AlbumID fields match and the price value in tblAlbums is currently null. When the query is finished, open tblAlbums again—you should see that the Purchase Price field in this table has been updated based on the values in tblAlbumsUpdated (see Figure 1-18).
Figure 1-17. The qryUpdateAlbumPrices update query in design view
Figure 1-18. The updated purchase prices for albums in tblAlbums
Discussion
You can use update queries in Access to update the values in a target table, and you can use another table to supply the values for the update. The trick is to join the two tables using a common field and to properly specify the name of the field from the source table in the Update To cell.
You can update more than one field at a time in an update query. You can also include additional fields in the query grid to further limit the rows to be updated. Drag these additional fields to the query grid and specify criteria for them. As long as you leave the Update To row blank for these columns, they will be used for their criteria only and will not be updated. Update queries are the most efficient way to make bulk changes to data; they are much more efficient than using a recordset in a VBA procedure.
1.7. Use a VBA Variable to Filter a Query
Problem
You'd like to be able to return rows in a query that have a test score greater than a specified value, which is stored in a VBA variable. When you try to use the variable in the query design grid, Access thinks it's a literal value. Is there some way to get queries to understand VBA variables?
Solution
To use a VBA variable in a query, you need to write a VBA function that returns the value of the variable as its return value and then reference the VBA function either as part of a calculation or in the criteria of a field. The only way to work with VBA in queries is to call a function. This solution shows you how to do that.
In the sample database 01-07.MDB you'll find tblScores, a table of names and test scores. The goal of the sample is to allow you to specify a cutoff value and list everyone whose scores are greater than that value.
Open the frmScores form. This form allows you to choose between a randomly selected cutoff value and a user-specified cutoff value. If you choose the user-specified cutoff value, a text box is made visible to allow you to enter the cutoff value. When you click on the "Show the results" command button, an event procedure runs that saves the cutoff value—either the randomly chosen cutoff or the user-specified cutoff—to a private variable and then runs the qryScores query.
The qryScores query references the private variable using the GetCutoff function and then returns the rows in tblScores in which the score is greater than the cutoff value (see Figure 1-19).
Figure 1-19. The sample form, frmScores, and its output, qryScores
Follow these steps to use a VBA variable in a query:
Create a select query,