Changing and Deleting Table Records

Any Access update query or delete query will also convert nicely to VBA. For example, suppose you keep track of which new customers you've sent e-mail to by using a Yes/No field named SentWelcome in a table. Customers who have been sent the message have True in that field; customers who haven't been sent the message have False in that field. For the sake of example, say that this table also has a field named Email that's either the Text or Hyperlink data type that contains each customer's e-mail address.

Now suppose you want to write some code that automatically changes the contents of the SentWelcome field to True for all AOL customers. You create an update query that includes the SentWelcome field and set its Update To row to True to change the contents of that field to True. Then you also need a criterion to prevent the change from occurring in all records. In this case, where you want to update only records that have @aol.com in the Email address field, the criterion expression would be InStr([Email],'@aol.com')>0. The entire update query would look like Figure 7-10. (Because it's a small query, I managed to fit both the Query Design and SQL views of the query into one figure.)

Figure 7-10:

A sample update query (two views).

[email protected]

UPDATE Customers 5ET Customers.SentWelcome = True

k|

WHERE ((In5tr([Email]/@)aol. com')>0))j

1

Figure 7-10:

A sample update query (two views).

JttNG/

Don't experiment with a delete query or an update query against a table that contains data that you can't afford to lose or ruin. Your best bet would be to work in a copy of your database so that you don't have to worry about losing any important information.

By using the standard method of getting a SQL statement into a variable and executed from VBA, the code that's needed to turn off warnings, do the update, and turn warnings back on looks like this:

'Build SQL statement into string variable named mySQL.

Dim mySQL As String mySQL = "UPDATE Customers"

mySQL = mySQL + " SET Customers.SentWelcome = True" mySQL = mySQL + " WHERE (InStr([Email],'@aol.com')>0)"

'Hide warning and do the update. DoCmd.SetWarnings False DoCmd.RunSQL mySQL DoCmd.SetWarnings True

If you want your code to delete records from a table, just create a delete query that specifies the records to be deleted and put its SQL statement into VBA code. For example, Figure 7-11 shows an Access delete query in both Query Design view and SQL view. That particular query deletes all records from a table named PaidOrderSummary.

Vba List Records

Figure 7-11:

A sample delete query.

Figure 7-11:

A sample delete query.

As with any action query, to get the SQL statement to execute from VBA and delete all records from the table, you need to execute the SQL statement with DoCmd.RunSQL. Because this particular SQL statement is so short, there's no need to store it in a variable in chunks. The following statement is sufficient:

DoCmd.RunSQL "DELETE PaidOrderSummary.* FROM PaidOrderSummary"

0 0

Post a comment