How to fill a subform based on a different subform

[Jerry8989]

I have two subforms on a form. When the user clicks on the 1st Subform the 2nd one will populate depending on what was selected on the first one. I have a click event but the 2nd subform isn't being populated correctly.
Is this possible?

 

[RuralGuy]

It sounds like you don't have the LinkChild/MasterFields set up correctly.

 

[Jerry8989]

How to do it using VBA

How can I do it when it's two subforms? The second subform will only let me link to the main form not the other subform. Can it be done in VBA?

Thanks

 

[RuralGuy]

Create an invisible TextBox on the MainForm with the ControlSource set to =SubFormControl1.Form!ControlWithTheValueYouWant. Then Set the LinkMasterField of SubForm2 to point to this hidden TextBox. You don't even need to use any click event. The hidden textbox will always contain the value from the "Current" record in SubForm1. Clicking on a different record in SubForm1 will change the "Current" record and should automatically update SubForm2. Post back if you need further assistance. I use this technique all of the time.

 

[Jerry8989]

Rural Thanks for the help so far.
But when I add the hidden text box it doesn't show up in the list of Master Links. I think the drop down is only showing the fields that populate the main form. I use a query to populate the main form and an Id from that query is linked with the first subform. How can I get the second subform to see that hidden text box?

Thanks again

 

[RuralGuy]

Just type it in! The LinkMasterField is assumed to be on the parent form.

 

[Jerry8989]

RuralGuy Thank you very much. I think that did it.
Thank you so much it was such a headache. I'm mostly a web developer and messing with Access subforms isn't the easiest thing.

 

[RuralGuy]

I completely understand. Glad I could help.