Just a warning on duplicate records | Newbie | | Join Date: Dec 2006
Posts: 17
| | |
I am trying to set up a message box warning (or open to some sort of other warning) when a duplicate record is entered. I do want to allow duplicate records but I am looking to warn when it does occur.
Example of warning: Check number all ready entered are you sure you want to continue.
Any Ideas?
|  | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: Just a warning on duplicate records
Quote:
Originally Posted by jjstevens I am trying to set up a message box warning (or open to some sort of other warning) when a duplicate record is entered. I do want to allow duplicate records but I am looking to warn when it does occur.
Example of warning: Check number all ready entered are you sure you want to continue.
Any Ideas? When relevant field is entered on form (lets call it RecNo for now) then you need a before update event to check if number is already entered. -
Private Sub RecNo_BeforeUpdate()
-
Dim rslt As Integer
-
-
If nz(DLookup("[RecNo]", "TableName", "[RecNo]=" & Me.RecNo),0) <> 0 Then
-
rslt = Msgbox ("This number has already been entered. Do you wish to continue?", vbYesNo)
-
If rslt = vbNo Then
-
Me.RecNo = Null
-
Me.RecNo.SetFocus
-
End If
-
End If
-
-
End Sub
-
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Just a warning on duplicate records
Mary's answer exactly matches the question.
However, in most circumstances you may be better served not entering a record number as such but allowing Access to supply an AutoNumber for the Primary Key. There are reasons why this is not always appropriate, but should always be considered.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: Just a warning on duplicate records
Quote:
Originally Posted by NeoPa Mary's answer exactly matches the question.
However, in most circumstances you may be better served not entering a record number as such but allowing Access to supply an AutoNumber for the Primary Key. There are reasons why this is not always appropriate, but should always be considered. This won't allow for duplicate entry Ade which seems to be a requirement.
Mary
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: Just a warning on duplicate records
Quote:
Originally Posted by mmccarthy This won't allow for duplicate entry Ade which seems to be a requirement.
Mary Quite right.
I must have misread it the first time because it states the requirement quite clearly. Sorry - ignore my post.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: Just a warning on duplicate records
Quote:
Originally Posted by NeoPa Quite right.
I must have misread it the first time because it states the requirement quite clearly. Sorry - ignore my post. Now Ade
I could never just ignore you. :D
Mary
| | Newbie | | Join Date: Dec 2006
Posts: 17
| | | re: Just a warning on duplicate records
Thank you all, this solution works great.
| | Administrator | | Join Date: Aug 2006 Location: Dublin, Ireland
Posts: 10,875
| | | re: Just a warning on duplicate records
Quote:
Originally Posted by jjstevens Thank you all, this solution works great. You're welcome.
| | Newbie | | Join Date: Feb 2007
Posts: 2
| | | re: Just a warning on duplicate records
Hello,
I've tried adding this to my table to get it to flash up a warning for duplicate postcodes, but it doesn't work.
Is it because I'm using a string instead of a number?
The error message I get is Run Time error 3075:
Syntax error (missing operator) in query expression '[Postcode]=TN8 6HR'
my code is: - Private Sub Postcode_BeforeUpdate(Cancel As Integer)
-
Dim rslt As Integer
-
-
If Nz(DLookup("[Postcode]", "Addresses", "[Postcode]=" & Me.Postcode), 0) <> 0 Then
-
rslt = MsgBox("This number has already been entered. Do you wish to continue?", vbYesNo)
-
If rslt = vbNo Then
-
Me.Postcode = Null
-
Me.Postcode.SetFocus
-
End If
-
End If
-
End Sub
can you help?
btw, I entered it in on the form design screen under Events >> Before Update as an Event Procedure
| | Newbie | | Join Date: Feb 2007
Posts: 2
| | | re: Just a warning on duplicate records
Quote:
Originally Posted by BrerBunny Hello,
I've tried adding this to my table to get it to flash up a warning for duplicate postcodes, but it doesn't work.
Is it because I'm using a string instead of a number?
The error message I get is Run Time error 3075:
Syntax error (missing operator) in query expression '[Postcode]=TN8 6HR'
my code is:
- Private Sub Postcode_BeforeUpdate(Cancel As Integer)
-
Dim rslt As Integer
-
-
If Nz(DLookup("[Postcode]", "Addresses", "[Postcode]=" & Me.Postcode), 0) <> 0 Then
-
rslt = MsgBox("This number has already been entered. Do you wish to continue?", vbYesNo)
-
If rslt = vbNo Then
-
Me.Postcode = Null
-
Me.Postcode.SetFocus
-
End If
-
End If
-
End Sub
can you help?
btw, I entered it in on the form design screen under Events >> Before Update as an Event Procedure
looks like I should change this to: - Private Sub Postcode_BeforeUpdate(Cancel As Integer)
-
Dim rslt As Integer
-
-
If Nz(DLookup("[Postcode]", "Addresses", "[Postcode]='" & Me.Postcode & "'"), 0) <> 0 Then
-
rslt = MsgBox("This number has already been entered. Do you wish to continue?", vbYesNo)
-
If rslt = vbNo Then
-
Cancel = True
-
Me.Postcode.SetFocus
-
End If
-
End If
-
End Sub
I'll try it and see!
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,414 network members.
|
