Clear field after duplicate or invalid entry (1 Viewer)

[hardhitter06]

Hi,

I have this code:

Private Sub AssetNumber_BeforeUpdate(Cancel As Integer)
 Dim Answer As Variant
 strIdNumber = Me.AssetNumber
 
 Answer = DLookup("[AssetNumber]", "tblMain", "[AssetNumber] = '" & Me.AssetNumber & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Asset Number Found." & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 Cancel = True
 Me.AssetNumber.Undo
 

 End If
If Len(AssetNumber) <> 10 Then
    MsgBox "Asset must be 10 characters, re-enter.", vbOKOnly, "Error"
    Cancel = True
    Me.AssetNumber.Undo
End If
End Sub

which checks to see if an AssetNumber already exists in the system or if you have entered the correct length of an AssetNumber (10 characters/digits)

I know this is overkill but is there a way to insure the last character/digit of the asset number is a ZERO and not the letter "O"? lol - because of the people i work with...

Thank you

 

[vbaInet]

Have you thought about using the Input Mask property of the textbox? See the wizard for examples and explanation.

 

[RainLover]

There are Three similar Functions.

Left, Mid and Right. You can use these to find a character or characters on the Left the Middle or the Right.

Search Access Help for RIGHT. This will give you the last Character where you can then apply an If Statement to see if it is correct.

 

[hardhitter06]

Have you thought about using the Input Mask property of the textbox? See the wizard for examples and explanation.

Yeah you're 100 percent right, can't believe I overlooked this. I had an input mask set up for letters and digits, i just changed the last character/number to a "0" which doesn't allow letters

Thanks for your help

 

[vbaInet]

No problemo!