Duplicate over multiple Fields

[unclefink]

I was wondering if someone might be able to help me with something i'm having some difficulty with. I have a table that is going to track people. First Name, Last Name, and Date of Birth in three separate fields.

I dont want to be able to add the same person in the table. How do i do this when the data is in separate fields.

 

[John Big Booty]

You could use the DCount() in the form's Before Update event, to check it the combination of First Name, Family Name and DOB, already exists.

 

[unclefink]

Forgive me for asking but how would i apply this? Ive got the table set which has fields for fname, lname, and dob.

Ive created a form for said table and im in the property sheet for the form. Ive got dcount() in the before update but ive got a feeling im missing something. How do i get it to check the specific fields?

 

[boblarson]

It would be:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If DCount("*", "TableNameHere", "[fname]=" & Chr(34) & Me!fname & Chr(34) & _
   " And [lname]=" & Chr(34) & Me!lname & Chr(34) & _
   " And [dob]=#" & Me!dob & "#") > 0 Then
     Msgbox "Person already exists."
     Cancel = True
   End If
End Sub

 

[jdraw]

Another option, in your table, create a Unique Composite Index with the fields

FirstName LastName and DOB


see this article for more info
http://en.allexperts.com/q/Using-MS-Access-1440/Creating-Unique-Value-check.htm

 

[unclefink]

Thank you very much for the prompt responses. I used the "Dcount" option, made a couple minor changes to work with the form and walaaa, it works exactly as i'd like. Thank you very much for the help, greatly appreciated.

 

[unclefink]

Bob,

I know this post is old and solved on the initial problem; however I've seem to come to an issue hopefully to be an easy fix.

I'm trying to use the same "dlookup" code in a table that has the primary key from a related table rather than the actual verbiage. Is there something I need to change in the code to make this work or is it possible?

 

[boblarson]

If it is numeric then it doesn't get the quote characters - CHR(34).

 

[unclefink]

Ok, i've tried this a few different ways and its not working. It may because I am trying to combine different types of data in the dlookup but here is what I have.

Table includes multiple fields with the two specifics I dont want duplicated.

Subject Name and Case Number.

In the table Subject name is showing a primary key and the case number is text.

If DCount("*", "tblcases", "[subjectname]=" & Me!Subjectname & _
   " And [casenumber]=" & Chr(34) & Me!casenumber & Chr(34) & "#") > 0 Then
     MsgBox "case already exists."
     Cancel = True
   End If

 

[unclefink]

I think I just figured it out.

If DCount("*", "tblcases", "[subjectname]=" & Me!SubjectName & _
" And [casenumber]=" & Chr(34) & Me!CaseNumber & Chr(34) & "?") > 0 Then
MsgBox "case already exists."
Cancel = True
End If

In comparison, I removed the # sign at the end of the code, where I placed a "?" mark for explanation purposes!!!!