Prevent duplicate selection from combo box on continuous form

[Kurt Heisler]

On my continuous subform, I would like to prevent the user from
selecting the same item twice from a combo box (called cboAB). The
subform is based on tblAntibiotics.

I made the row source of the combo box this:

SELECT tblLookupAntibiotics.AB
FROM tblLookupAntibiotics LEFT JOIN tblAntibiotics ON
tblLookupAntibiotics.AB = tblAntibiotics.AB
WHERE (((tblAntibiotics.AB) Is Null))
ORDER BY tblLookupAntibiotics.AB;

And added this to the combo box's After Update event:

Me.cboAB.Requery

But when you select an item and move to the next record in the
continuous form, that item still shows up on the list of available
items. It disappears only when you've closed main form (and subform)
then reopened it.

What am I missing?

 

[Paul Shapiro]

You need to requery the combo box row source after the data has been saved
to the subform's table (tblAntibiotics?). The combo box AfterUpdate event is
too soon for that because the data is not in the table yet. Try the
subform's AfterUpdate event and see if that's late enough. You might have to
put in a small delay time, but I would expect the form's AfterUpdate to
work.

You can also manually requery with the <F9> key when the combo box has the
focus, which can help with testing.

 

[Kurt Heisler]

Requerying the subform's AfterUpdate event worked!

But I notice that when you *delete* a record in the continuous form,
the item that was deleted doesn't reappear in the box until you close
and reopen the form (or hit F9).

Weird how requerying the subform works for one condition (adding a
record to the table) but not the other (deleting a record from the
table).

Ideas? Thanks.

 

[Paul Shapiro]

You can requery the combobox in the subform's AfterDelConfirm event too:
"The AfterDelConfirm event occurs after the user confirms the deletions
and the records are actually deleted or when the deletions are canceled."
Delete events are more complicated than update events, since multiple rows
can be simulataneously deleted, etc. Check the online help for more details
if it doesn't work as you want.

 

[Kurt Heisler]

Once again - thanks! Requerying the combo box at the subform's
AfterDelConfirm event solved the problem.

And in what is hopefully my last and final question:

The row source for the combo box doesn't consider the Patient ID,
which means if "Antibiotic ABC" is selected for Patient 1, it will not
be available to select for Patient 2.

(There is a one-to-many between tblPatients and tblAntibiotics.
frmPatients (based on tblPatients) is used to enter Patient data, and
fsubAntibiotics (based on tblAntibiotics) is used to select the
antibiotics he's on.

tblPatients
---------------
PatientID (PK)
LastName ..

tblAntibiotics
------------------
AntibioticID (PK)
PatientID
AB

I tried adding a PatientID WHERE clause to the row source like so, but
the query always comes up empty (no antibiotics listed at all):

SELECT tblLookupAntibiotics.AB, tblAntibiotics.PatientID
FROM tblLookupAntibiotics
LEFT JOIN tblAntibiotics
ON tblLookupAntibiotics.AB = tblAntibiotics.AB
WHERE (((tblAntibiotics.PatientID)=[Forms]![frmPatients]![PatientID])
AND ((tblAntibiotics.AB) Is Null))
ORDER BY tblLookupAntibiotics.AB;

It's now occurring to me that perhaps I need a many-to-many between
tblPatients and tblAntibiotics, because one patient can have many
antibiotics, and one antiobiotics can be assigned to many patients.

Hmm ... perhaps that's my problem.