Using one subform to control another subform

  • Thread starter Thread starter kevin.jonas
  • Start date Start date
K

kevin.jonas

Let say I have a form called "frmMachineSpecs" witht wo subforms,
"frmSpecs" and "frmMachines".
Both subforms are in datasheet view. The first control in "frmSpecs"
is "txtOEM_No".


I want to select a record in frmSpecs. Based on the OEM Number of the
record selected I want to display all the machines that use that spec
in the frmMachines subform.
How do I do this? I think I need to change the record source somehow.
 
Let say I have a form called "frmMachineSpecs" witht wo subforms,
"frmSpecs" and "frmMachines".
Both subforms are in datasheet view. The first control in "frmSpecs"
is "txtOEM_No".


I want to select a record in frmSpecs. Based on the OEM Number of the
record selected I want to display all the machines that use that spec
in the frmMachines subform.
How do I do this? I think I need to change the record source somehow.
Click to expand...

You can actually do it using the second subform's Master Link Field
property. Change it to

[Forms]![frmMachineSpecs]![frmSpecs].Form![txtOEM_No]

This assumes that frmSpecs is the name *of the Subform control*, not
just the name of the form within that control.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
That works. However, since a subform only has an on exit and on enter
event I don't think I am going to be able to do what I want.

I got it to work in the on exit even of the subform:
[Forms]![frmMachineSpecs]![frmMachines].Form.RecordSource = "SELECT
machines.[OEM Equiv Code], machines.[Machine] & "" "" & [Model] AS
MakeModel, machines.Option AS [Option/Linkage], machinenames.name " & _
"FROM machinenames INNER JOIN machines ON
machinenames.machine = machines.Machine " & _
"WHERE machines.[OEM Equiv Code]=""" &
[Forms]![frmSpecs]![sfSpecs].Form![OEM_Code] & """"


Now, only if I could do this when I select the item in the first
subform. I tried putting this code in the subform form's on current
(datasheet view) but I get this error: Run-time error '2455': You
entered an expression that has an invalid reference to the property
Form/Report
 
That works. However, since a subform only has an on exit and on enter
event I don't think I am going to be able to do what I want.

I got it to work in the on exit even of the subform:
[Forms]![frmMachineSpecs]![frmMachines].Form.RecordSource = "SELECT
machines.[OEM Equiv Code], machines.[Machine] & "" "" & [Model] AS
MakeModel, machines.Option AS [Option/Linkage], machinenames.name " & _
"FROM machinenames INNER JOIN machines ON
machinenames.machine = machines.Machine " & _
"WHERE machines.[OEM Equiv Code]=""" &
[Forms]![frmSpecs]![sfSpecs].Form![OEM_Code] & """"


Now, only if I could do this when I select the item in the first
subform. I tried putting this code in the subform form's on current
(datasheet view) but I get this error: Run-time error '2455': You
entered an expression that has an invalid reference to the property
Form/Report
Click to expand...

My suggestion involves NO CODE AT ALL. The second subform will display
values related to whichever record is selected on the first subform.

If you want to change the second subform's Recordsource you can do so
in the AfterUpdate and/or Current events of the first subform's Form;
it's not an event on the Subform control, but instead a form event
within the form within the control.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Right, I needed to know how to get at the data. Since my subforms are
unbound I can't use the master link field.
 
I tried creating a new form, bound it to a query that gets all the
information. I tried your suggestion witht he master link field. The
second subform shows no records.
 
I tried creating a new form, bound it to a query that gets all the
information. I tried your suggestion witht he master link field. The
second subform shows no records.
Click to expand...

My suggestion assumes bound subforms (it doesn't matter whether the
mainform is bound or unbound), and that you're selecting a record in
the first subform. I'm not sure why it isn't working in your case!

Could you post the Recordsource properties of the two subforms, the
name of each Subform control, and the exact Master and Child Link
Fields of both subforms?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top