if statement to check for duplicate entries with list box

cedtech23

Registered User.
Local time
Yesterday, 21:02
Joined
Jan 28, 2006
Messages
25
I have a problem where in a table called tblPatientLanguage
that contains three fields

PatientLanguageID
PatientID
LanguageID

a patient who is identified by PatientID can speak several languages
identified by the LanguageID.

The problem is with my current form and code

Code:
For Each varSelected In Me!lstLanguage.ItemsSelected
            rs.AddNew
            rs("PatientID") = [Forms]![frmPatientDemographics]![PatientID]
            rs("LanguageID") = Me!lstLanguage.ItemData(varSelected)
            rs.Update
Next varSelected


when the user click on the command button called cmdOK it will put the
data in the table "tblPatientLanguage" and corresponding fields. without
checking to see if there is already a duplicate languageID that corresponds to the
same patientID in the table

This has lead to duplicates in the table

example
PatientID = 9

if I look in the table I will see 7 entries that say

PatientID = 9 and LangaugeID = 7

I want to prevent this from happening with and if statement
I could make both the PatientID and LanguageID primary keys
this would prevent this from happening. But I would rather do it in
the VBA code? This way in will move thorugh the selections without poping an error and the person inputing the date. Any ideas sorry for being long winded. I attached the
database.

PS. If looking at the database you have to use form frmPatientDemographics and click on the language link
 

Attachments

Users who are viewing this thread

Back
Top Bottom