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

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


Mike wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1