Validation of a field

[Eric Starn]

I have a asset table with such fields as [Asset ID], [Type], [Serial Number],
etc..
The [Asset ID] field is already the PK for the table. I have a data entry
form for new assets but would like to validate that the user is not making a
duplicate record based off the [Serial Number] field and then tell the user
that is the case. I think this can be done in setting up a validation rule
for that field but am not sure how to accomplish it to get the results I am
looking for.

I appreciate any suggestions.

Eric

 

[Allen Browne]

If you want to block the possibility of a duplicate serial number, just open
the table in design view, select the serial number field, and set the
Indexed property (lower pane of the table design window) to:
Yes (No Duplicates)

If you want to give a warning but let the user over-ride it, you will need
to do that at the form level. Use the Form_BeforeUpdate event to check if a
duplicate exists in the table, using DLookup():
http://allenbrowne.com/casu-07.html

 

[Eric Starn]

Great
Thanks for the help
Eric

If you want to block the possibility of a duplicate serial number, just open
the table in design view, select the serial number field, and set the
Indexed property (lower pane of the table design window) to:
Yes (No Duplicates)

If you want to give a warning but let the user over-ride it, you will need
to do that at the form level. Use the Form_BeforeUpdate event to check if a
duplicate exists in the table, using DLookup():
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a asset table with such fields as [Asset ID], [Type], [Serial
Number],
etc..
The [Asset ID] field is already the PK for the table. I have a data entry
form for new assets but would like to validate that the user is not making
a
duplicate record based off the [Serial Number] field and then tell the
user
that is the case. I think this can be done in setting up a validation rule
for that field but am not sure how to accomplish it to get the results I
am
looking for.

I appreciate any suggestions.

Eric

 

[Wayne-I-M]

I will take a guess here (may be wrong).
I assume you can have different type of Assets with the same serial number
ie.
Car = number 1234
Table = 5678
Apple = number 1234
So in this case it is the combination of "asset type" and "number" that you
need to look for ???

In this case then add this to the [Serial Number] AfterUpdate event - I
assume you add [Type] before this - if not then just alter the code
I have use txtSerialNumber and txtType as the form control that you enter
stuff into on your form - change them to what they really are and TABLENAME

Untested code but should be OK (I don't have access on this machine)

Private Sub Serial Number_AfterUpdate()
If ((DLookup("[ID]", "[TABLENAME]", "[Serial Number] ='" &
Form!txtSerialNumber & "' AND [Type] = '" & Form!txtType & "'"))) Then
Beep
MsgBox "This stuff may already be on the system - or some other
message", vbOKOnly, "New Asset checker"
End If
End Sub


Good luck - hope this helps