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.  

Thanks
Ken MilamEngineerAsked:
Who is Participating?
 
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

[SubForm].Form![Combo1].Requery
[SubForm].Form![Combo2].Requery
[SubForm].Form![Combo3].Requery
[SubForm].Form![Combo4].Requery
0
 
PatHartmanCommented:
Does the RowSource query in the combos reference the RecordID on the main form:

Select ... From ... Where RecordID = Forms!themainform!RecordID
0
 
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)
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
PatHartmanCommented:
The names of the queries aren't helpful.  Do  they look like the SQL I posted?  Please post one of them.
0
 
PatHartmanCommented:
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.
0
 
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?

Thanks!
0
 
PatHartmanCommented:
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.
0
 
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.
0
 
PatHartmanCommented:
You're welcome.  Were you able to add the requery to the macro?
0
 
Ken MilamEngineerAuthor Commented:
Solved problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.