Multitable form

[Scott Matheny]

Is it possible to have a subform that changes its source
table based on a selection in the main form? For example:
if I am using this form to look through Type A & B tests,
can the sub-form only show Type A tests based on the
selection "Type A tests" in a field on the main form?

 

[Al Borges]

Hi Scott:

Of course- if the Type A and Type B tests involve totally different table
structures, then you could place 2 subforms on your Parent Form and simply
make one visible depending on the selection. You can place the code in the
After Update event of, say, the combobox:

If combotest.value = "Type A" Then
Subform_A.Visible = True
Subform_B.Visible = False
Else
Subform_B.Visible = True
Subform_A.Visible = False
End If

If you have 2 separate similar tables (with like column structures) or both
types (A and B) are in the same table, you can use the same subform:

For 2 separate, but similar tables-

If combotest.value = "Type A" Then
Subform_A.RecordSource = "SELECT TypeATable.* FROM TypeATable;"
Else
Subform_B.RecordSource = "SELECT TypeBTable.* FROM TypeBTable;"
End If

For 1 table with both recordsets-

If combotest.value = "Type A" Then
Subform_A.RecordSource = "SELECT TypeATable.* FROM TypeATable WHERE
TypeATable.[TypeRecordField] " & Chr(34) & "Type A" & Chr(34) & ";"
Else
Subform_A.RecordSource = "SELECT TypeBTable.* FROM TypeBTable WHERE
TypeBTable.[TypeRecordField] " & Chr(34) & "Type B" & Chr(34) & ";"
End If

There- all you have to do now is to pick your schenario.

Regards,
Al

 

[Scott Matheny]

I input the code and changed the names to match the names
of my field and sub-forms, but it is not working. Is
there a special way I need to name anything? What am I
doing wrong?

 

[Al Borges]

Hi Scott:

Make sure that your referenced subforms correctly named, s.a. "Subform_A"
and "Subform_B" in my example... remember to pay attention to how they are
embedded-

i.e. if directly embedded to the Parent form-

"Forms![parentform]![subform_A].Visible = "True"

or if it's a second tier subform-

"Forms![parentform]![Subform1stTier]![subform_A].form.Visible = "True"

If that's OK, then pay attention to whether you are referencing the combobox
correctly. See if the combotest.value truely does equal "Type A" by placing
the following temporary statement into your code:

MsgBox combotest.value

Let us know what you get...

Regards,
Al



then most likely your reference to the

 

[Guest]

"Private Sub TestType_AfterUpdate()

MsgBox Tables![TestListFMW]![TestType].Value

If TestType.Value = "Tensile" Then
Forms![TestListFMW]![TensileData_subform].Visible = True
Forms![TestListFMW]![FatigueData_subform].Visible = False
Else
Forms![TestListFMW]![FatigueData_subform].Visible = True
Forms![TestListFMW]![TensileData_subform].Visible = False
End If
End Sub"

This is the exact code I am using. TestListFMW is my
master table and TestType is a field within that table.
The subforms are getting their data from related tables,
and both work fully. I'm trying to do this within a
textbox that has a lookup wizard, and if the value
is "Tensile," I want it to display only
TensileData_subform. Likewise for Fatigue. The text box
and the field it's source is from are both named TestType
(I checked the name of the TextBox under properties to be
sure.) I know very little about VBA, if that isn't
already apparent; so really spell it out for me.

Thanks a billion,
-Scott