Cascading Combo Box Hell!

[S Jackson]

I have been working on this now for weeks and I cannot get it to work. I've
read and read all I can't find an answer.

I have two combo boxes on my form. The first combo box, cboRegion, looks up
the Region number and is bound to the Region field in my table.

The second combo box, cboFieldofc, then displays a list of people in
accordance with the Region selection and is bound to the Fieldofc field in
my table:

Afterupdate event for cboRegion:
Dim strSQL As String
'Select SurveyorId and Surveyor Name from Surveyor table by matching
Region
strSQL = "SELECT tblSurveyors.SurveyorId, " _
& "tblSurveyors.SvyFirstName & ' ' & tblSurveyors.SvyLastName AS PMName,
" _
& "tblSurveyors.Region, tblSurveyors.SvyTitle, tblSurveyors.SvyCity, " _
& "tblSurveyors.SvyPhone " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "AND tblSurveyors.SvyEmploy=No " _
& "ORDER BY tblSurveyors.svyLastName;"

Me.cboFieldofc.RowSource = strSQL
Me.cboFieldofc.Requery

What is happening is when I make a change to the Region selection on one
record and then cycle forward to the next record, the cboFieldofc combo box
is blank for that record. If I click the drop-down, it will display choices
for the Region selected in the record prior and not for the current Region
selection for that record.

Two questions:
1. What code will correct the problem?
2. Where do I put the code to correct this problem?

TIA
S. Jackson

 

[Ken Snell [MVP]]

Use the Current event to run the AfterUpdate procedure as well:

Private Sub Form_Current()
Call cboRegion_AfterUpdate
End Sub

 

[S Jackson]

Thank you, thank you! It works, finally!

Use the Current event to run the AfterUpdate procedure as well:

Private Sub Form_Current()
Call cboRegion_AfterUpdate
End Sub

--

Ken Snell
<MS ACCESS MVP>

looks "