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