filter combo box in subform withe a combo box in main form.

[Mike]

Hi,
I have a form frmJob, with a subform frmWorker

frmJob contains a series of details about a job including a combo box
(Company).
Workers will be assigned a company when they are created.
As part of a job i want to add a company to a job, by selecting the company
from the combo box. I then want this selection to limit the workers in the
subform to just those who work for that company. I can then select the right
workers for each job.
I am almost there with this but am having trouble updating the form when a
different company selction is made on a new record. When i open the form for
the first time the combo box does as i wish. I then have to close it and
reopen to selct a different company to limit the records.
Any help is appreciated.
If i have been unclear about my problem in any way i will try to explain
further.
thanks

 

[BruceM via AccessMonster.com]

It would have helped had you posted the method by which it works initially,
and what you have tried for subsequent records. Presumably you are setting
the Row Source for the subform combo box when the form loads, or something
like thqt. You could instead use something like the following in the main
form's code module:

Private Function SetRow

Dim lngCo as Long
Dim strSQL as String

lngCo = Me.CompanyID

strSQL = "SELECT [CompanyID],[CompanyName] " & _
"FROM YourTable " & _
"WHERE [CompanyID] = " & lngCo & _
" ORDER BY [CompanyName]"

Me.SubformControlName.Form.ComboBoxName.RowSource = strSQL

End Function

In the form's Current event, and in the After Update event of the Company
combo box on the main form:

Call SetRow