ACC2000: How to Delete a Table Set in RecordSource When the Form Closes

This article shows you how to delete a table set in the RecordSource property of a form when you close the form. This technique is useful if you need to set the RecordSource property of a form to a temporary table at run time and then delete the temporary table when the form is closed.

The following technique runs two sample Microsoft Visual Basic functions from command buttons on a form that is based on one table, but requires a second table to be substituted at run time. The first sample function sets the RecordSource property of the form to a second table. The second function then closes the form and deletes the second table. The RecordSource property of the form then reverts back to the table upon which it was based when the form was created.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Select the Customers table, and then on the Edit menu, click Copy.
3. On the Edit menu click Paste.
4. In the Paste Table As dialog box, type Cust2 in the Table Name box.
5. Repeat steps 2 and 3 for the Customers form.
6. In the Paste As dialog box, type CustForm.
7. Open the Cust2 table and delete the first 20 records.
   
   The Cust2 table now contains only 71 records.
8. Create a module and type the following line in the Declarations section if it is not already there:
   Option Explicit
9. Type or paste the following procedures:

   Function SetNewTable()
     Forms!CustForm.RecordSource = "Cust2" 
   End Function
   
   Function CloseAndDelete ()
     Forms!CustForm.RecordSource = ""
     DoCmd.Close acForm, "CustForm"
     DoCmd.DeleteObject acTable, "Cust2"
   End Function
   					

10. Open the CustForm form in Design view and set the form's properties as follows:
    Caption: CustForm
    RecordSource: Customers
11. Create two new command buttons in the Detail section of the CustForm form and set their properties as follows:
    Button0
    Name: Button0
    Caption: Set New Table
    On Click: =SetNewTable()
    
    Button1
    Name: Button1
    Caption: Close Form
    On Click: =CloseAndDelete()
12. Save the CustForm form, and then view the form in Form view.
13. Click the Set New Table button to change the underlying table to the Cust2 table.
    
    Notice that the number of records indicated at the bottom of the form is now 71 records.
14. Click the Close Form button.
    
    Note that the form closes and the Cust2 table itself is deleted. The RecordSource property returns to "Customers," which is the original value.

For more information about the DoCmd object, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type macro actions and methods of the docmd object in the Office Assistant or the Answer Wizard, and then click Search to view the topic.