VBA Validation

[Sammatter]

I would like some help in how I am to validate a text field to ensure that it is numeric and cannot be negative. Also in this text box the user is not allowed to continue without entering the correct data in the first text field.
This will need to be using VBA in Microsoft Access 2003.
I have attempted using IsNumeric; however characters are still allowed to be entered. I am unsure on how to proceed to clear the characters that have been entered, or this method that I am using is incorrect and there is a far better method. Would appreciate any help. Thank you.

[RuralGuy]

Me.YourControlName.UnDo will put a bound control back to its previous value. IsNumeric should work just fine and > 0 for the non negative test. Validation should be accomplished in the control's BeforeUpdate event and set Cancel = True to cancel the update. I would think just letting the user know that *all* numbers is a requirement would be sufficent, but that's your call.

[Sammatter]

I have attempted to do all that has been stated and this is what it looks like, however I think there are still some flaws. I have attempted to uncover what the flaws are, but what ever I have done does not make any of the slightest difference. Can somebody please give me an idea of where about I am going wrong. Would be very much appreciated, thank you.

Private Sub Branch_Code_BeforeUpdate(Cancel As Integer)

Me.YourControlName.Undo

Branch_Code.SetFocus
If Not IsNumeric(Branch_Code) Then 'This makes sure that the number used is numeric
MsgBox "Invalid Format" 'This is the message box that is shown when a character has been entered
Else
Branch_Code.Undo ' This clears the character that has been entered

End If

If Branch_Code = "" Or IsNull(Branch_Code) Then 'This makes sure that the field cannot be left blank
MsgBox " You must enter a value" ' This is the message box that is given when nothing is entered
Cancel = True
ElseIf Len(Branch_Code) <> 2 Then ' This makes sure that there must be two characters in this field
MsgBox " This field must be 2 characters only"
Cancel = True
End If

If Branch_Code >= 0 Then 'The code must not be a negative number
MsgBox "Must not be negative" ' This is the message box that is given when a negative number is entered.
End If

[missinglinq]

There are a number of flaws. Your first line of code

Me.YourControlName.Undo
 

would be undoing any data that you've entered, before it's even been tested, assuming that you’d used the name of the your actual control, Branch_Code, rather that the “generic” name Allan used in his example! Once you correct the textbox name, you’ll never be able to add/edit a record., and

Branch_Code.SetFocus

will place you in an endless loop, repeatedly sending you back to the textbox.

Next, what Dataype is the Branch_Code? Is it actually Numeric or is it Text. Identifying numbers such as this, or telephone numbers or Social Security Numbers should always be Text. You should only use a Numeric datatype if you’re actually going to do math with the “numbers.” If you actually use a Numeric datatype, Access will actually strip off any leading zeros, which will give you problems if you have Branch_Code's like 01, 02, etc.

These lines actually check if the data consists of the characters 0-9, sending up a messagebox if it isn’t, but only undoes the data, with the Else, if it actually is made up of numeric characters!

If Not IsNumeric(Branch_Code) Then 
MsgBox "Invalid Format" 
Else
Branch_Code.Undo 
End If
 

These lines need to be in the Form’s Before update event, otherwise the user can simply enter nothing in the field. If nothing is entered in the field, the textbox BeforeUpdate event is never triggered

If Branch_Code = "" Or IsNull(Branch_Code) Then 
MsgBox " You must enter a value" 
Cancel = True
ElseIf Len(Branch_Code) <> 2 Then ' This makes sure that there must be two characters in this field
MsgBox " This field must be 2 characters only"
Cancel = True
End If

These lines can go in the Branch_Code_BeforeUpdate, but this

If Branch_Code >= 0 Then 

needs to be this

If Branch_Code <= 0 Then

to throw up your message if it’s negative. The general rule is that code checking the correctness of entered data goes in the control’s BeforeUpadate event, but code checking whether any data has been entered at all goes in the Form’s BeforeUpdate event.

Welcome to Bytes!

Linq ;0)>

[missinglinq]

I think this code does what you want. I've changed some of the messages a little to make it clearer to the users which field you're talking about. This is always a good idea, especially when using the form's BeforeUpdate event , as the textbox in question may not be the last field filled in.

Private Sub Branch_Code_BeforeUpdate(Cancel As Integer)
 
If Not IsNumeric(Branch_Code) Then 'This makes sure that the number used is numeric
  MsgBox "Invalid Format in Branch_Code field! Only Numeric characters allowed!" 'This is the message box that is shown when a character has been entered
  Cancel = True
  Me.Branch_Code.Undo ' This clears the character that has been entered
End If
 
If Len(Branch_Code) <> 2 Then ' This makes sure that there must be two characters in this field
 MsgBox " This field must be 2 Numeric characters only!"
 Cancel = True
 Me.Branch_Code.Undo
End If
 
If Branch_Code <= 0 Then 'The code must not be a negative number
MsgBox "Branch_Code field must not be negative" ' This is the message box that is given when a negative number is entered.
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Branch_Code = "" Or IsNull(Branch_Code) Then 'This makes sure that the field cannot be left blank
  MsgBox " You must enter a value in Branch_Code field" ' This is the message box that is given when nothing is entered
  Cancel = True
  Me.Branch_Code.SetFocus
End If

Linq ;0)>