Data Validation (1 Viewer)

spearse

New member
Local time
Yesterday, 17:11
Joined
Jun 8, 2015
Messages
9
Working on building a table in Access that will contain only two fields: 1) Asset Tag Number and 2) Serial Number. The “Asset Tag Number” is always a six- (6) digit number; whereas, the “Serial Number” is always alphanumeric. Both fields will be inputted via a barcode scanner.

Two questions:

1) How do I set up a data validation so the Asset Tag Number cannot be accidentally scanned into the Serial Number field? (Already set validation for the Asset Tag Number)
2) In the case that an error message appears, can Access be setup to clear the field after the “OK” button is clicked upon reading the message?

If you need more information to help you answer my question – please do not hesitate to ask me.

Just a note: I am a green Access user.

Thanks!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:11
Joined
Feb 19, 2013
Messages
16,553
1) How do I set up a data validation so the Asset Tag Number cannot be accidentally scanned into the Serial Number field? (Already set validation for the Asset Tag Number)...
whereas, the “Serial Number” is always alphanumeric
[/QUOTE]if by this you mean it is always a mixture of number and letters, or just letters (as opposed to might be text, might be numbers, might be both) then you can use the isnumeric function - i.e. isnumeric(myfield)=false means it is not a number

2) In the case that an error message appears, can Access be setup to clear the field after the “OK” button is clicked upon reading the message?
Only if you use a form to handle your scanning. Tables do not have events for this sort of thing. You can easily create a form (with a datasheet view) which looks like a table, then you would do the validation in the controls before update event
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:11
Joined
Aug 30, 2003
Messages
36,118
Will it always be greater than 6 digits (you can use the Len() function)? Always be alphanumeric (you can use the IsNumeric() function, which it should fail)? Basically you need a rule or rules you can apply.

You can certainly clear the field. I normally use the before update event of the form, but for these you can use the same event of each control:

http://www.baldyweb.com/BeforeUpdate.htm

You can also use a validation rule, if it can be reduced to that.
 

spearse

New member
Local time
Yesterday, 17:11
Joined
Jun 8, 2015
Messages
9
Thank you for your replies so far.

1) As noted the serial number will always be alphanumeric. Two examples of what the serial number will look like are: 1) 4K5VM22, or 2) C02N60WEF2GC. The first one is 7 digits; whereas, the second one is 12.

2) Yes, although I did not mention it, I will be scanning into a form. My apologies.

I do not know how to program validation rules in (noob user...HA!), so any assistance here would be helpful. I am using Access 2010.

Thank you again!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:11
Joined
Aug 30, 2003
Messages
36,118
The link should be a start, and if a purely numeric value would be invalid, a test could be

Code:
If IsNumeric(Value) Then
  'code here for invalid entry
End If
 

spearse

New member
Local time
Yesterday, 17:11
Joined
Jun 8, 2015
Messages
9
Thank you....but....where do I insert the program text?
 

spearse

New member
Local time
Yesterday, 17:11
Joined
Jun 8, 2015
Messages
9
OK everyone...got it figured out.

All I needed to do for the validation rule for the Serial Number field was to write the following validation: NOT Like "######". So now if the user accidentally scans the Asset Tag Number in the scan is rejected and a error message now appears.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:11
Joined
Aug 30, 2003
Messages
36,118
Glad you found a solution.
 

Users who are viewing this thread

Top Bottom