Field Validation (1 Viewer)

[Tor_Fey]

Good Morning;

I’m looking for help with text validation, what I need to do is ensure a field meets certain criteria before the user can continue.

The Filed needs to start with two letters and end in one of three letters, so for example:

• MA12345678A

The end letter will only ever be the fixed letters A, B or C but the start can be anything really depending on the users first and last name.

I want this to be a manual field that users can type in but it must start with any two letters and end in only A, B or C.

Is this possible to do using VBA?

Kind Regards
Tor Fey

 

[arnelgp]

on the fields BeforeUpdate event, write this code:


Private Sub field1_BeforeUpdate()
If [field1] Like "[A-Z][A-Z]*[ABC]" then
Else
Cancel = True
Msgbox "Not a valid entry."
End If
End Sub

 

[missinglinq]

Haven't tested this, but I think you'd also have to force the user into the Control, on starting a New Record, and checking for data using the OnExit event, cancelling that event if the Control is empty...otherwise the user could simply ignore the Control, enter nothing, and the validation would be circumvented.

Linq ;0)>

 

[The_Doc_Man]

Depending on what you are doing, you might need to have this in a couple of parts...

First, write some VBA function that looks at the string and returns a Boolean result. Perhaps as simple as to return TRUE if the string is of a valid format. Arnel's method for string verification is as good as any other, though you might also wish to upcase the string, check its length, etc. Where you define the function is of course up to you, but if more than one form needs to do this validation, put it in a general module and make it public so it can be activated from any form.

Second, call that code in a couple of places for any form that needs it. The control's LostFocus event is one place, but you should also perhaps address the control from the form's BeforeUpdate event. If there is an issue, you cancel the update and force the focus back to the control with that string in it. That way, you catch implied as well as explicit updates.