ACC: How to Delete Table Set in RecordSource When Form Closes

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates how you can delete a table set in a form's RecordSource property when you close the form. This technique is useful if you need to set a form's RecordSource property to a temporary table at run time and then delete the temporary table when the form closes.

MORE INFORMATION

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

The method described below runs a sample user-defined Visual Basic function from a command button on a form based on one table. The sample function sets the form's RecordSource property to an empty string (""), and then deletes the table set in the form's RecordSource property when the form closes.

To delete a table set in a form's RecordSource property when the form closes, follow these steps:

1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0).

2. Select the Customers table, and 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. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit

8. Type the following procedure:

   In Microsoft Access 7.0 and 97:

   Function CloseAndDelete ()

           Forms!CustForm.RecordSource = ""
           DoCmd.Close acForm, "CustForm"
           DoCmd.DeleteObject acTable, "Cust2"
         End Function
   
       In Microsoft Access 2.0:
   
         Function CloseAndDelete ()
           Forms!CustForm.RecordSource = ""
           DoCmd Close A_FORM, "CustForm"
           DoCmd DeleteObject A_TABLE, "Cust2"
         End Function
   
   

9. Open the CustForm form in Design view and set the form's properties as follows:

   Caption: CustForm RecordSource: Cust2

10. Create a new command button in the CustForm form's detail section and set its properties as follows:

    Command Button:

             Name: Button0
             Caption: Close Form
             OnClick: =CloseAndDelete()
    
    

11. Save the CustForm form, and then view the form in Form view.

12. Click the Close Form button. Note that the form closes, the Cust2 table is deleted from the form's RecordSource property, and the Cust2 table itself is deleted.

REFERENCES

For more information about the DoCmd object, search for "DoCmd object" using the Microsoft Access 97 Help Index.