469,282 Members | 1,704 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

Just a warning on duplicate records

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
9 9534
MMcCarthy
14,534 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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
Thank you all, this solution works great.
Dec 5 '06 #7
MMcCarthy
14,534 Expert Mod 8TB
Thank you all, this solution works great.
You're welcome.
Dec 5 '06 #8
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
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.

Similar topics

1 post views Thread by Peter | last post: by
2 posts views Thread by Carroll | last post: by
4 posts views Thread by Thomas Arthur Seidel | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.