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


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210250
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

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 is closed.

MORE INFORMATION

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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbhowto kbinfo kbusage KB210250
©2004 Microsoft Corporation. All rights reserved.