Checking multiple fields for Duplicate

Guest · Oct 16, 2007

I have been struggling with this code and trying to learn from other threads.
I am trying to check three fields on a form for duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any records
already in the table that match ALL three, then a warning message is given
and you are taken to that record. So far I have the code working for one
field, but I get problems when I try to add other fields. I am a novice and
probably 'over my head', but I seem so close..any suggestions or help is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

OldPro · Oct 17, 2007

I have been struggling with this code and trying to learn from other threads.
I am trying to check three fields on a form for duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any records
already in the table that match ALL three, then a warning message is given
and you are taken to that record. So far I have the code working for one
field, but I get problems when I try to add other fields. I am a novice and
probably 'over my head', but I seem so close..any suggestions or help is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

Guest · Oct 17, 2007

Thank you for your response...and I adjusted the code to the following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And "[strFirstName]= ' "
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName, FirstName and
DOB are the first three fields on the form. If I have the Before Update on
the LastName field, then the other two are currently blank. Should I have the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

OldPro said:
I have been struggling with this code and trying to learn from other threads.
I am trying to check three fields on a form for duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any records
already in the table that match ALL three, then a warning message is given
and you are taken to that record. So far I have the code working for one
field, but I get problems when I try to add other fields. I am a novice and
probably 'over my head', but I seem so close..any suggestions or help is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Click to expand...

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Pieter Wijnen · Oct 17, 2007

you have to many spaces in the criteria & you can solve the null date issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]= '" &
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Kathy said:
Thank you for your response...and I adjusted the code to the following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And "[strFirstName]= '
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName, FirstName
and
DOB are the first three fields on the form. If I have the Before Update on
the LastName field, then the other two are currently blank. Should I have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

OldPro said:

I have been struggling with this code and trying to learn from other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message is
given
and you are taken to that record. So far I have the code working for
one
field, but I get problems when I try to add other fields. I am a novice
and
probably 'over my head', but I seem so close..any suggestions or help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Click to expand...

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Guest · Oct 17, 2007

Thank you for helping...but I am now receiving a syntax error and I just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]= '" &
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

Pieter Wijnen said:
you have to many spaces in the criteria & you can solve the null date issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]= '" &
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Kathy said:

Thank you for your response...and I adjusted the code to the following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And "[strFirstName]= '
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName, FirstName
and
DOB are the first three fields on the form. If I have the Before Update on
the LastName field, then the other two are currently blank. Should I have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

OldPro said:

I have been struggling with this code and trying to learn from other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message is
given
and you are taken to that record. So far I have the code working for
one
field, but I get problems when I try to add other fields. I am a novice
and
probably 'over my head', but I seem so close..any suggestions or help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Pieter Wijnen · Oct 17, 2007

Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format: yyyy-mm-dd

Pieter

Kathy said:
Thank you for helping...but I am now receiving a syntax error and I just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

Pieter Wijnen said:

you have to many spaces in the criteria & you can solve the null date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Kathy said:

Thank you for your response...and I adjusted the code to the following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And "[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName, FirstName
and
DOB are the first three fields on the form. If I have the Before Update
on
the LastName field, then the other two are currently blank. Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

I have been struggling with this code and trying to learn from other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message is
given
and you are taken to that record. So far I have the code working for
one
field, but I get problems when I try to add other fields. I am a
novice
and
probably 'over my head', but I seem so close..any suggestions or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) >
0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Douglas J. Steele · Oct 17, 2007

"Pieter Wijnen"

Access only recognizes US Format: mm/dd/yyyy & Military Format: yyyy-mm-dd

<picky>
Actually, it'll recognize any unambiguous format, such as dd mmm yyyy. The
problem comes when there's ambiguity.
</picky>

Guest · Oct 17, 2007

Sorry I am still with errors...I am receiving 'compile error/syntax error'
...this is how it looks: (But Private Sub txtLastName...is highlighted in
yellow and stLinkCriteria is all in red). I am really a novice as you can
tell, but just seems like I am almost there...thank you for your continued
help.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

--
Kbelo

Pieter Wijnen said:
Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format: yyyy-mm-dd

Pieter

Kathy said:

Thank you for helping...but I am now receiving a syntax error and I just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

Pieter Wijnen said:

you have to many spaces in the criteria & you can solve the null date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Thank you for your response...and I adjusted the code to the following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And "[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName, FirstName
and
DOB are the first three fields on the form. If I have the Before Update
on
the LastName field, then the other two are currently blank. Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

I have been struggling with this code and trying to learn from other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message is
given
and you are taken to that record. So far I have the code working for
one
field, but I get problems when I try to add other fields. I am a
novice
and
probably 'over my head', but I seem so close..any suggestions or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) >
0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Douglas J. Steele · Oct 17, 2007

The code that assigns the value to stLinkCriteria is supposed to be all on
one line, not the 3 lines that appear due to word-wrap.

