Add tables to be used for a form, once it has been created. 1

[surfside1]

I have a form I want to use as a generic form for multiple tables so that I don't need to create a separate form for each table. All tables have the same format and their forms will also. I already have a lead-in form that gives the users a combobox where they select their facility (separate table for each) and that drives what data they see in another drop down. The record they select will identify the table and record numbers that will be used in later processes of the database.

Can you add additional tables to a form once it has been created. All fields will match the ones in each table.

Also, what is an example of the syntax of a Where clause when you DoCmd.OpenForm. Is field coded as table.field?
Do you have an example.

Thanks,
Surfside1

 

[Remou]

You can change the record source of a form at any time, which means that you can use different tables and queries. You can refer to controls on a form as Me.ControlName or fields in the recordset clone for the form. Is this the kind of thing you are thinking of, or have I missed your point?

 

[Zion7]

Since all fields match, IDENTICALLY, then yes, as Remou said, use the RecordSource property of the form, to change its Bound table at will.
From another form,
Forms!frmName.Recordource = "tblWorld"
From the form itself
Me.Recordsource = "SELECT * FROM tblWorld WHERE..."


Docmd.OpenForm "frmName",,,"txtCountry Like 'A*'"

DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

 

[JerryKlmns]

surfside1,

Since all tables have the same format, I would suggest to append all records of all tables to one table with an extra field. That extra field (indexing is strongly recommended) would define the records belonging to each category that now lay in seperate tables. Then the recordsource of your form (a valid SQL statement like the 2nd example of Zion7) could be created on the fly from the lead-in form you already have.

 

[surfside1]

I appreciate all your responses.
This is the code I have when a value in a combobox is selected. What it doesn't like is how I am referencing the form name to use as a string in the OpenForm.
Any suggestions?
Thanks,
Surfside1
------------------------------------------------------
Private Sub combo_Test_Click()
Dim str_FacilityName As String
Dim str_TableName As String
Me.combo_Facility.SetFocus
'combobox for Facilities
str_FacilityName = Me.combo_Facility.Text
str_TableName = "tbl_2005_" & str_FacilityName
'name of table
Dim str_ComboRec As String
Me.combo_Test.SetFocus
str_ComboRec = Me.combo_Test
'combobox of all records in table
MsgBox str_TableName & str_ComboRec & " = Is the record it will display on form", vbCritical
'above displays the record I want
Dim str_FormName As String
str_FormName = Forms!frm_SpendData
'THIS IS WHAT IT DOES NOT LIKE "TYPE MISMATCH"
str_RecordSource = str_TableName
'table in database
DoCmd.OpenForm str_FormName & "." & str_RecordSource, , , ID = " & [Forms]![frm_SpendMenu]![combo_Test]"

End Sub

 

[surfside1]

Also, I assume that if you had created a form based on a particular table and that value is in RecordSource of the properties of the form, that gets overwritten when you code it in OpenForm.
(If I ever get this thing to work)
Thanks,
Surfside1

 

[Remou]

I think what I might do is use OpenArgs:
[tt]DoCmd.OpenForm str_FormName , , , , , , "Select * From " & strTableName & " Where ID=" & [Forms]![frm_SpendMenu]![combo_Test] [/tt]

With a little code on the form, perhaps:

Private Sub Form_Open(Cancel As Integer)
    If Me.OpenArgs <> "" Then
        Me.RecordSource = Me.OpenArgs
    Else
    'Error
        Cancel = True
    End If
End Sub

 

[Zion7]

SurfSide1, you've referenced your form wrong, for the open argument. You're using the Forms Collection, not correct, just use the name (with quotes).

str_FormName = "frm_SpendData"

 

[surfside1]

I did the OpenArgs and everything is working great!
Thanks so much for all your help,
Surfside1