Field Data Validation in VBA Forms - Here's a way

[Greg Glynn]

Whilst VBA forms do not support data validation on field entry, here's
some code to buidl your own. This code validates input against a field
called AssetNoInput (Asset Number Input Field) and looks for the format
AA-99999 (Two Alpha (Uppercase), a "Dash", 5 Numeric) - Max of 8
characters.

You can change the CASE statements for your own requirements.

- - - - - - - - - -

Dim xAssetNoInput

Private Sub AssetNoInput_Change()

AssetNoInput = UCase(AssetNoInput)
For Count = 1 To Len(AssetNoInput)
Select Case Count
Case 1, 2
If InStr("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Mid(AssetNoInput,
Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 3
If Mid(AssetNoInput, Count, 1) <> "-" Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 4 To 8
If InStr("0123456789", Mid(AssetNoInput, Count, 1)) = 0 Then
AssetNoInput = xAssetNoInput
Beep
End If
Case 9
AssetNoInput = xAssetNoInput
Beep
End Select
Next
xAssetNoInput = AssetNoInput
CheckAssetOK
End Sub