Not sure about why the initial line would cause an error. See whether fixing
the other problem fixes it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Kathy said:
Sorry I am still with errors...I am receiving 'compile error/syntax error'
..this is how it looks: (But Private Sub txtLastName...is highlighted in
yellow and stLinkCriteria is all in red). I am really a novice as you can
tell, but just seems like I am almost there...thank you for your continued
help.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

--
Kbelo

Pieter Wijnen said:

Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format:
yyyy-mm-dd

Pieter

Kathy said:

Thank you for helping...but I am now receiving a syntax error and I
just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

:

you have to many spaces in the criteria & you can solve the null date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Thank you for your response...and I adjusted the code to the
following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And
"[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName,
FirstName
and
DOB are the first three fields on the form. If I have the Before
Update
on
the LastName field, then the other two are currently blank. Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

I have been struggling with this code and trying to learn from
other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message
is
given
and you are taken to that record. So far I have the code working
for
one
field, but I get problems when I try to add other fields. I am a
novice
and
probably 'over my head', but I seem so close..any suggestions or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria)

0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You
will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Guest · Oct 17, 2007

Thank you...I have put the code all on one line, but when I do that Access
removes the () from the Date as copied below:

stLinkCriteria = "[strLastName]= '" & SID & "'And [strFirstName]= '" &
strFirstName & "'And [dtmDOB] = " & Format(Nz(dtmDOB, Date), "\#yyyy-mm-dd\#")

If I try to put it back in, then I get errors. At this time, using the above
code with the Date() missing...it does not give me an error, but does not not
check for duplicates...in other words, I can enter data now, but if I
purposely put in duplicate data, the code does not catch it. Thank you so
much for the help.
--
Kbelo

Douglas J. Steele said:
The code that assigns the value to stLinkCriteria is supposed to be all on
one line, not the 3 lines that appear due to word-wrap.

Not sure about why the initial line would cause an error. See whether fixing
the other problem fixes it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Kathy said:

Sorry I am still with errors...I am receiving 'compile error/syntax error'
..this is how it looks: (But Private Sub txtLastName...is highlighted in
yellow and stLinkCriteria is all in red). I am really a novice as you can
tell, but just seems like I am almost there...thank you for your continued
help.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

--
Kbelo

Pieter Wijnen said:

Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format:
yyyy-mm-dd

Pieter

Thank you for helping...but I am now receiving a syntax error and I
just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

:

you have to many spaces in the criteria & you can solve the null date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Thank you for your response...and I adjusted the code to the
following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And
"[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName,
FirstName
and
DOB are the first three fields on the form. If I have the Before
Update
on
the LastName field, then the other two are currently blank. Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

I have been struggling with this code and trying to learn from
other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message
is
given
and you are taken to that record. So far I have the code working
for
one
field, but I get problems when I try to add other fields. I am a
novice
and
probably 'over my head', but I seem so close..any suggestions or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria)

0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You
will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Stefano · Oct 17, 2007

Hi Kathy,
I have only two text field two check and no data field.
Which part I have to remove in my case?

Thanks for your help,
Stefano.

Kathy said:
Thank you...I have put the code all on one line, but when I do that Access
removes the () from the Date as copied below:

stLinkCriteria = "[strLastName]= '" & SID & "'And [strFirstName]= '" &
strFirstName & "'And [dtmDOB] = " & Format(Nz(dtmDOB, Date),
"\#yyyy-mm-dd\#")

If I try to put it back in, then I get errors. At this time, using the
above
code with the Date() missing...it does not give me an error, but does not
not
check for duplicates...in other words, I can enter data now, but if I
purposely put in duplicate data, the code does not catch it. Thank you so
much for the help.
--
Kbelo

Douglas J. Steele said:

The code that assigns the value to stLinkCriteria is supposed to be all
on
one line, not the 3 lines that appear due to word-wrap.

Not sure about why the initial line would cause an error. See whether
fixing
the other problem fixes it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Kathy said:

Sorry I am still with errors...I am receiving 'compile error/syntax
error'
..this is how it looks: (But Private Sub txtLastName...is highlighted
in
yellow and stLinkCriteria is all in red). I am really a novice as you
can
tell, but just seems like I am almost there...thank you for your
continued
help.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

--
Kbelo

:

Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format:
yyyy-mm-dd

Pieter

Thank you for helping...but I am now receiving a syntax error and I
just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And
"[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) >
0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

:

you have to many spaces in the criteria & you can solve the null
date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And
"[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Thank you for your response...and I adjusted the code to the
following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And
"[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am
wondering
(guessing?) that I am not handling Null values. The LastName,
FirstName
and
DOB are the first three fields on the form. If I have the Before
Update
on
the LastName field, then the other two are currently blank.
Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

On Oct 16, 5:45 pm, Kathy <[email protected]>
wrote:
I have been struggling with this code and trying to learn from
other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are
any
records
already in the table that match ALL three, then a warning
message
is
given
and you are taken to that record. So far I have the code
working
for
one
field, but I get problems when I try to add other fields. I am
a
novice
and
probably 'over my head', but I seem so close..any suggestions
or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation",
stLinkCriteria)

