Query to append one record

You can actually create a query that appends a single record to a table, but the way you create the query is a little weird. The resulting SQL statement doesn't exactly match the syntax that I described earlier, either. But it all works and would definitely be easier than trying to write a lengthy SQL statement by hand.

The trick is to create a new query that doesn't have any tables up top. Or if there is a table at the top of the query, right-click it and choose Delete so that there are no tables up top. Then choose QueryOAppend Query to change the query to an append query and specify the name of the table into which the query should append its record.

In the Field row of the QBE grid, you need to provide a value for at least one field in the table. The syntax will be name : value, where name could be any name, and value is the value that you want to store in a field. Then, in the Append To row, choose the field into which you want to place the value. For example, the query in Figure 7-9 will append a single record with the current date in the Submitted field, False in the Paid field, and No Reply in the Status field. The figure also shows the SQL statement for the query.

Even though the syntax of the SQL statement for the query doesn't look like the syntax that I describe earlier, the statement will execute just fine in VBA. Here's how you could write the code to execute that statement (and temporarily turn off warning messages):

Dim mySQL As String mySQL = "INSERT INTO Stats ( Submitted, Paid, Status )" mySQL = mySQL + " SELECT Date() AS Submitted, False AS Paid," mySQL = mySQL + " 'No Reply' AS Status"

'Note single quotation marks inside double quotation marks above.

DoCmd.SetWarnings False DoCmd.RunSQL mySQL DoCmd.SetWarnings True

0 0

Post a comment