How to connect a form to a different table

[mplsdoors]

I have a great form that I use for Table1 that I want to use for newly created Table2. The 2nd table is very similar to the first table in that it has the exact same fields.

How can I connect the "copy of the great form" to Table2? It keeps pulling data from Table1.

Thanks in advance,
Michelle

 

[mickeyg]

Michelle,

Here is one possible way:
On the form's OnOpen event you could set the RecordSource property based on some variable you created.

If MyDataSource = "table2" then me!RecordSource = "table2"
Else
'Do Nothing
EndIf

Just set MyDataSource before you open the form (via a button?).

Hope this gets you in the right direction.

Mickey

 

[pjm]

If you want the copy of your form to always point to your table2 why not set the record source permanently? (Select the form control button in design view, right click to get the properties window and change the rec source on the data tab.)

 

[dgriffin]

I have a simular application which has to access fiscal year data saved in individual tables for each year. I declare a global variable called sFiscalTable in a separate module (under the MODULE tab, not in the form):

Option Compare Database
Option Explicit
Public sFiscalFile As String
...

and then set the record source of each form, in the ON OPEN even, that needs to use the currently selected table:

Me.RecordSource = "SELECT [" & sFiscalTable & "].Field1, [" & sFiscalTable & "].Field2, [" & sFiscalTable & "].Field3, [" & sFiscalTable & "].Field4, ORDER BY [" & sFiscalTable & "].Field1;"

where Field1, Field2, etc. are the names of all the fields in the table that your form needs. The user selects an option on the main menu that allows them to select the fiscal year that they wish to work with, which of course sets the global variable.

HTH
Dan