Duplicate Entries

[Guest]

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

 

[rquintal]

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

Sounds like you put the code directly in the event's box. Instead, you
need to select the [Event Procedure] option in that box, then click on
the ellipse that appears to the right. Paste the code in between the
lines "public sub ....." and "end sub".

Q

 

[Linq Adams via AccessMonster.com]

We really need to see the code you're using and know where it's placed.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Jen]

Thank you for replying...you're right, i was pasting the code into the
BeforeUpdate box...why did i have to be blonde for God's sake ??
I tried clicking on the ellipse and selecting 'code builder'......i pasted
the code into the right place between the lines like you said and i'm getting
syntax errors all over the place. My table name is 'tbl_Main Records'....my
text box is called 'Assessment No' whose control source is a field of the
same name. The text box label is called 'Label139'...in case it
matters....help !!

Jenny

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

Sounds like you put the code directly in the event's box. Instead, you
need to select the [Event Procedure] option in that box, then click on
the ellipse that appears to the right. Paste the code in between the
lines "public sub ....." and "end sub".

Q

 

[Jen]

Here's the code i'm trying to use....it's from one of the posts near to this
one

Private Sub Assessment No_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("Assessment No", "tbl_Main Records", _
"Assessment No = " & Chr(34) & Me!Assessment No &
Chr(34))

If Not IsNull(varAssessment No) Then

