|| Feedback | A query to Append new records ||
Delete queries are useful, not only because they can be linked to a button on a form, thereby automating the deletion process, but also because they are much quicker than manually deleting the records in an open table.
Would you like to learn more?
There is nothing special about creating a delete query. A normal query can be created to select all of the records which are required for deletion. When you have carefully checked that the selection criteria are correct, use the menu option Query | Delete Query to change the type.
The following example will delete all of the bookings records which relate to before 1 April 2005 and which have been fully paid. If your delete query is intended to delete all records and therefore no criteria are required, you must nevertheless add a field to the definition.
Delete Bookings : Delete Query (design)
|Criteria:||Is Not Null||<#01/04/2005#|
If you just wish to preview the effect of running the query, click on the DataSheet View button. To make it perform its operation, either click on the Run button (in design mode) or Open the query.
When using Access 2000 to design a form, the button wizard ignores the delete queries from the list of available items - i.e. only select, append and update queries are shown. Once the wizard has been completed (having temporarily used another query name) and a button created it is possible to edit the property linked to the OnClick event and edit the code to change the query name to the required delete query. The button will then function normally.
If the dialog boxes which warn that you are about to delete records are considered to be unecessary in your program, they can be temporarily switched off by editing the button on-click code and using the SetWarnings command.
|Private Sub Btn_Del_Bkgs_Click()
On Error GoTo Err_Btn_Del_Bkgs_Click
Dim stDocName As String
DoCmd.SetWarnings False ' Turn warning dialog boxes off
DoCmd.Hourglass True ' Change cursor to hourglass symbol
stDocName = "Delete Bookings"
DoCmd.OpenQuery stDocName, acNormal, acEdit
An update query will amend data in existing records. It does not append new records to a table. For example it may be required that a series of old records which contain distances measured in miles are to be converted in to kilometers. The existing field values will be updated and overwritten. Having designed and tested a query, while in design mode select the Query | Update Query menu option
Update MilesToKm : Update Query (design)
Another common use of an update query is to reset all of the values of a field back to a common value or to blank. To delete all of the values in a field, simply set its 'Update to:' criteria as "".
|file: acc_deleteupdate.htm||Page last modified Nov06||© MeadInKent.co.uk 2006|