Basing a subform on a selection from either of two subforms

[Kurt]

frmHospital has three subforms: fsubDoctors, fsubNurses, and
fsubPersonDetails.

fsubDoctors & fsubNurses (both in continuous view) list people from
tblPeople. They have the same SQL record source except one shows
people whose Role = "Doctor"; the other shows people whose Role =
"Nurse."

When the user clicks on a name in either subform, I'd like that
person's full record to appear in the 3rd subform, fsubPersonDetails.
To do this I've added a hidden text box on frmHospital (txtPersonID)
which I populate with this code in the OnCurrent event of the two
continuous subforms:

Me.Parent!txtPersonID = Me![PersonID]

fsubDetail is linked to frmHospital with PersonID (Child Field) and
txtPersonID (MasterField).

When a hospital has both doctors and nurses, only one of the subforms
updates the txtPersonID when I select a name. Clicking a name from the
other subform (when a person in the other form was already selected)
seems to have no affect. I think the OnCurrent code is competing to
populate txtPersonID.

I can move code out of the OnCurrent event and put it in the click
event of a control in the continuous subform, but of course this means
fsubPersonDetails won't automatically refresh when the user changes
records on the main form (frmHospital).

Any suggestions?

 

[Kurt]

I managed to resolve this by also adding this code to the OnClick
event of a control (which shows the person's names) in each of the
continuous subforms:

Me.Parent!txtPersonID = Me![PersonID]

(I also kept this in the OnCurrent event of both subforms.)

Everything seems to be performing as desired.

But I have a related issue, which I think can be overcome despite not
being able to control the order which Access loads the two subforms.

I have a search form (fsubSearch) with a listbox to search people and
hospitals. Column 0 of the listbox shows the person (PersonID) and
column 1 shows the hospital (HospitalID). Column 1 is the bound
column, so when the user selects an item, it finds the record with the
matching HospitalID and pulls it up on frmHospital. (frmSearch and
frmHospital are actually subforms on frmMain).

Like so:

###

Dim sbfrm As Form

' set focus to main form
Forms!frmMain.SetFocus

With Forms!frmMain!frmHospital
.SetFocus
Set sbfrm = .Form
End With

' find the desired record
With sbfrm.RecordsetClone
.FindFirst "[PracticeID]= " & Me.lstResults.Column(1)
If Not .NoMatch Then sbfrm.Bookmark = .Bookmark
End With

###

But how can I capture the values of *both* Column 0 and Column 1. This
way, it can call up the matching Hospital, and *also* select the
matching person (in either fsubDoctors or fsubNurses), and show that
person's details in fsubPersonDetails. If selecting the matching
record in fsubDoctors and fubNurses is too tricky, I can just populate
txtPersonID (with the PersonID from Column 0) on frmHospitals, and
fsubPersonDetails should update automatically.

I also need to handle the possibility that some hospitals many not
have any people (i.e., PersonID is Null), in which case txtPersonID
should be null (and hopefully fsubPersonDetails will default to a
newrecord). I suspect some sort of If statement could handle this:

If IsNull(lstResults.Column(0)) Then ' there are no people in this
hospital
Forms!frmMain!frmHospitals.Form!txtPersonID.Value = ""
Else
...

###

Thank you for any pointers.