where to put Event Procedure on form to populate dropdown

[Guest]

I am putting together a list of cleaning jobs due Today - have that fine.
But also would like to populate a dropdown list based on who is TRAINED on
that job.

I attached the below code to the on Open event of the form know that is not
right - need to compare the given JOB Number for each record against who is
trained on that job - not sure what to attach the code to.


Private Sub Form_Open(Cancel As Integer)
Dim bsql As String
bsql = "Select lastname,Frequency,trainingjobnum " & "FROM
qrytrainingclassputontowho " & "WHERE trainingjobnum =""" & Me.Job & """ " &
"ORDER BY lastname"
Me.availper.RowSource = bsql
End Sub


thanks,
barb

 

[Guest]

Put this code in two places
1. On Current event of the form
2. After Update event of the Job text box

 

[Guest]

Thanks fo your input -
I added the code to both places but the dropdown list shows up the SAME for
all records based on the job number of the First record. If I manually
update the second or other job numbers the dropdown list is updated with the
correct choices.

Thinking I may need to put the code somewhere else so it sees EACH job
number for each record.

Any ideas??

Thanks again,
Barb

 

[Guest]

Any ideas!!

Thanks fo your input -
I added the code to both places but the dropdown list shows up the SAME for
all records based on the job number of the First record. If I manually
update the second or other job numbers the dropdown list is updated with the
correct choices.

Thinking I may need to put the code somewhere else so it sees EACH job
number for each record.

Any ideas??

Thanks again,
Barb

 

[Guest]

Babs,

Being dynamic code I suggest putting a requery after the on current event as
well because each record can make the source of the combo a different one.

so: me.yourcombo.requery

 

[Guest]

Sorry Just got back to this the requery helps but now another issue.

when I do the 1st record it is fine- it populates the dropdown with the
correct choices. However the one I pick goes in a a Default for the dropdown
field for the REST of the records- really want it to be blank until I select
a choice from the dropdown.


Thanks,
Barb