Create custom error message if duplicate entered (is indexed)

[Pete]

I have a unique tracking number field that is indexed so
that no duplicates can be entered. Ideally, if a
duplicate is entered, I would like a custom error message
to be displayed as soon as the cursor moves to the next
text box. If this is not possible and will only display
an error message once the record is being updated, I would
still like it to display a custom error message.

Currently, once a duplicate is entered and record updated,
a default Access message is displayed that could be
confusing to an unfamiliar user (which will be the
case). Any help would be greatly appreciated!

Pete.

 

[Dan Artuso]

Hi,
You can use DLookup in the control's AfterUpdate event:

If Not IsNull(DLookup("[TrackingNumber]", "yourTable", "TrackingNumber = " & Me.yourControl )) Then
MsgBox "hi"
End If

You have to substitute the correct field, table and control names in the above

 

[Wayne Morgan]

Dan's answer should get you the "as soon leaving the control" option. For
future reference, to trap the error Access is generating, you would use the
Form's Error event. This is where data errors, such as the index violation,
will show up.

 

[Pete]

Hi,

I've tried your suggestion. I used the code builder
function to enter:

If Not IsNull(DLookup("[TrackingNum]", " Occurrence Ledger
Table ", " TrackingNum = " & Me.Tracking Number )) Then
MsgBox " You have created a duplicate Tracking
Number. Please ensure you are entering the next
sequential Tracking Number. "
End If

Where "TrackingNum" is table field; "Occurrence Ledger
Table" is the table; and "Tracking Number" is the control
name on my form. But as soon as I enter a tracking
number and move to the next control, I get a
compile/syntax error I can't fix. What am I doing wrong?

Peter.

-----Original Message-----
Hi,
You can use DLookup in the control's AfterUpdate event:

If Not IsNull(DLookup

("[TrackingNumber]", "yourTable", "TrackingNumber = " &
Me.yourControl )) Then

MsgBox "hi"
End If

You have to substitute the correct field, table and control names in the above
--
HTH
Dan Artuso, Access MVP


"Pete" <[email protected]> wrote in

message news:[email protected]...

 

[Dan Artuso]

Hi,
Is there really a space in your control name? Not a very good idea.
Access wil allow it, but if you notice, after you type the period in Me.
the intellisense drop down list will show: Tracking_Number
In my opinion, you should never have spaces in the names of *any* objects you create,
it only causes problems.
Add the underscore and it should fix your problem

--
HTH
Dan Artuso, Access MVP

Hi,

I've tried your suggestion. I used the code builder
function to enter:

If Not IsNull(DLookup("[TrackingNum]", " Occurrence Ledger
Table ", " TrackingNum = " & Me.Tracking Number )) Then
MsgBox " You have created a duplicate Tracking
Number. Please ensure you are entering the next
sequential Tracking Number. "
End If

Where "TrackingNum" is table field; "Occurrence Ledger
Table" is the table; and "Tracking Number" is the control
name on my form. But as soon as I enter a tracking
number and move to the next control, I get a
compile/syntax error I can't fix. What am I doing wrong?

Peter.

-----Original Message-----
Hi,
You can use DLookup in the control's AfterUpdate event:

If Not IsNull(DLookup

("[TrackingNumber]", "yourTable", "TrackingNumber = " &
Me.yourControl )) Then

MsgBox "hi"
End If

You have to substitute the correct field, table and control names in the above
--
HTH
Dan Artuso, Access MVP


"Pete" <[email protected]> wrote in

message news:[email protected]...

.