Duplicate Records - Msgbox (Again!)

[Guest]

I know there have been a number of posts regarding this issue but I'm afraid
I can't get any of the solutions to work.
I want to prevent duplicate records from being entered on my form. I have
indexed my "RegNo" field in my "tblDetails" to Yes (No Duplicates). I would
like a warning msgbox to pop up on the, say, Before Event, so that, in the
event of a duplicate, the user does not enter all the fields on the form
before being informed that the record can't be saved. Is there a tried and
true Event Procedure?
Thanks
Anne

 

[Chris Reveille]

Here is something I ran by on a previous post. I have used
the second example.

In the BeforeUpdate event of the control try something like
the following:


if not isnull(dlookup("Field1","myTable","PK1=" & me.pk1)) then
msgbox "Record Already exists!"
cancel=true
endif

or

If Not IsNull(DLookup("SocialSecurityNumber", "client Info", _
"[socialsecuritynumber]=" & "'" &
Me.SocialSecurityNumber & "'")) _
Then
MsgBox "Record Already exists!"
' Cancel = True
Else: MsgBox "Looks Good"
End If

The Dlookup will return a null if there is no match so you
are looking for a Not null condition to indicate a
duplicate. If any of your fields are text you will have to
wrap the value in Quotes so that the value is passed to the
Dlookup as a literal string.

Chris

 

[Guest]

Hi Anne,
As an alternative you could use the Exit Event of the RegNo TextBox to
attempt Save operation, if it fails then because of your table constraint
then you can prevent them from leaving the field and warn them accordingly.

Private Sub RegNo_Exit(Cancel As Integer)
On Error GoTo ErrUpdate

DoCmd.RunCommand acCmdSaveRecord
Exit Sub

ErrUpdate:
MsgBox "The value that you have entered is not unique. Please enter a
different value", vbExclamation
Cancel = True

End Sub

 

[Guest]

Thanks for these codes Geoff and Chris. I tried each of them without
success. I know it's something I'm doing and my knowledge is not great so
can't really give any more info. But thanks anyway for posting these codes.
I'll keep trying.
Anne

Hi Anne,
As an alternative you could use the Exit Event of the RegNo TextBox to
attempt Save operation, if it fails then because of your table constraint
then you can prevent them from leaving the field and warn them accordingly.

Private Sub RegNo_Exit(Cancel As Integer)
On Error GoTo ErrUpdate

DoCmd.RunCommand acCmdSaveRecord
Exit Sub

ErrUpdate:
MsgBox "The value that you have entered is not unique. Please enter a
different value", vbExclamation
Cancel = True

End Sub

 

[Guest]

I did it! Talk about obsessive compulsive. I'm not sure why I couldn't get
it to work before but there you go. I was using Geoffs code on exit the time
I got it to work so there it will stay. Thank you.
Anne

Thanks for these codes Geoff and Chris. I tried each of them without
success. I know it's something I'm doing and my knowledge is not great so
can't really give any more info. But thanks anyway for posting these codes.
I'll keep trying.
Anne

Hi Anne,
As an alternative you could use the Exit Event of the RegNo TextBox to
attempt Save operation, if it fails then because of your table constraint
then you can prevent them from leaving the field and warn them accordingly.

Private Sub RegNo_Exit(Cancel As Integer)
On Error GoTo ErrUpdate

DoCmd.RunCommand acCmdSaveRecord
Exit Sub

ErrUpdate:
MsgBox "The value that you have entered is not unique. Please enter a
different value", vbExclamation
Cancel = True

End Sub

-----Original Message-----
I know there have been a number of posts regarding this
issue but I'm afraid
I can't get any of the solutions to work.
I want to prevent duplicate records from being entered on
my form. I have
indexed my "RegNo" field in my "tblDetails" to Yes (No
Duplicates). I would
like a warning msgbox to pop up on the, say, Before Event,
so that, in the
event of a duplicate, the user does not enter all the
fields on the form
before being informed that the record can't be saved. Is
there a tried and
true Event Procedure?
Thanks
Anne
.

 

[Guest]

Glad to be of help Anne. {

I did it! Talk about obsessive compulsive. I'm not sure why I couldn't get
it to work before but there you go. I was using Geoffs code on exit the time
I got it to work so there it will stay. Thank you.
Anne

Thanks for these codes Geoff and Chris. I tried each of them without
success. I know it's something I'm doing and my knowledge is not great so
can't really give any more info. But thanks anyway for posting these codes.
I'll keep trying.
Anne

Hi Anne,
As an alternative you could use the Exit Event of the RegNo TextBox to
attempt Save operation, if it fails then because of your table constraint
then you can prevent them from leaving the field and warn them accordingly.

Private Sub RegNo_Exit(Cancel As Integer)
On Error GoTo ErrUpdate

DoCmd.RunCommand acCmdSaveRecord
Exit Sub

ErrUpdate:
MsgBox "The value that you have entered is not unique. Please enter a
different value", vbExclamation
Cancel = True

End Sub

-----Original Message-----
I know there have been a number of posts regarding this
issue but I'm afraid
I can't get any of the solutions to work.
I want to prevent duplicate records from being entered on
my form. I have
indexed my "RegNo" field in my "tblDetails" to Yes (No
Duplicates). I would
like a warning msgbox to pop up on the, say, Before Event,
so that, in the
event of a duplicate, the user does not enter all the
fields on the form
before being informed that the record can't be saved. Is
there a tried and
true Event Procedure?
Thanks
Anne
.

 

[Hermie]

Hello geoffs

I put your code also in one of my forms and works great, only when there is
a composite key it not works.

Herman

Hi Anne,
As an alternative you could use the Exit Event of the RegNo TextBox to
attempt Save operation, if it fails then because of your table constraint
then you can prevent them from leaving the field and warn them accordingly.

Private Sub RegNo_Exit(Cancel As Integer)
On Error GoTo ErrUpdate

DoCmd.RunCommand acCmdSaveRecord
Exit Sub

ErrUpdate:
MsgBox "The value that you have entered is not unique. Please enter a
different value", vbExclamation
Cancel = True

End Sub