Update combo box data on subform after update to combo on main form header

I have a combo box located in my form's (f-MainForm) header.  It's used to find records on the main form based on the associated RecordID control.

I have a sub-form with a Parent/Child relationship based on the RecordID.  

I have four combo boxes on the sub-form (Combo1....Combo4).  The contents of the combo boxes on the sub form are supposed to be determined by the main form's RecordID.

When I open the form the first time, and select a record with the header combo, the sub form combos populate with appropriate data.  When I use the header combo box subsequently, the main form info is changed, but the sub form's combo details do not change.  

Do I need an after update event on the header combo?  If so, is it a refresh or requery, and can you pls tell me how to specify the combo on the sub form from the header's combo.  

Ken MilamEngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Does the RowSource query in the combos reference the RecordID on the main form:

Select ... From ... Where RecordID = Forms!themainform!RecordID
Ken MilamEngineerAuthor Commented:
No, the combos on the sub form are based on  queries -
Row Source for Combo1 - q-combo-Material_Blend_Lot (Material1)
Row Source for Combo2 - q-combo-Material_Blend_Lot (Material2)
Row Source for Combo3 - q-combo-Material_Blend_Lot (Material3)
Row Source for Combo4 - q-combo-Material_Blend_Lot (Material4)
The names of the queries aren't helpful.  Do  they look like the SQL I posted?  Please post one of them.
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Ken MilamEngineerAuthor Commented:
Adding an After Change Event for the combo box located in the main form's header seems to have done the trick


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AfterChange is not the correct event to use for this purpose.  AfterChange runs for every single character typed.  There is no need to requery the combos until an item is selected so the AfterUpdate event is a better choice.
Ken MilamEngineerAuthor Commented:
Thanks, Pat.  I've always struggled with Access' events (what happens when).

When I originally added the record finder combo box (is there a better name for this type of combo??) at the header of the page, Access automatically created an After Update Macro.  In order to add the Requery code to the After Update event (instead of After Change), I'll need to combine it with code equivalent to the Macro that Access baked into the record finder combo box.

Macro code is
Record = First
Where Condition="[MaterialID] = " & Str(Nz([Screen].[ActiveControl],0))  

Can you tell me what the code should look like that represents the current macro associated with the combo box?

Sorry, I don't use macros but you should be able to add the requery command after the existing item.

OR, you could use the convert to code button on the ribbon and then modify the generated code.

PS - you never gave me anywhere near enough information to solve the problem for you.  Knowing that you were using a generated macro would have been critical information.
Ken MilamEngineerAuthor Commented:
Sorry, Pat.  I didn't realize Macro vs. Code was key to our discussion.  My apologies.  For what its worth, the Covert to Code button on the ribbon is greyed out.  Thanks so much for your continued support and assistance to people like me that are trying to learn Access.
You're welcome.  Were you able to add the requery to the macro?
Ken MilamEngineerAuthor Commented:
Solved problem
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.