Just a warning on duplicate records

[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?

[MMcCarthy]

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
 

[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.

[MMcCarthy]

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

[NeoPa]

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.

[MMcCarthy]

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

[jjstevens]

Thank you all, this solution works great.

[MMcCarthy]

Thank you all, this solution works great.

You're welcome.

[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

[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!