Writing Custion Messages in Access 2003

[brydone via AccessMonster.com]

I have a database in Access 2003. Apart from the standard ID field, I have a
field called TrackingNumber which is a text field for bar code numbers (text
and numbers). I have indexed this field disallowing duplicates. This field
takes input from a bar code scanner and then automatically moves onto the
next field for entry. What I would like is that if a duplicate bar code is
scanned in, a message should appear warning the inputter that this is a
duplicate entry and the user be presented with and option of Cancel whick
will clear the fiels of the scanned bar code and the other option to rescan
the bar code. At the moment the standard VB error message is beng displayed
with End and Debug as options. Obviousley, I don't want the inputter to have
the debug option.

Any help would really be appreciated.

Many thanks......

 

[Stefan Hoffmann]

hi,

I have indexed this field disallowing duplicates. This field
takes input from a bar code scanner and then automatically moves onto the
next field for entry.

Before moving to the next field, test if you can save it, e.g.

If DCount("*", _
"yourTable", _
"TrackingNumber = '" & txtTreackingNumber.Value & "'") = 0 _
Then
' move to next field
Else
MsgBox "Tracking number already exists."
End If



mfG
--> stefan <--

 

[Tom van Stiphout]

On Fri, 21 Nov 2008 13:40:15 GMT, "brydone via AccessMonster.com"

In the TrackingNumber's BeforeUpdate event write:
if not isnull(DLookup("TrackingNumber", "myTable", "TrackingNumber='"
& Me.TrackingNumber & "'")) then
Msgbox "Arrrcchhh, this number already exists"
Cancel = True
end if
Of course you may have to change the names to the ones in your
database. The strategy used here is to use DLookup to find a matching
record and if found (not isnull), then that's a problem. Also note how
I'm wrapping the tracking number in single quotes, which is required
for text lookups.
"Cancel=True" is important; it prevents the user from moving off of
this field until a trackingnumber that is not found is entered.

I'm sure you can figure out the rest, or post back with more
questions.

-Tom.
Microsoft Access MVP