I came across this problem because I wanted to create a button that would open a form and change its record source. The problem was that when the user went to close the form, Access prompted the user to save the changes. First, I did not want the changes to be saved and second, if I wanted the changes saved, I would not want the decision to be left up to the user. The solution was to create an instance of the form, and change its properties. An instance only exists while it is open. In fact it is not possible to save its changes. To learn about this do the following;
1. Create two identical tables with 1 field (txtOne). a. Name one table table1 and the other table2
2. Enter a couple of records with different values in each table.
3. Create a form with a textbox and set the textbox control source to txtOne. Set the Forms record source to table1. Save the form as frmform1.
4. Close the form and create the following procedure.
Sub testFormClass () Dim frm1 As Form Set frm1 = Form_frmForm1 Frm1.RecordSource = ôTable2ö Frm1.Visible = True End Sub
Run the procedure and the following happens; You will see the form that you created, but it uses the data from table2. You can add data and browse records. When you close this form, you will not be prompted to save changes and the original form1 still has its original record source. "Table1"