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

ID: Q132100

The information in this article applies to:

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

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 closes.

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.

MORE INFORMATION

The following technique runs a sample Visual Basic function from a command button on a form that is based on one table. The sample function sets the RecordSource property of the form to an empty string (""), and then deletes the table set in the RecordSource property of the form when the form closes.

  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 detail section of the CustForm
     form 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 RecordSource property of the form, 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.

Additional query words: timing event code

Keywords          : kbusage FmsEvnt PgmObj 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998