How to populate combobox from subform entry

[Myron60]

Hi y'all,
I have been trying to populate a subform combobox with a select query.
I have a main form text box called [JobNumber] and a subform called JobRates, you choose the SSN# from a combobox that populates the rest of the subform (viewed as datasheet). I have another subform called [JobTimeExpenses] that has a combobox where you choose the SSN# again.
My problem is, I would like the [JobTime subform combobox to only show the chosen SSN#'s that equal the ones in the [JobRates] subform. I have managed to get what I want to work some, when I click on the JobTime subform cbo I get the SSN# that is in the JobRates subform, but if I change the JobNumber on the Main form, the JobTime cbo still has the first SSN#. I can press F9 and it works correctly, but I have tried every other requery location to no avail.
Below is my SQL query:

SELECT JobRates.PersonnelID, Personnel.FirstName, Personnel.LastName FROM Personnel INNER JOIN JobRates ON Personnel.PersonnelID = JobRates.PersonnelID WHERE (((JobRates.JobNumber) Like [Forms]![MSR Main]![JobNumber])) ORDER BY JobRates.JobNumber, JobRates.PersonnelID;

Any help would be greatly appreciated.
Thanks

 

[lameid]

Comboboxes do not automatically update there lists when referenced controls change so you need to requery the combobox when the data changes. Two examples to requery are below... On the value that is referenced, you need code on the after update event to requery the combo box control that has the query that references. You may also want to requery when the record changes or the ON CURRENT event of the FORM.

Forms!<form Name>!<Control_Name>.Requery  'this is requerying 
                                          'when the control is on main form or no subforms

Forms!<form Name>!<Subform control name>.Form!<Control_Name>.Requery 'this is requerying 
                                                                 'when the control is on a subform

 

[MajP]

On the value that is referenced, you need code on the after update event to requery the combo box control that has the query that references. You may also want to requery when the record changes or the ON CURRENT event of the FORM.

So that you do not have to requery on multiple events of different objects, it is a lot simpler/reliable to do this on the OnEnter event of the combobox.

 

[lameid]

For MajP's suggestion you could easily use the Me reference for the current form...

Me!<controlName>.Requery

Depending on the layout of your screen and runtime of your query, you may find one method better than the other for usability, but all things being equal otherwise, MajP's solution is easier to maintain.

 

[Myron60]

Thanks, that worked beautifully. I had tried a lot of different events but not the On Enter, I did the CODE(Me!<controlName>.Requery)and it worked the way I wanted it to right out of the gate.
Thanks again.