Ken Milam
asked on
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
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
ASKER
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)
Row Source for Combo1 - q-combo-Material_Blend_Lot
Row Source for Combo2 - q-combo-Material_Blend_Lot
Row Source for Combo3 - q-combo-Material_Blend_Lot
Row Source for Combo4 - q-combo-Material_Blend_Lot
The names of the queries aren't helpful. Do they look like the SQL I posted? Please post one of them.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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].[ActiveCon trol],0))
Can you tell me what the code should look like that represents the current macro associated with the combo box?
Thanks!
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].[ActiveCon
Can you tell me what the code should look like that represents the current macro associated with the combo box?
Thanks!
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.
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.
ASKER
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?
ASKER
Solved problem
Select ... From ... Where RecordID = Forms!themainform!RecordID