By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Just a warning on duplicate records

P: 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?
Dec 3 '06 #1
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub RecNo_BeforeUpdate()
  2. Dim rslt As Integer
  3.  
  4.    If nz(DLookup("[RecNo]", "TableName", "[RecNo]=" & Me.RecNo),0) <> 0 Then
  5.       rslt = Msgbox ("This number has already been entered. Do you wish to continue?", vbYesNo)
  6.       If rslt = vbNo Then
  7.          Me.RecNo = Null
  8.          Me.RecNo.SetFocus
  9.       End If
  10.    End If
  11.  
  12. End Sub
  13.  
Dec 3 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 3 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 4 '06 #4

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 4 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 4 '06 #6

P: 17
Thank you all, this solution works great.
Dec 5 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you all, this solution works great.
You're welcome.
Dec 5 '06 #8

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Postcode_BeforeUpdate(Cancel As Integer)
  2. Dim rslt As Integer
  3.  
  4.    If Nz(DLookup("[Postcode]", "Addresses", "[Postcode]=" & Me.Postcode), 0) <> 0 Then
  5.       rslt = MsgBox("This number has already been entered. Do you wish to continue?", vbYesNo)
  6.       If rslt = vbNo Then
  7.          Me.Postcode = Null
  8.          Me.Postcode.SetFocus
  9.       End If
  10.    End If
  11. End Sub
can you help?
btw, I entered it in on the form design screen under Events >> Before Update as an Event Procedure
Feb 15 '07 #9

P: 2
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Postcode_BeforeUpdate(Cancel As Integer)
  2. Dim rslt As Integer
  3.  
  4.    If Nz(DLookup("[Postcode]", "Addresses", "[Postcode]=" & Me.Postcode), 0) <> 0 Then
  5.       rslt = MsgBox("This number has already been entered. Do you wish to continue?", vbYesNo)
  6.       If rslt = vbNo Then
  7.          Me.Postcode = Null
  8.          Me.Postcode.SetFocus
  9.       End If
  10.    End If
  11. 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:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Postcode_BeforeUpdate(Cancel As Integer)
  2. Dim rslt As Integer
  3.  
  4.    If Nz(DLookup("[Postcode]", "Addresses", "[Postcode]='" & Me.Postcode & "'"), 0) <> 0 Then
  5.       rslt = MsgBox("This number has already been entered. Do you wish to continue?", vbYesNo)
  6.       If rslt = vbNo Then
  7.          Cancel = True
  8.          Me.Postcode.SetFocus
  9.       End If
  10.    End If
  11. End Sub
I'll try it and see!
Feb 15 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.