Retrieving Complete Recordset When Returning To A Page

[EarlCPhillips]

I have an application using three pages; Events, People and Sponsors. All
manage environmental conservation events for a local conservation group.
When displaying the People, Event or Sponsor page, I use a combo box to
select any record within the recordset for display on that page. If I
transfer to the People page from the Events page, I display the person
associated with the most recently displayed Event, if anyone is associated
with that Event, otherwise I display the first record in the recordset. When
I transfer from the People page back to the Event page, if the person
previously displayed was associated with an event, I display that event,
otherwise I display the first event in the recordset.

If I transfer pages without using a key to find any associated record in
another table, it brings up a recordset of ALL members of the table but
displays the first record in that recordset. If I transfer pages using a key
to find an associated record in another table, it displays a recordset of ONE
member and displays the record matching the key. The code is as follows:

Set objConn = CurrentProject.Connection
SQLStmt = "SELECT lngzEventID, lngz1stContact, *" & _
" FROM tblEvent" & _
" WHERE lngz1stContact =" & dblContactID & ";"

where dblContactID is the key of the People page displayed.

rsSponsor.Open SQLStmt, objConn, adOpenForwardOnly, adLockReadOnly
If rsSponsor.EOF = True Or rsSponsor.BOF = True Then
dblEventID = 0
stDocName = "frmEventInfo"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stDocName = "frmPeopleInfo"
DoCmd.Close (acForm), stDocName, acSavePrompt
rsSponsor.Close
Else
dblEventID = rsSponsor(0)
stDocName = "frmEventInfo"
strWhere = "[tblEvent]![lngzEventID] = " & dblEventID
DoCmd.OpenForm stDocName, , , strWhere, , , stLinkCriteria
stDocName = "frmPeopleInfo"
DoCmd.Close (acForm), stDocName, acSavePrompt
rsSponsor.Close
End If

going from the People form to the Event form.

Is there a way to fetch the entire recordset from the destination table but
display the desired record that matches the associated record on the prior
page? Examples of code would help.

This helps a local environmental group instead of the food bank I usually
support.

EarlCPhillips
Former Mainframe Programmer Learning Access
To Feed The Hungry And Protect The Environment