Need Help With Link Table

[S Jackson]

Sorry, this is long: I have been struggling with this for several days.
Maybe someone can help. 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.
tblCaseSurveyors (this is a link table b/t tblCaseInfo and tblSurveyors. It
is a one-to-many relationship)
-CaseSvyID
-CaseID
-SurveyorID

What I am trying to do is set up another table. This one will be called
tblPM's. It will look like this:
-PMID
-CaseID
-SurveyorID
The relationship is one-to-one with CaseInfo, 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. 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;

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. 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!

 

[tina]

one-to-one table relationships are somewhat rare, and i think not needed in
this instance. suggest you add SurveyorID to tblCases, as a foreign key
field. if the field name makes you uncomfortable because you only intend to
use it for program managers, just change the field name to something like
"PMSurID". at the form level, you can use a combo box to restrict the
available entries to program managers only. eliminating the separate table
will simplify data management tasks for you.

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.

that is a common complaint when using a "filtered" combo box in a continuous
or datasheet form. the data is not really "wiped out", it just doesn't show
because the available values in the droplist have been changed. there are
ways to get around it, but it's a real bear to try to explain in a post (for
me, anyway!). if you really want to pursue a work-around, i have a sample
form - somewhere - that i did for somebody who posted the same question in
the newsgroups a year or so ago. i can try to dig it up to send to you...or
somebody who's better at posting instructions may help you out.

hth