0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr &
"You
will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Guest · Oct 17, 2007

Hi Kathy,

When you were only checking the last name, you could use the "LastName
BeforeUpdate" event. But with 3 criteria, you don't want to check for
duplicates before the other 2 criteria are filled in.

So I would move the code to the "Form BeforeUpdate" event.

One other thing. Access has a nasty habit of naming controls the same name
as the field they are bound to. So I always change the name of the control.
If the field name is "strLastName", then I name the control on the form
"tbLastName" (tb = textbox). Then I know if I am looking at a field or a
control.

Anyway, take a look at the code. Hopefully this will get you moving in the
right direction.

'------beg code-------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "You will now be taken to the record."
mButtons = vbOKOnly + vbExclamation
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Exit Sub
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'always check the NoMatch property after a find

' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE

' I hate negative logic!!!
If Not rsc.NoMatch Then
Me.Undo
MsgBox mPrompt, mButtons, mTitle
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'------end code-------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:
Thank you...I have put the code all on one line, but when I do that Access
removes the () from the Date as copied below:

stLinkCriteria = "[strLastName]= '" & SID & "'And [strFirstName]= '" &
strFirstName & "'And [dtmDOB] = " & Format(Nz(dtmDOB, Date), "\#yyyy-mm-dd\#")

If I try to put it back in, then I get errors. At this time, using the above
code with the Date() missing...it does not give me an error, but does not not
check for duplicates...in other words, I can enter data now, but if I
purposely put in duplicate data, the code does not catch it. Thank you so
much for the help.
--
Kbelo

Douglas J. Steele said:

The code that assigns the value to stLinkCriteria is supposed to be all on
one line, not the 3 lines that appear due to word-wrap.

Not sure about why the initial line would cause an error. See whether fixing
the other problem fixes it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Kathy said:

Sorry I am still with errors...I am receiving 'compile error/syntax error'
..this is how it looks: (But Private Sub txtLastName...is highlighted in
yellow and stLinkCriteria is all in red). I am really a novice as you can
tell, but just seems like I am almost there...thank you for your continued
help.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

--
Kbelo

:

Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format:
yyyy-mm-dd

Pieter

Thank you for helping...but I am now receiving a syntax error and I
just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

:

you have to many spaces in the criteria & you can solve the null date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Thank you for your response...and I adjusted the code to the
following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And
"[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName,
FirstName
and
DOB are the first three fields on the form. If I have the Before
Update
on
the LastName field, then the other two are currently blank. Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

I have been struggling with this code and trying to learn from
other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message
is
given
and you are taken to that record. So far I have the code working
for
one
field, but I get problems when I try to add other fields. I am a
novice
and
probably 'over my head', but I seem so close..any suggestions or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria)

0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You
will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Guest · Oct 17, 2007

Stefano,

Your criteria would look something like

(should be one line)
stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'And [strFirstName]=
'" & Me.tbFirstName & "" '

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Stefano said:
Hi Kathy,
I have only two text field two check and no data field.
Which part I have to remove in my case?

Thanks for your help,
Stefano.

Kathy said:

Thank you...I have put the code all on one line, but when I do that Access
removes the () from the Date as copied below:

stLinkCriteria = "[strLastName]= '" & SID & "'And [strFirstName]= '" &
strFirstName & "'And [dtmDOB] = " & Format(Nz(dtmDOB, Date),
"\#yyyy-mm-dd\#")

If I try to put it back in, then I get errors. At this time, using the
above
code with the Date() missing...it does not give me an error, but does not
not
check for duplicates...in other words, I can enter data now, but if I
purposely put in duplicate data, the code does not catch it. Thank you so
much for the help.
--
Kbelo

Douglas J. Steele said:

The code that assigns the value to stLinkCriteria is supposed to be all
on
one line, not the 3 lines that appear due to word-wrap.

Not sure about why the initial line would cause an error. See whether
fixing
the other problem fixes it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry I am still with errors...I am receiving 'compile error/syntax
error'
..this is how it looks: (But Private Sub txtLastName...is highlighted
in
yellow and stLinkCriteria is all in red). I am really a novice as you
can
tell, but just seems like I am almost there...thank you for your
continued
help.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

--
Kbelo

:

Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '"
&
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format:
yyyy-mm-dd

Pieter

Thank you for helping...but I am now receiving a syntax error and I
just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And
"[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) >
0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

:

you have to many spaces in the criteria & you can solve the null
date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And
"[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Thank you for your response...and I adjusted the code to the
following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And
"[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am
wondering
(guessing?) that I am not handling Null values. The LastName,
FirstName
and
DOB are the first three fields on the form. If I have the Before
Update
on
the LastName field, then the other two are currently blank.
Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

