JamieRhodes
asked on
Validate for duplicate values during data entry
frmAddMedication allows a user to select a med, enter dosage, date, dr. Before the record is saved I want to check to ensure that resident is not already on that med. This form is bound to tblResidentMedication. Investigating the before update event looks great however, I am just not sure how to code.
Rey Obrero (Capricorn1)
What is resident? and which field on the table you want it check for existence?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Jamie,
sounds like you're talking about patients resident at a hospital or similar? If so, then presumably you have some way of tracking patient 'visits' or 'episodes' or whatever terminology your place uses. If you have a unique way of identifying each visit, rather than each patient, you could try setting a primary key (or other unique index) across the fields MedicationID and VisitID. Just thought, if there is some reason why the patient would only ever visit once, then you can make the index unique across the fields PatientID and MedicationID. If you could have multiple visit from the same patient, but no way of identifying them, you are going to have difficulty detecting when a duplicate PatientID / MedicationID is genuine (in some cases it may be historical and not relevant to the current visit).
With the unique index in place, the system will fail every time you try to enter a duplicate VisitID / MedicationID; the error message is not particularly user-friendly but this at least a simple solution.
s46.
sounds like you're talking about patients resident at a hospital or similar? If so, then presumably you have some way of tracking patient 'visits' or 'episodes' or whatever terminology your place uses. If you have a unique way of identifying each visit, rather than each patient, you could try setting a primary key (or other unique index) across the fields MedicationID and VisitID. Just thought, if there is some reason why the patient would only ever visit once, then you can make the index unique across the fields PatientID and MedicationID. If you could have multiple visit from the same patient, but no way of identifying them, you are going to have difficulty detecting when a duplicate PatientID / MedicationID is genuine (in some cases it may be historical and not relevant to the current visit).
With the unique index in place, the system will fail every time you try to enter a duplicate VisitID / MedicationID; the error message is not particularly user-friendly but this at least a simple solution.
s46.
ASKER
solution46
I am not needing to track visits, just the meds the patient is on.
capricorn1
Resident/patient is the person. the table that the info is going into is tblResidentMedications
Nestorio
I will try it .
I am not needing to track visits, just the meds the patient is on.
capricorn1
Resident/patient is the person. the table that the info is going into is tblResidentMedications
Nestorio
I will try it .