ACC: How to Delete a Table Set in the RecordSource Property When Form Closes (132100)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q132100
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

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:m
    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.
Modification Type:MinorLast Reviewed:10/11/2006
Keywords:kbhowto kbProgramming kbusage KB132100
©2004 Microsoft Corporation. All rights reserved.