Prevent duplicates on form level

[Mary Beth]

I have created a table called 'tblConsumer', and a form called 'frmConsumer'
with one of the data entry text boxes on the form for a 'RecipID'. On the
table level, I have created an index with Unique as 'yes', to prevent
duplicate 'RecipID's', however, I want a message box to pop up on the form
using 'RecipID_After Update' to warn the user that the RecipID is a
duplicate. Can someone please help me with the code for this? I am a new
user...thanks.

 

[Klatuu]

Actually, you do this in the Before Update event. the After Update is too
late. That is why the Before Update event has a Cancel argument, so you can
cancel the update if the input doesn't pass validation:

Private Function txtRecipId_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = " &
Me.txtRecipId Then
MsgBox"RecipID " & Me.txtRecipId & " is Already in the database"
Cancel = True
End If
End Sub

 

[Mary Beth]

Thank you for your answer/code and put it in exactly how you have it, however
I am having problems with the code highlighting yellow from "If....to Then".
I think that there should be double quotes after Me.txtRecipid, but even
after I put those in, I still get an error. Do you know what might be
causing this error?
--
Mary Beth

Actually, you do this in the Before Update event. the After Update is too
late. That is why the Before Update event has a Cancel argument, so you can
cancel the update if the input doesn't pass validation:

Private Function txtRecipId_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = " &
Me.txtRecipId Then
MsgBox"RecipID " & Me.txtRecipId & " is Already in the database"
Cancel = True
End If
End Sub
--
Dave Hargis, Microsoft Access MVP

I have created a table called 'tblConsumer', and a form called 'frmConsumer'
with one of the data entry text boxes on the form for a 'RecipID'. On the
table level, I have created an index with Unique as 'yes', to prevent
duplicate 'RecipID's', however, I want a message box to pop up on the form
using 'RecipID_After Update' to warn the user that the RecipID is a
duplicate. Can someone please help me with the code for this? I am a new
user...thanks.

 

[Klatuu]

Yes, got my syntax wrong, sorry. It should be:
If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = " &
Me.txtRecipId)) Then
^
Did not close the parens
Now, the syntax assumes the field RecipID in your tablle is a number. If it
is text, then it should be:
If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = '" &
Me.txtRecipId & "'")) Then
--
Dave Hargis, Microsoft Access MVP

Thank you for your answer/code and put it in exactly how you have it, however
I am having problems with the code highlighting yellow from "If....to Then".
I think that there should be double quotes after Me.txtRecipid, but even
after I put those in, I still get an error. Do you know what might be
causing this error?
--
Mary Beth

Actually, you do this in the Before Update event. the After Update is too
late. That is why the Before Update event has a Cancel argument, so you can
cancel the update if the input doesn't pass validation:

Private Function txtRecipId_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = " &
Me.txtRecipId Then
MsgBox"RecipID " & Me.txtRecipId & " is Already in the database"
Cancel = True
End If
End Sub
--
Dave Hargis, Microsoft Access MVP

I have created a table called 'tblConsumer', and a form called 'frmConsumer'
with one of the data entry text boxes on the form for a 'RecipID'. On the
table level, I have created an index with Unique as 'yes', to prevent
duplicate 'RecipID's', however, I want a message box to pop up on the form
using 'RecipID_After Update' to warn the user that the RecipID is a
duplicate. Can someone please help me with the code for this? I am a new
user...thanks.

 

[Mary Beth]

Thank you so much. This now works like a charm!!
--
Mary Beth

Yes, got my syntax wrong, sorry. It should be:
If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = " &
Me.txtRecipId)) Then
^
Did not close the parens
Now, the syntax assumes the field RecipID in your tablle is a number. If it
is text, then it should be:
If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = '" &
Me.txtRecipId & "'")) Then
--
Dave Hargis, Microsoft Access MVP

Thank you for your answer/code and put it in exactly how you have it, however
I am having problems with the code highlighting yellow from "If....to Then".
I think that there should be double quotes after Me.txtRecipid, but even
after I put those in, I still get an error. Do you know what might be
causing this error?
--
Mary Beth

Actually, you do this in the Before Update event. the After Update is too
late. That is why the Before Update event has a Cancel argument, so you can
cancel the update if the input doesn't pass validation:

Private Function txtRecipId_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[RecipID]", "tblConsumer", "[RecipID] = " &
Me.txtRecipId Then
MsgBox"RecipID " & Me.txtRecipId & " is Already in the database"
Cancel = True
End If
End Sub
--
Dave Hargis, Microsoft Access MVP


:

I have created a table called 'tblConsumer', and a form called 'frmConsumer'
with one of the data entry text boxes on the form for a 'RecipID'. On the
table level, I have created an index with Unique as 'yes', to prevent
duplicate 'RecipID's', however, I want a message box to pop up on the form
using 'RecipID_After Update' to warn the user that the RecipID is a
duplicate. Can someone please help me with the code for this? I am a new
user...thanks.