• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 722
  • Last Modified:

Access 2010 Duplicate Entry Warning Message

Hi,

I am having a little difficulty warning users when then enter a duplicate entry for FirstName & LastName into a form.

I have used the following code from a sample access database that works fine for checking against FirstName & LastName but I also need to check for duplicate entries of Date Of Birth as well.

'WORKS FINE FOR FIRSTNAME & LASTNAME ONLY
If (DCount("*", "[qryPassengersExtended]", "[Passenger_ID]<>" & Nz([tblHAPassengers.Passenger_ID], 0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]), "'", "''") & "'") > 0) Then
'DOESNT CHECK CORRECTLY FOR DOB
'If (DCount("*", "[qryPassengersExtended]", "[Passenger_ID]<>" & Nz([tblHAPassengers.Passenger_ID], 0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]), "'", "''") & "'") > 0 And [qryPassengersExtended]![DOB] <> Me.DOB) Then
'MsgBox "Poss Dupl"

Dim strMsg As String
Dim iResponse As Integer
        ' Specify the message to display.
       strMsg = "Passenger with the same First Name, Last Name and D.O.B. already exists! Do you want to create this as a new Passenger?" & Chr(10)
       strMsg = strMsg & "Click Yes to Continue."
        ' Display the message box.
       iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Booking?")
If iResponse = vbNo Then
       Me.Undo
Else

.....save form etc

End If

Any ideas anyone?
0
andrewpiconnect
Asked:
andrewpiconnect
  • 4
  • 3
1 Solution
 
mbizupCommented:
Try this...


IF  DCount("*", "[qryPassengersExtended]", "[Passenger_ID]<>" & Nz([tblHAPassengers.Passenger_ID], 0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]), "'", "''") & "' AND [DOB] = #" & Me.DOB & "#") > 0  THEN
0
 
andrewpiconnectAuthor Commented:
Sorry, It still doesnt work
0
 
mbizupCommented:
How specifically?

Error message (which one )?
Incorrect data pulled by the query (what are the incorrect results and what would correct data include )?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
andrewpiconnectAuthor Commented:
No Error Message.

If the first and last names are the same but the DOB is different it still prompts the Msg Box.

If you enter a completely different first and last name and DOB the Msg Box stills shows.

I have used this line of code at the beginning of the If statement and it works fine throughout but it only checks the First and Last Name. I really would prefer to have the DOB checked as well.

Many thanks
0
 
mbizupCommented:
That explanation helps...

See how this works:


IF  DCount("*", "[qryPassengersExtended]", "[Passenger_ID]<>" & Nz([tblHAPassengers.Passenger_ID], 0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]), "'", "''") & "' AND Format([DOB],'yyyy-mm-dd') = '" & Format(Me.DOB, "yyyy-mm-dd") & "'" ) > 0  THEN 

Dim strMsg As String
Dim iResponse As Integer
        ' Specify the message to display.
       strMsg = "Passenger with the same First Name, Last Name and D.O.B. already exists! Do you want to create this as a new Passenger?" & Chr(10)
       strMsg = strMsg & "Click Yes to Continue."
        ' Display the message box.
       iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Booking?")
If iResponse = vbNo Then
       Me.Undo
Else

' ETCETERA ...

Open in new window

0
 
andrewpiconnectAuthor Commented:
Excellent....works a treat. Many thanks and have a great weekend
0
 
mbizupCommented:
Glad to help out!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now