Prev: RowSource in Access 2007
Next: New record if not found on form
From: Mike on 18 Jan 2010 05:23
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
From: BruceM via AccessMonster.com on 18 Jan 2010 07:20
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

 | 
Pages: 1
Prev: RowSource in Access 2007
Next: New record if not found on form