By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,576 Members | 1,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,576 IT Pros & Developers. It's quick & easy.

Using one subform to control another subform

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
On 7 Dec 2004 13:35:33 -0800, ke*********@gmail.com wrote:
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.


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
Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
On 8 Dec 2004 06:26:56 -0800, ke*********@gmail.com wrote:
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


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
Nov 13 '05 #4

P: n/a
Right, I needed to know how to get at the data. Since my subforms are
unbound I can't use the master link field.

Nov 13 '05 #5

P: n/a
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.

Nov 13 '05 #6

P: n/a
On 8 Dec 2004 09:37:11 -0800, "SirPoonga" <ke*********@gmail.com>
wrote:
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.


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
Nov 13 '05 #7

P: n/a
Either way, I got it working. Thanks for giving me the clue at least.

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.