Microsoft KB Archive/210250

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

Article ID: 210250

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.

 Start Microsoft Access and open the sample database Northwind.mdb. Select the Customers table, and then on the Edit menu, click Copy. On the Edit menu click Paste. In the Paste Table As dialog box, type Cust2 in the Table Name box. Repeat steps 2 and 3 for the Customers form. In the Paste As dialog box, type CustForm .</li> Open the Cust2 table and delete the first 20 records.

The Cust2 table now contains only 71 records.</li> Create a module and type the following line in the Declarations section if it is not already there:

Option Explicit

</li>  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 </li> Open the CustForm form in Design view and set the form's properties as follows:

Caption: CustForm

RecordSource: Customers

</li> 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

</li> Save the CustForm form, and then view the form in Form view.</li> 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.</li> 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.</li></ol>

<div class="references_section">