Microsoft KB Archive/132100

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

Article ID: 132100

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



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.

 Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 2.0). Select the Customers table, and 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> Create a module and type the following line in the Declarations section if it is not already there:

Option Explicit

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

Caption: CustForm

RecordSource: Cust2

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

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

<div class="references_section">