CODEWITHSUNDEEP
vbams-accesscombobox
pal
pal

Reputation: 9

hide values in a combo box if it's already selected in previous record on a contins form

Sorry Andre your right I have edited my question.

I have a subform called frmIntContPhasesubform which has a field called Regimen, and it's row source is a table called lookupRegimen which has names of around 15 pills and the names themselves are the Primary Keys. Each patient gets a combination of five pills and when a patient is prescribed a pill I do not want that same name to show up in the next combo field choice for that patient in that subform. I have attached a picture of the form. What do you think will be a good solution for it? !

Upvotes: 0

Views: 345

Answers (1)

Thuro G
Thuro G

Reputation: 167

Assuming that the combobox's entries are drawn from a table or query, you can simply exclude values already selected before with a "WHERE x NOT IN ()" statement. Also assuming pills prescribed must be limited for each date.

tbl_Patient_Medication (would be the table linking 1 Patient with the desired Pill with fields Patient_ID_FK and Pill_Name_FK)
tbl_Pills (would contain information to the pills with field Pill_Name)

Name textboxes for patient and date as tbxPatient and tbxDate then combobox RowSource query could look like this:

SELECT Pill_Name
FROM tbl_Pills
WHERE Pill_Name Not In 
        (SELECT Pill_Name_FK FROM tbl_Patient_Medication 
        WHERE Patient_ID=[tbxPatient] AND pDate=[tbxDate]);

To work properly, add the following GotFocus event to the combobox (named cboPill):

Private Sub cboPill_GotFocus()
Me.cboPill.Requery
Me.Recalc
End Sub

Upvotes: 1

Related Questions