On Oct 16, 5:45 pm, Kathy <[email protected]>
wrote:
I have been struggling with this code and trying to learn from
other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are
any
records
already in the table that match ALL three, then a warning
message
is
given
and you are taken to that record. So far I have the code
working
for
one
field, but I get problems when I try to add other fields. I am
a
novice
and
probably 'over my head', but I seem so close..any suggestions
or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation",
stLinkCriteria)

0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr &
"You
will
now be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Many thanks!

Click to expand...

Click to expand...

Guest · Oct 17, 2007

Steve, (and everyone that has helped me too!)....Thank you so much as this
code is working very well. I only have one problem in that when I tested the
new code and I purposely did NOT fill in the FirstName textbox, I got the
error message, but then it would not allow me to go back and fill it in. Do
you have any suggestions on how I could fix this part of the code....Again
many many thanks!
--
Kbelo

Steve Sanford said:
Hi Kathy,

When you were only checking the last name, you could use the "LastName
BeforeUpdate" event. But with 3 criteria, you don't want to check for
duplicates before the other 2 criteria are filled in.

So I would move the code to the "Form BeforeUpdate" event.

One other thing. Access has a nasty habit of naming controls the same name
as the field they are bound to. So I always change the name of the control.
If the field name is "strLastName", then I name the control on the form
"tbLastName" (tb = textbox). Then I know if I am looking at a field or a
control.

Anyway, take a look at the code. Hopefully this will get you moving in the
right direction.

'------beg code-------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "You will now be taken to the record."
mButtons = vbOKOnly + vbExclamation
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Exit Sub
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'always check the NoMatch property after a find

' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE

' I hate negative logic!!!
If Not rsc.NoMatch Then
Me.Undo
MsgBox mPrompt, mButtons, mTitle
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'------end code-------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:

Thank you...I have put the code all on one line, but when I do that Access
removes the () from the Date as copied below:

stLinkCriteria = "[strLastName]= '" & SID & "'And [strFirstName]= '" &
strFirstName & "'And [dtmDOB] = " & Format(Nz(dtmDOB, Date), "\#yyyy-mm-dd\#")

If I try to put it back in, then I get errors. At this time, using the above
code with the Date() missing...it does not give me an error, but does not not
check for duplicates...in other words, I can enter data now, but if I
purposely put in duplicate data, the code does not catch it. Thank you so
much for the help.
--
Kbelo

Douglas J. Steele said:

The code that assigns the value to stLinkCriteria is supposed to be all on
one line, not the 3 lines that appear due to word-wrap.

Not sure about why the initial line would cause an error. See whether fixing
the other problem fixes it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry I am still with errors...I am receiving 'compile error/syntax error'
..this is how it looks: (But Private Sub txtLastName...is highlighted in
yellow and stLinkCriteria is all in red). I am really a novice as you can
tell, but just seems like I am almost there...thank you for your continued
help.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0 Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

--
Kbelo

:

Extra " before And [StrFirstName]

stLinkCriteria = "[strLastName]= '" & SID & "' And [strFirstName]= '" &
strFirstName & "' And [dtmDOB] = " &
Format(nz(dtmDOB,Date()),"\#yyyy-mm-dd\#")

Note That I also changed the Date Field's Format to one that is valid
regardless of Regional Settings.
Access only recognizes US Format: mm/dd/yyyy & Military Format:
yyyy-mm-dd

Pieter

Thank you for helping...but I am now receiving a syntax error and I
just
don't know how to fix it. Here is the whole code so far:

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria) > 0
Then
Me.Undo
MsgBox "Warning Possible Duplicate Record" & vbCr & vbCr & "You will
now
be
taken to the record.", vbInformation, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Can you see where I am going wrong? Thanks so much again.
--
Kbelo

:

you have to many spaces in the criteria & you can solve the null date
issue

stLinkCriteria = "[strLastName]= '" & SID & "' " And "[strFirstName]=
'"
&
strFirstName & "' And [dtmDOB] = #" & nz(dtmDOB,Date()) & "# "

hth

Pieter

Thank you for your response...and I adjusted the code to the
following:

stLinkCriteria = "[strLastName]= ' " & SID & " ' " And
"[strFirstName]=
'
"
& strFirstName & " ' And [dtmDOB] = #" & dtmDOB & " # ; "

But I am receiving Run Time Error 13 (type mismatch). I am wondering
(guessing?) that I am not handling Null values. The LastName,
FirstName
and
DOB are the first three fields on the form. If I have the Before
Update
on
the LastName field, then the other two are currently blank. Should I
have
the
Before Update on the third field (DOB field) only?
Sorry that I am so lost....thanks again for your help.
--
Kbelo

:

I have been struggling with this code and trying to learn from
other
threads.
I am trying to check three fields on a form for
duplicates...specifically
LastName, FirstName and DOB (date of birth) and if there are any
records
already in the table that match ALL three, then a warning message
is
given
and you are taken to that record. So far I have the code working
for
one
field, but I get problems when I try to add other fields. I am a
novice
and
probably 'over my head', but I seem so close..any suggestions or
help
is
greatly appreciated.
Here is the working code for one field.

