Lose selected subform record with unrelated delete

[Impecunious]

I've got a tab control that drives several things, and I'm running into

a problem.

The user searches for a particular patient using unbound controls on a
Patient search tab, and a subform displays the associated results.
When the user selects the patient they want in the subform's datasheet,
this selection drives the results shown in the other two main tabs of
the form, like Patient Details and Transactions. The problem is,
whenever the user
deletes a particular transaction in the Transactions subform, I lose
the primary key
of the patient they previously selected from the Patient Search
subform. Instead, the user
sees the transactions of the very first patient in the underlying
table.

For my transactions sub form, for example, I make this association as
such:
Me.subfrm_transaction_transactions.Form.Filter = "PATIENT_ID = " &
Me.FRM_SUB_PATIENT.Form!PATIENT_ID

I've created a variable to capture this patient ID, and this remedies
part of the problem, but I'm really hoping there's a more elegant way
to preserve the selected patient in the subform, regardless of any
actions the user makes elsewhere in the main tabs. This issue doesn't
happen for edits or adds I make to records in my Transactions tab -
only for deletes.

Thanks!

 

[Graham Mandeno]

Hi Impecunious (yeah - me too

If I understand you correctly, this is a classic case of synchronising
subforms, where you want to select a particular record on one subform which
is in continuous (or datasheet) view and other subforms on the main form
need to show details for that selected record.

This trick will make it happen automatically...

Add a hidden textbox to your main form, named "txtSelectedPatient", and set
its ControlSource to:
=FRM_SUB_PATIENT.Form!PATIENT_ID
(Access will add a bunch of redundant square brackets, but don't worry about
that)

Now, for your Transactions (and other) subform control(s), set the following
link properties:
LinkMasterFields: txtSelectedPatient
LinkChildFields: PATIENT_ID

Et voilà!

As you select different records in the list subform, the textbox on the main
form will change its value, and this will automatically requery the other
subforms.