If MsgBox( _
"A record was found with the same Account Number.
" & _ "Do you want to cancel these changes
and go to that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "Assessment No = " & varAssessment No
End If

End If

End If

End Sub

Thank you for replying...you're right, i was pasting the code into the
BeforeUpdate box...why did i have to be blonde for God's sake ??
I tried clicking on the ellipse and selecting 'code builder'......i pasted
the code into the right place between the lines like you said and i'm getting
syntax errors all over the place. My table name is 'tbl_Main Records'....my
text box is called 'Assessment No' whose control source is a field of the
same name. The text box label is called 'Label139'...in case it
matters....help !!

Jenny

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

Sounds like you put the code directly in the event's box. Instead, you
need to select the [Event Procedure] option in that box, then click on
the ellipse that appears to the right. Paste the code in between the
lines "public sub ....." and "end sub".

Q

 

[Jen]

Ok i've tried some different code which is now having no effect at all. My
database is finished once i get this sodding thing sorted out...help guys
please !

Here's the code

Private Sub strAssessmentNo_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

SID = Me.strAssessmentNo.Value
stLinkCriteria = "[strAssessment No]=" & "'" & SID & " ' "

If DCount("strAssessment No", "tbl_Main Records", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
MsgBox "This assessment no " _
& SID & " has been used already." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Thanks
Jenny

Here's the code i'm trying to use....it's from one of the posts near to this
one

Private Sub Assessment No_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("Assessment No", "tbl_Main Records", _
"Assessment No = " & Chr(34) & Me!Assessment No &
Chr(34))

If Not IsNull(varAssessment No) Then

If MsgBox( _
"A record was found with the same Account Number.
" & _ "Do you want to cancel these changes
and go to that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "Assessment No = " & varAssessment No
End If

End If

End If

End Sub

Thank you for replying...you're right, i was pasting the code into the
BeforeUpdate box...why did i have to be blonde for God's sake ??
I tried clicking on the ellipse and selecting 'code builder'......i pasted
the code into the right place between the lines like you said and i'm getting
syntax errors all over the place. My table name is 'tbl_Main Records'....my
text box is called 'Assessment No' whose control source is a field of the
same name. The text box label is called 'Label139'...in case it
matters....help !!

Jenny

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

Sounds like you put the code directly in the event's box. Instead, you
need to select the [Event Procedure] option in that box, then click on
the ellipse that appears to the right. Paste the code in between the
lines "public sub ....." and "end sub".

Q

 

[Jen]

No problem...got it sorted

Ok i've tried some different code which is now having no effect at all. My
database is finished once i get this sodding thing sorted out...help guys
please !

Here's the code

Private Sub strAssessmentNo_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

SID = Me.strAssessmentNo.Value
stLinkCriteria = "[strAssessment No]=" & "'" & SID & " ' "

If DCount("strAssessment No", "tbl_Main Records", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
MsgBox "This assessment no " _
& SID & " has been used already." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Thanks
Jenny

Here's the code i'm trying to use....it's from one of the posts near to this
one

Private Sub Assessment No_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("Assessment No", "tbl_Main Records", _
"Assessment No = " & Chr(34) & Me!Assessment No &
Chr(34))

If Not IsNull(varAssessment No) Then

If MsgBox( _
"A record was found with the same Account Number.
" & _ "Do you want to cancel these changes
and go to that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "Assessment No = " & varAssessment No
End If

End If

End If

End Sub

Thank you for replying...you're right, i was pasting the code into the
BeforeUpdate box...why did i have to be blonde for God's sake ??
I tried clicking on the ellipse and selecting 'code builder'......i pasted
the code into the right place between the lines like you said and i'm getting
syntax errors all over the place. My table name is 'tbl_Main Records'....my
text box is called 'Assessment No' whose control source is a field of the
same name. The text box label is called 'Label139'...in case it
matters....help !!

Jenny

:

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

Sounds like you put the code directly in the event's box. Instead, you
need to select the [Event Procedure] option in that box, then click on
the ellipse that appears to the right. Paste the code in between the
lines "public sub ....." and "end sub".

Q

 

[Jen]

Or so i thought

I've been using Autonumber to solve this but the record no's are all over
the place as people are starting a record, deleting it and then starting
again and the whole thing is beginning to look awful. Anbody suggest some
code to trap the duplicate entry at form level ?

Table is called tbl_Main Records
Field is called Assessment No
Text box is called Assessment No

Thanks,
Jen

No problem...got it sorted

Ok i've tried some different code which is now having no effect at all. My
database is finished once i get this sodding thing sorted out...help guys
please !

Here's the code

Private Sub strAssessmentNo_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

SID = Me.strAssessmentNo.Value
stLinkCriteria = "[strAssessment No]=" & "'" & SID & " ' "

If DCount("strAssessment No", "tbl_Main Records", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
MsgBox "This assessment no " _
& SID & " has been used already." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Thanks
Jenny

Here's the code i'm trying to use....it's from one of the posts near to this
one

Private Sub Assessment No_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("Assessment No", "tbl_Main Records", _
"Assessment No = " & Chr(34) & Me!Assessment No &
Chr(34))

If Not IsNull(varAssessment No) Then

If MsgBox( _
"A record was found with the same Account Number.
" & _ "Do you want to cancel these changes
and go to that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "Assessment No = " & varAssessment No
End If

End If

End If

End Sub

:

Thank you for replying...you're right, i was pasting the code into the
BeforeUpdate box...why did i have to be blonde for God's sake ??
I tried clicking on the ellipse and selecting 'code builder'......i pasted
the code into the right place between the lines like you said and i'm getting
syntax errors all over the place. My table name is 'tbl_Main Records'....my
text box is called 'Assessment No' whose control source is a field of the
same name. The text box label is called 'Label139'...in case it
matters....help !!

Jenny

:

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

Sounds like you put the code directly in the event's box. Instead, you
need to select the [Event Procedure] option in that box, then click on
the ellipse that appears to the right. Paste the code in between the
lines "public sub ....." and "end sub".

Q

 

[Jen]

Right...i managed to work it out. The problem was with the fact that i had
gaps in the field names and table names, so i changed them. Assessment No
became AssessmentNo etc...

Here's the code now

Private Sub AssessmentNo_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.AssessmentNo) Or (Me.AssessmentNo =
Me.AssessmentNo.OldValue) Then
'Do nothing: it's not a duplicate of itself.
Else
If Not IsNull(DLookup("AssessmentNo", "tblMainRecords",
"AssessmentNo = " & Me.AssessmentNo)) Then
Cancel = True
strMsg = "That assessment number has been used already." &
vbCrLf & _
"Use the 'View Assessment List' report to see the sequence."
MsgBox strMsg, vbExclamation, "Stop right there you muppet !"
End If
End If
End Sub

Byeee...

Or so i thought

I've been using Autonumber to solve this but the record no's are all over
the place as people are starting a record, deleting it and then starting
again and the whole thing is beginning to look awful. Anbody suggest some
code to trap the duplicate entry at form level ?

Table is called tbl_Main Records
Field is called Assessment No
Text box is called Assessment No

Thanks,
Jen

No problem...got it sorted

Ok i've tried some different code which is now having no effect at all. My
database is finished once i get this sodding thing sorted out...help guys
please !

Here's the code

Private Sub strAssessmentNo_BeforeUpdate(Cancel As Integer)

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

Set rsc = Me.RecordsetClone

SID = Me.strAssessmentNo.Value
stLinkCriteria = "[strAssessment No]=" & "'" & SID & " ' "

If DCount("strAssessment No", "tbl_Main Records", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
MsgBox "This assessment no " _
& SID & " has been used already." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Thanks
Jenny

:

Here's the code i'm trying to use....it's from one of the posts near to this
one

Private Sub Assessment No_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("Assessment No", "tbl_Main Records", _
"Assessment No = " & Chr(34) & Me!Assessment No &
Chr(34))

If Not IsNull(varAssessment No) Then

If MsgBox( _
"A record was found with the same Account Number.
" & _ "Do you want to cancel these changes
and go to that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "Assessment No = " & varAssessment No
End If

End If

End If

End Sub

:

Thank you for replying...you're right, i was pasting the code into the
BeforeUpdate box...why did i have to be blonde for God's sake ??
I tried clicking on the ellipse and selecting 'code builder'......i pasted
the code into the right place between the lines like you said and i'm getting
syntax errors all over the place. My table name is 'tbl_Main Records'....my
text box is called 'Assessment No' whose control source is a field of the
same name. The text box label is called 'Label139'...in case it
matters....help !!

Jenny

:

Hi Guys,
Had a problem recently and got it solved so quick that i thought i'd try
again. If i don't want a user to make a duplicate entry into a form, how do i
go about it ?. I've already got this field set as a primary key and this will
give an error message when the user tries to save the information but i'd
prefer a warning message to pop up when the user moves to the next control.
I've looked at some of the replies to a similar question on
these forums but the solution doesn't work for me. Some users were advised to
put a certain code in the BeforeUpdate event....but i just keep getting a
message saying that "The macro to which you're referring doesn't
exist"....what am i doing wrong ?.

Thanks,
Jenny

Ps...I'm using Access 2000

Sounds like you put the code directly in the event's box. Instead, you
need to select the [Event Procedure] option in that box, then click on
the ellipse that appears to the right. Paste the code in between the
lines "public sub ....." and "end sub".

Q