| | Feedback | A query to Append new records | Using Queries | | ||
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.
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 Query Tools Design ribbon option Query Type | Delete 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) | ox | |||
 |
||||
| Field: | Booking_Ref | Date_InvPaid | Date_Return | |
| Table: | Bookings | Bookings | Bookings | |
| Delete: | where | where | where | |
| Criteria: | Is Not Null | <#01/04/2005# | ||
| Or: | ||||
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 to design a form, the button wizard omits 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 has been created it is possible to edit the VB code linked to the OnClick event change the query name to the required delete query. The button will then function perform the required task.
If the dialog boxes which warn that you are about to delete records are considered to be unecessary in your database application, they can be 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 DoCmd.Hourglass False DoCmd.SetWarnings True Exit_Btn_Del_Bkgs_Click: Exit Sub Err_Btn_Del_Bkgs_Click: MsgBox Err.Description Resume Exit_Btn_Del_Bkgs_Click End Sub |
An update query will amend data in existing records. It does not append new records to a table. For example a series of telephone numbers in a Customers table, may need to be updated with new area codes. The existing field values will be updated and overwritten. Having designed and tested a query, while still in design mode select the Query Type | Update ribbon option.
| Update MilesToKm: Update Query (design) | ox | |||
| |
||||
| Field: | Booking_Ref | Km_Start | Km_Return | |
| Table: | Bookings | Bookings | Bookings | |
| [Km_Start]/0.621 | [Km_Return]/0.621 | |||
| Criteria: | ||||
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 Mar14 | © MeadInKent.co.uk 2014 |