Private Sub txtLastName_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strLastName.Value

stLinkCriteria = "[strLastName]=" & "'" & SID & "'"

Should be something like this:
stLinkCriteria = "[strLastName]= ' " & SID & " ' AND
[strFirstName]= ' " & sFirstName & " ' AND [strBirthDate] = #" &
sBirthDate & " # ; "

If DCount("strLastName", "tblPatientInformation", stLinkCriteria)

0

Click to expand...

Click to expand...

Guest · Oct 17, 2007

Kathy,

You should be able to fill in the first name. In my test table/form I can
skip the first name, get the alert and go back and fill it in.

I added three lines to the code in this section (look for '<= new***********):

'-----------Snip------------------
'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbLastName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbFirstName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Me.tbDOB.SetFocus '<= new***********
Exit Sub
End If
'-----------Snip------------------

OK, things to try:

The first two lines on every code page should be:
Option Compare Database
Option Explicit

Did you try and compile the code (Debug/Compile)? Any errors? I used
different control names - did you modify the code to your names?

Did you set a breakpoint and single step thru the code? Any errors?

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:
Steve, (and everyone that has helped me too!)....Thank you so much as this
code is working very well. I only have one problem in that when I tested the
new code and I purposely did NOT fill in the FirstName textbox, I got the
error message, but then it would not allow me to go back and fill it in. Do
you have any suggestions on how I could fix this part of the code....Again
many many thanks!
--
Kbelo

Steve Sanford said:

Hi Kathy,

When you were only checking the last name, you could use the "LastName
BeforeUpdate" event. But with 3 criteria, you don't want to check for
duplicates before the other 2 criteria are filled in.

So I would move the code to the "Form BeforeUpdate" event.

One other thing. Access has a nasty habit of naming controls the same name
as the field they are bound to. So I always change the name of the control.
If the field name is "strLastName", then I name the control on the form
"tbLastName" (tb = textbox). Then I know if I am looking at a field or a
control.

Anyway, take a look at the code. Hopefully this will get you moving in the
right direction.

'------beg code-------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "You will now be taken to the record."
mButtons = vbOKOnly + vbExclamation
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Exit Sub
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'always check the NoMatch property after a find

' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE

' I hate negative logic!!!
If Not rsc.NoMatch Then
Me.Undo
MsgBox mPrompt, mButtons, mTitle
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'------end code-------------------

HTH

Click to expand...

riccifs · Oct 18, 2007

Kathy said:
Kathy,

You should be able to fill in the first name. In my test table/form I can
skip the first name, get the alert and go back and fill it in.

I added three lines to the code in this section (look for '<= new***********):

'-----------Snip------------------
'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbLastName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbFirstName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Me.tbDOB.SetFocus '<= new***********
Exit Sub
End If
'-----------Snip------------------

OK, things to try:

The first two lines on every code page should be:
Option Compare Database
Option Explicit

Did you try and compile the code (Debug/Compile)? Any errors? I used
different control names - did you modify the code to your names?

Did you set a breakpoint and single step thru the code? Any errors?

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:

Steve, (and everyone that has helped me too!)....Thank you so much as this
code is working very well. I only have one problem in that when I tested the
new code and I purposely did NOT fill in the FirstName textbox, I got the
error message, but then it would not allow me to go back and fill it in. Do
you have any suggestions on how I could fix this part of the code....Again
many many thanks!

Click to expand...

Hi Kathy,
When you were only checking the last name, you could use the "LastName
BeforeUpdate" event. But with 3 criteria, you don't want to check for
duplicates before the other 2 criteria are filled in.
So I would move the code to the "Form BeforeUpdate" event.
One other thing. Access has a nasty habit of naming controls the same name
as the field they are bound to. So I always change the name of the control.
If the field name is "strLastName", then I name the control on the form
"tbLastName" (tb = textbox). Then I know if I am looking at a field or a
control.
Anyway, take a look at the code. Hopefully this will get you moving in the
right direction.
'------beg code-------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Dim mPrompt As String
Dim mButtons, mTitle
Set rsc = Me.RecordsetClone
'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "You will now be taken to the record."
mButtons = vbOKOnly + vbExclamation
mTitle = "Duplicate Information"
'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Exit Sub
End If
stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "
rsc.FindFirst stLinkCriteria
'always check the NoMatch property after a find
' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates
' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE
' I hate negative logic!!!
If Not rsc.NoMatch Then
Me.Undo
MsgBox mPrompt, mButtons, mTitle
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing
End Sub
'------end code-------------------
HTH

Click to expand...

Click to expand...

Hi Steve,

at the end YOU DID IT!!
your code is working great. I'm more than thank to you for the help
you give to us!
But I have to say many thanks even to Kathy in fact is from her that
start all that.
She didn't give up and she didn't stop to ask for a reasonable
solution! Well done to you Kathy.

Bye to everyone,
Stefano.

Guest · Oct 18, 2007

Steve, thank you again...the SetFocus took take care of the problem...and it
is working beautifully. I am very grateful for all your help. So I understand
if you don't want to deal with me anymore....but I no longer have any shame
to ask you one more thing...here goes: Since the form is so long to fill out
and the "alerts" will not kick in until you move to the next record, I tried
to put the code on the third field which would be the DOB. Sortof like an
early warning system....(meanwhile I took it off the
Form_beforeUpdate)....anyway, it went back to not allowing me to enter data
into blank fields...It gives me the correct alert...like the code is trying
to work...but then I cannot get into the field to fill it. My limitied skills
prevent me from figuring out how to correct the problem. Would this be
something "simple" to correct.
As always, my humble thanks,
Kbelo

Steve Sanford said:
Kathy,

You should be able to fill in the first name. In my test table/form I can
skip the first name, get the alert and go back and fill it in.

I added three lines to the code in this section (look for '<= new***********):

'-----------Snip------------------
'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbLastName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbFirstName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Me.tbDOB.SetFocus '<= new***********
Exit Sub
End If
'-----------Snip------------------

OK, things to try:

The first two lines on every code page should be:
Option Compare Database
Option Explicit

Did you try and compile the code (Debug/Compile)? Any errors? I used
different control names - did you modify the code to your names?

Did you set a breakpoint and single step thru the code? Any errors?

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:

Steve, (and everyone that has helped me too!)....Thank you so much as this
code is working very well. I only have one problem in that when I tested the
new code and I purposely did NOT fill in the FirstName textbox, I got the
error message, but then it would not allow me to go back and fill it in. Do
you have any suggestions on how I could fix this part of the code....Again
many many thanks!
--
Kbelo

Steve Sanford said:

Hi Kathy,

When you were only checking the last name, you could use the "LastName
BeforeUpdate" event. But with 3 criteria, you don't want to check for
duplicates before the other 2 criteria are filled in.

So I would move the code to the "Form BeforeUpdate" event.

One other thing. Access has a nasty habit of naming controls the same name
as the field they are bound to. So I always change the name of the control.
If the field name is "strLastName", then I name the control on the form
"tbLastName" (tb = textbox). Then I know if I am looking at a field or a
control.

Anyway, take a look at the code. Hopefully this will get you moving in the
right direction.

'------beg code-------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "You will now be taken to the record."
mButtons = vbOKOnly + vbExclamation
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Exit Sub
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'always check the NoMatch property after a find

' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE

' I hate negative logic!!!
If Not rsc.NoMatch Then
Me.Undo
MsgBox mPrompt, mButtons, mTitle
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'------end code-------------------

HTH

Click to expand...

Click to expand...

Guest · Oct 20, 2007

Sorry it took so long to get back to you, Kathy.
I have been trying to duplicate your problem of not allowing you to enter
data into blank fields. So far, I've been always been able to enter data into
the empty controls.

You still need to check for duplicates in the form AfterUpdate event. What
happens if you pass the first check for duplicates, then, before the record
is saved, the last name is change to a name that would be a duplicate. Since
the DOB is not being changes, none of the DOB control events would fire and
the duplication would not be caught.

I think you at least need to check for dups in the form AfterUpdate event
and the DOB control Exit event.

So I moved the code and created a function. then the function is called from
the event procedures.

There was no way to add a name if there happened to be two "Jim Smiths" with
the same DOB. So I changed the message box to allow that option.

Here is the event procedure code for the form and the DOB control:

'-------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = DuplicateCheck
End Sub

Private Sub tbDOB_Exit(Cancel As Integer)
Cancel = DuplicateCheck
End Sub
'-------------------------------

Here is the function to check for dups:

'------beg code-------------------------
Function DuplicateCheck() As Boolean

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle, Response

'check if change to form data
If Not Me.Dirty Then
Exit Function
End If

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "Do you want to continue to add this record?" & vbCr
& vbCr
mPrompt = mPrompt & "Select YES to add the record" & vbCr & vbCr
mPrompt = mPrompt & "Select NO to be taken to the record."
mButtons = vbYesNo + vbCritical + vbDefaultButton2
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
DuplicateCheck = True
Me.tbLastName.SetFocus
Exit Function
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
DuplicateCheck = True
Me.tbFirstName.SetFocus
Exit Function
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation '
DuplicateCheck = True
Me.tbDOB.SetFocus
Exit Function
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'next 3 lines are for testing
' Debug.Print rsc!strLastName
' Debug.Print rsc!strFirstName
' Debug.Print rsc!dtmDOB

'always check the NoMatch property after a find
' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE
' I hate negative logic!!!

'********changes here*************
If Not rsc.NoMatch Then
'possible duplicate record found
'ask if they want to goto the record or continue to add record
Response = MsgBox(mPrompt, mButtons, mTitle)
If Response = vbNo Then ' User chose No
'undo entries and goto record
Me.Undo
Me.Bookmark = rsc.Bookmark
Me.tbLastName.SetFocus
End If
End If
'*********************************
DuplicateCheck = False

'clean up
rsc.Close
Set rsc = Nothing

End Function
'------end code-------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:
Steve, thank you again...the SetFocus took take care of the problem...and it
is working beautifully. I am very grateful for all your help. So I understand
if you don't want to deal with me anymore....but I no longer have any shame
to ask you one more thing...here goes: Since the form is so long to fill out
and the "alerts" will not kick in until you move to the next record, I tried
to put the code on the third field which would be the DOB. Sortof like an
early warning system....(meanwhile I took it off the
Form_beforeUpdate)....anyway, it went back to not allowing me to enter data
into blank fields...It gives me the correct alert...like the code is trying
to work...but then I cannot get into the field to fill it. My limitied skills
prevent me from figuring out how to correct the problem. Would this be
something "simple" to correct.
As always, my humble thanks,
Kbelo

Steve Sanford said:

Kathy,

You should be able to fill in the first name. In my test table/form I can
skip the first name, get the alert and go back and fill it in.

I added three lines to the code in this section (look for '<= new***********):

'-----------Snip------------------
'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbLastName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbFirstName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Me.tbDOB.SetFocus '<= new***********
Exit Sub
End If
'-----------Snip------------------

OK, things to try:

The first two lines on every code page should be:
Option Compare Database
Option Explicit

Did you try and compile the code (Debug/Compile)? Any errors? I used
different control names - did you modify the code to your names?

Did you set a breakpoint and single step thru the code? Any errors?

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:

Steve, (and everyone that has helped me too!)....Thank you so much as this
code is working very well. I only have one problem in that when I tested the
new code and I purposely did NOT fill in the FirstName textbox, I got the
error message, but then it would not allow me to go back and fill it in. Do
you have any suggestions on how I could fix this part of the code....Again
many many thanks!
--
Kbelo

:

Hi Kathy,

When you were only checking the last name, you could use the "LastName
BeforeUpdate" event. But with 3 criteria, you don't want to check for
duplicates before the other 2 criteria are filled in.

So I would move the code to the "Form BeforeUpdate" event.

One other thing. Access has a nasty habit of naming controls the same name
as the field they are bound to. So I always change the name of the control.
If the field name is "strLastName", then I name the control on the form
"tbLastName" (tb = textbox). Then I know if I am looking at a field or a
control.

Anyway, take a look at the code. Hopefully this will get you moving in the
right direction.

'------beg code-------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "You will now be taken to the record."
mButtons = vbOKOnly + vbExclamation
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Exit Sub
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'always check the NoMatch property after a find

' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE

' I hate negative logic!!!
If Not rsc.NoMatch Then
Me.Undo
MsgBox mPrompt, mButtons, mTitle
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'------end code-------------------

HTH

Click to expand...

Click to expand...

Guest · Oct 25, 2007

Hello Steve,
Again many thanks...I don't know where you are located, but I am in San
Diego and we are just getting hooked up again after unbelievable wild
fires...sort of feel shell shocked. But I appreciate that you have not given
up on me and as soon as I am back on project, I will let you know how it is
going.
best,
--
Kbelo

Steve Sanford said:
Sorry it took so long to get back to you, Kathy.
I have been trying to duplicate your problem of not allowing you to enter
data into blank fields. So far, I've been always been able to enter data into
the empty controls.

You still need to check for duplicates in the form AfterUpdate event. What
happens if you pass the first check for duplicates, then, before the record
is saved, the last name is change to a name that would be a duplicate. Since
the DOB is not being changes, none of the DOB control events would fire and
the duplication would not be caught.

I think you at least need to check for dups in the form AfterUpdate event
and the DOB control Exit event.

So I moved the code and created a function. then the function is called from
the event procedures.

There was no way to add a name if there happened to be two "Jim Smiths" with
the same DOB. So I changed the message box to allow that option.

Here is the event procedure code for the form and the DOB control:

'-------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = DuplicateCheck
End Sub

Private Sub tbDOB_Exit(Cancel As Integer)
Cancel = DuplicateCheck
End Sub
'-------------------------------

Here is the function to check for dups:

'------beg code-------------------------
Function DuplicateCheck() As Boolean

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle, Response

'check if change to form data
If Not Me.Dirty Then
Exit Function
End If

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "Do you want to continue to add this record?" & vbCr
& vbCr
mPrompt = mPrompt & "Select YES to add the record" & vbCr & vbCr
mPrompt = mPrompt & "Select NO to be taken to the record."
mButtons = vbYesNo + vbCritical + vbDefaultButton2
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
DuplicateCheck = True
Me.tbLastName.SetFocus
Exit Function
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
DuplicateCheck = True
Me.tbFirstName.SetFocus
Exit Function
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation '
DuplicateCheck = True
Me.tbDOB.SetFocus
Exit Function
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'next 3 lines are for testing
' Debug.Print rsc!strLastName
' Debug.Print rsc!strFirstName
' Debug.Print rsc!dtmDOB

'always check the NoMatch property after a find
' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE
' I hate negative logic!!!

'********changes here*************
If Not rsc.NoMatch Then
'possible duplicate record found
'ask if they want to goto the record or continue to add record
Response = MsgBox(mPrompt, mButtons, mTitle)
If Response = vbNo Then ' User chose No
'undo entries and goto record
Me.Undo
Me.Bookmark = rsc.Bookmark
Me.tbLastName.SetFocus
End If
End If
'*********************************
DuplicateCheck = False

'clean up
rsc.Close
Set rsc = Nothing

End Function
'------end code-------------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Kathy said:

Steve, thank you again...the SetFocus took take care of the problem...and it
is working beautifully. I am very grateful for all your help. So I understand
if you don't want to deal with me anymore....but I no longer have any shame
to ask you one more thing...here goes: Since the form is so long to fill out
and the "alerts" will not kick in until you move to the next record, I tried
to put the code on the third field which would be the DOB. Sortof like an
early warning system....(meanwhile I took it off the
Form_beforeUpdate)....anyway, it went back to not allowing me to enter data
into blank fields...It gives me the correct alert...like the code is trying
to work...but then I cannot get into the field to fill it. My limitied skills
prevent me from figuring out how to correct the problem. Would this be
something "simple" to correct.
As always, my humble thanks,
Kbelo

Steve Sanford said:

Kathy,

You should be able to fill in the first name. In my test table/form I can
skip the first name, get the alert and go back and fill it in.

I added three lines to the code in this section (look for '<= new***********):

'-----------Snip------------------
'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbLastName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Me.tbFirstName.SetFocus '<= new***********
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Me.tbDOB.SetFocus '<= new***********
Exit Sub
End If
'-----------Snip------------------

OK, things to try:

The first two lines on every code page should be:
Option Compare Database
Option Explicit

Did you try and compile the code (Debug/Compile)? Any errors? I used
different control names - did you modify the code to your names?

Did you set a breakpoint and single step thru the code? Any errors?

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

:

Steve, (and everyone that has helped me too!)....Thank you so much as this
code is working very well. I only have one problem in that when I tested the
new code and I purposely did NOT fill in the FirstName textbox, I got the
error message, but then it would not allow me to go back and fill it in. Do
you have any suggestions on how I could fix this part of the code....Again
many many thanks!
--
Kbelo

:

Hi Kathy,

When you were only checking the last name, you could use the "LastName
BeforeUpdate" event. But with 3 criteria, you don't want to check for
duplicates before the other 2 criteria are filled in.

So I would move the code to the "Form BeforeUpdate" event.

One other thing. Access has a nasty habit of naming controls the same name
as the field they are bound to. So I always change the name of the control.
If the field name is "strLastName", then I name the control on the form
"tbLastName" (tb = textbox). Then I know if I am looking at a field or a
control.

Anyway, take a look at the code. Hopefully this will get you moving in the
right direction.

'------beg code-------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Dim mPrompt As String
Dim mButtons, mTitle

Set rsc = Me.RecordsetClone

'for message box
mPrompt = "Warning: Possible Duplicate Record" & vbCr & vbCr
mPrompt = mPrompt & "You will now be taken to the record."
mButtons = vbOKOnly + vbExclamation
mTitle = "Duplicate Information"

'check that all criteria are entered
If IsNull(Me.tbLastName) Then
MsgBox "Last name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbFirstName) Then
MsgBox "First name is required!!", vbOKOnly + vbExclamation
Cancel = True
Exit Sub
End If
If IsNull(Me.tbDOB) Then
MsgBox "Date of Birth is required!!", vbOKOnly + vbExclamation
'vbExclamation
Cancel = True
Exit Sub
End If

stLinkCriteria = "[strLastName]= '" & Me.tbLastName & "'"
stLinkCriteria = stLinkCriteria & " And [strFirstName]= '" &
Me.tbFirstName & "'"
stLinkCriteria = stLinkCriteria & " And [dtmDOB] = #" & Me.tbDOB & "# "

rsc.FindFirst stLinkCriteria

'always check the NoMatch property after a find

' rsc.NoMatch = TRUE means no duplicates
' rsc.NoMatch = FALSE means there are duplicates

' if rsc.NoMatch = FALSE, then NOT rsc.NoMatch is TRUE

' I hate negative logic!!!
If Not rsc.NoMatch Then
Me.Undo
MsgBox mPrompt, mButtons, mTitle
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub
'------end code-------------------

HTH

Click to expand...

Click to expand...

Checking multiple fields for Duplicate

Guest

OldPro

Guest

Pieter Wijnen

Guest

Pieter Wijnen

Douglas J. Steele

Guest

Douglas J. Steele

Guest

Stefano

Guest

Guest

Guest

Guest

riccifs

Guest

Guest

Guest