I hope someone can help and has solved my exact problem with a simple solution and very little coding.
You should know that I consider myself a beginner in VB and MS Access.
I have 3 forms:
Main form FrmCustMainAboveSubJob
Caption Customer
Record Source Customer
Inside FrmCustMainAboveSubJob above is:
Subform FrmJobTblSubfrm
Caption FrmJobTbl
Record Source TblJobCarpet
Name FrmJobTbl
Source Object FrmTblJobSubfrm
With: Link Master Field: CustomerID
Link Child Field: CustomerID
Inside the subform, FrmJobTblSubfrm above is: Subform FrmQuantityXUnitRateTbl in datasheet view, (like a spreadsheet)
Caption FrmQuantityXUnitRateTbl
Record Source QuantityXUnitRateTbl
Name FrmQuantityXUnitRateTbl
Source Object FrmQuantityXUnitRateTbl
With: Link Master Field: JobID
Link Child Field: JobID
On the FrmQuantityXUnitRateTbl subform is a ComboBox
Name ItemID
Control source Item
Row Source SELECT [UnitRateLookup].[Item], [UnitRateLookup].[ItemDesc], [UnitRateLookup].[UnitRate], [UnitRateLookup].[Taxable] FROM UnitRateLookup;
The ComboBox lists Items that are associated with Jobs that belong to Customers.
What I hope I have described to you is that I have a Main Form which has a SubForm inside of it and inside of that SubForm, I have another SubForm, which has a CombBox.
Upon a Dbl Click (Double Click) of the Item Combobox, a new Form Opens using a DoCmd.OpenForm "ItemDescUnitRate". That Form, updates a table of Items that can be added to any Job for a Customer .
The problem that I have is upon returning from the Double Click of the Item ComboBox Form, ItemDescUnitRateSubform , the lasts Item that was added to the Table is not in the ComboBox dropdown, yet it is in the table. I can hit the F9 Function Key and the Form FrmQuantityXUnitRateTbl SubForm updates properly with any recent changes to the table.
Since the F9 key works, then I think I only need a Refresh, but I'm not sure if I should have a Requery?
A Requery of the Main Form takes me complete out of the 3 forms, back to theSwitchBoard, so that doesn’t work.
Anyway, my real problem is that I have Not be able to figure out where and when I can put the Refresh or Requery to make it happen automatically! Additionally, do I Refresh or Requery the Form or the ComboBox?
I have tried AfterUpdate on the Item ComboBox Form FrmQuantityXUnitRateTbl SubForm, but that does not work, because the update is not happening on that Form.
I have tried putting a Requery in the Close of the Table update Form ItemDescUnitRate, but I am unable to Address the ComboBox Form, because it is not loaded. I ran a routine to show all Form that are loaded in the Project, but only the Main Form, FrmCustMainAboveSubJob is loaded, none of the 2 subform are loaded.
If someone can help me with this problem I would really appreciate it. I have been working on it for a long time. It seems so simple, but I cannot resolve it. I hope you can follow the description of my Forms.
Thanks in advance!
You should know that I consider myself a beginner in VB and MS Access.
I have 3 forms:
Main form FrmCustMainAboveSubJob
Caption Customer
Record Source Customer
Inside FrmCustMainAboveSubJob above is:
Subform FrmJobTblSubfrm
Caption FrmJobTbl
Record Source TblJobCarpet
Name FrmJobTbl
Source Object FrmTblJobSubfrm
With: Link Master Field: CustomerID
Link Child Field: CustomerID
Inside the subform, FrmJobTblSubfrm above is: Subform FrmQuantityXUnitRateTbl in datasheet view, (like a spreadsheet)
Caption FrmQuantityXUnitRateTbl
Record Source QuantityXUnitRateTbl
Name FrmQuantityXUnitRateTbl
Source Object FrmQuantityXUnitRateTbl
With: Link Master Field: JobID
Link Child Field: JobID
On the FrmQuantityXUnitRateTbl subform is a ComboBox
Name ItemID
Control source Item
Row Source SELECT [UnitRateLookup].[Item], [UnitRateLookup].[ItemDesc], [UnitRateLookup].[UnitRate], [UnitRateLookup].[Taxable] FROM UnitRateLookup;
The ComboBox lists Items that are associated with Jobs that belong to Customers.
What I hope I have described to you is that I have a Main Form which has a SubForm inside of it and inside of that SubForm, I have another SubForm, which has a CombBox.
Upon a Dbl Click (Double Click) of the Item Combobox, a new Form Opens using a DoCmd.OpenForm "ItemDescUnitRate". That Form, updates a table of Items that can be added to any Job for a Customer .
The problem that I have is upon returning from the Double Click of the Item ComboBox Form, ItemDescUnitRateSubform , the lasts Item that was added to the Table is not in the ComboBox dropdown, yet it is in the table. I can hit the F9 Function Key and the Form FrmQuantityXUnitRateTbl SubForm updates properly with any recent changes to the table.
Since the F9 key works, then I think I only need a Refresh, but I'm not sure if I should have a Requery?
A Requery of the Main Form takes me complete out of the 3 forms, back to theSwitchBoard, so that doesn’t work.
Anyway, my real problem is that I have Not be able to figure out where and when I can put the Refresh or Requery to make it happen automatically! Additionally, do I Refresh or Requery the Form or the ComboBox?
I have tried AfterUpdate on the Item ComboBox Form FrmQuantityXUnitRateTbl SubForm, but that does not work, because the update is not happening on that Form.
I have tried putting a Requery in the Close of the Table update Form ItemDescUnitRate, but I am unable to Address the ComboBox Form, because it is not loaded. I ran a routine to show all Form that are loaded in the Project, but only the Main Form, FrmCustMainAboveSubJob is loaded, none of the 2 subform are loaded.
If someone can help me with this problem I would really appreciate it. I have been working on it for a long time. It seems so simple, but I cannot resolve it. I hope you can follow the description of my Forms.
Thanks in advance!
Last edited: