Find record based on PK in Subform

[Kathleen]

Table set up:
tblPatient
PatID (PK)
Other variables

tblCase
PatID (FK)
StateID (PK)
Other variables

I have a form using tblPatient with a subform using tblCase. Both forms are
in form view. On the main form I have two unbound combo boxes to search for
the correct record, lkpPatient finds the record by PatID, lkpStateID finds
the record by StateID. The first box works perfectly. The second one,
lkpStateID, finds the correct patient record but displays the subform record
for the first StateID the patient has rather than the StateID selected in the
combo box. I understand why it is finding that first record (the
rst.FindFirst bit) but every time I try to modify it to find the StateID
selected it gives me an error. I tried to add a second box that would
display and hold the StateID value but as soon as the After Update event
fires the value is changed to be the first StateID for that PatID.

Do you have any ideas about what I need to change so I can display the
selected record? Thanks.

Here is the relevant code for the combo box.

Private Sub lkpStateID_Enter()
lkpStateID.RowSource = "SELECT tblCase.PATID, tblCase.STATEID FROM tblCase;"
End Sub

Private Sub lkpStateID_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.Recordset.Clone
rst.FindFirst "[PatID] = '" & Me![lkpStateID] & "'"
If Not rst.EOF Then Me.Bookmark = rst.Bookmark

Exit_lkpStateID_AfterUpdate:
rst.Close
Exit Sub
End Sub

 

[Allen Browne]

For the State combo, you are wanting to filter the main form so it contains
only the patients who have a match in the subform? If so, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html