Help With Cascading Combo Boxes

[S Jackson]

My apologies for posting this again, but I posted originally in the wrong
forum and don't expect a response:

Here is what I have:

Main table:
tblCaseInfo
-CaseID
-CaseName
-Region
.. . . etc.
Other tables:
tblSurveyors (this table contains names and addresses of employees)
-SurveyorID
-SvyFirstName
-SvyLastName
-Region
.. . .etc.
tblPM's
-PMID
-CaseID
-SurveyorID
The relationship b/t tblPM and CaseInfo is one-to-one, as each case has only
one PM (program manager). tblSurveyors contains all employees with titles
designating them as "surveyors" or "program managers." Each case has
several "surveyors", but only one "program manager."

The trouble I am having is setting up my form for the user to select a
program manager. I have set up a test query and test form to see if I can
get this to work. The query looks like this:
SELECT tblCaseInfo.*, tblPMs.*
FROM tblCaseInfo LEFT JOIN tblPMs ON tblCaseInfo.CaseId = tblPMs.CaseID;

I don't want for them to cycle through the huge list of employees. So, on
the form, I have inserted a combo box to look up Region. After the user
selects Region, an AfterUpdate Event populates a second combo box with only
"program managers" from that Region, stores the SurveyorID in tblPM, and
then displays the selected Program Manager's name. It works fine. However,
if you move to a new record and make a selection in the combo box for
Region, it wipes out the program manager information in the 2nd combo box in
the record previous - when you click back to that record, the box is blank.
I checked the tblPM and it is storing the proper information. Also, when
you reopen the form, the proper information is displayed. The problem only
occurs when you update or input new records. How can I fix this?

FYI: The 2nd combo box has a control source of tblPMs.SurveyorID and is
bound to Column 1.

Here is the code that populates the box in the AFterUpdate event of the
first combo box:
Dim strSQL As String
'Select Surveyor Id and Surveyor Name from Surveyor table by matching
Region
strSQL = "SELECT tblSurveyors.SurveyorId, " _
& "tblSurveyors.SvyFirstName & ' ' & tblSurveyors.SvyLastName AS PMName,
" _
& "tblSurveyors.Region, tblSurveyors.SvyTitle, tblSurveyors.SvyPhone, "
_
& "tblSurveyors.SvyCity " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Forms!Form2.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "AND tblSurveyors.SvyEmploy=No " _
& "ORDER BY tblSurveyors.svyLastName;"

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

Any help is much appreciated!

 

[S Jackson]

I think I have figured out. If someone reads this and sees that I should be
doing something differently, please post.

I placed the following code in the OnCurrent Event of the Form:

'Synchronise FieldOfc combo with existing Region
cboFieldofc.RowSource = "SELECT tblSurveyors.SurveyorId, " _
& "tblSurveyors.SvyFirstName & ' ' & tblSurveyors.SvyLastName AS PMName,
" _
& "tblSurveyors.Region, tblSurveyors.SvyTitle, tblSurveyors.SvyPhone, "
_
& "tblSurveyors.SvyCity " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region.Value & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "AND tblSurveyors.SvyEmploy=No " _
& "ORDER BY tblSurveyors.svyLastName;"

Me.cboFieldofc.Requery

Thanks anyway