Validation Rule Blues

[rnickels]

Hello,

I have been searching for the solution to this problem for a while with no luck,

I have a form where users can edit data.

One of the fields is PaymentAmount and I want to enforce that users can only enter numbers (no letters). Can someone please show me how to do this?

Also I have a date field (in short date format). I want to enforce that users only enter numbers and that they must enter all the numbers in the following format (xx/xx/xxxx).

This would help me so much if anyone can offer a solution.

Thanks,
Rob

 

[missinglinq]

To allow only numeric entry (as well as decimal points, Backspace, Enter, etc)

Private Sub PaymentAmount_KeyDown(KeyCode As Integer, Shift As Integer)

Select Case KeyCode
Case 48, 49, 50, 51, 52, 53, 54, 55, 56, 57
Case 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 190, vbKeyDelete, vbKeyBack, vbKeyReturn
' this seperates the case statements. This is an access bug in earlier versions and making
' it do the x= y then it defeats the bug
x = y

Case Else

KeyCode = 0
End Select

End Sub

For the Date field, goto Properties - Data and set the Input Mask to 99/99/0000;0;_

Linq

 

[rnickels]

Thank you

Thanks, the numeric only for the paymentamount field works great but I am still struggling with enforcing the date field.

I need some sort of validation rule on the field itself. I get a generic error message if I don't input he date as (xx/xx/xxxx) but it does not hold the focus to this field. When the user clicks on Save to save the data and close the form, if the date field has been entered incorrectly I get an error message.
I want to trap the error straight away.

Thanks again.
Rob

 

[mhartman]

Hello:

For your date, use an "Input Mask" that will insure that a user enters a valid date!

Regards
Mark

 

[missinglinq]

OK, let's trap the error at the form level! You have the Input Mask set for your field, so let's trap the error if the data doesn't match the mask (Error 2279) and throw up a custom error message.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim Message As String
If DataErr = 2279 Then 'Value entered not in correct format
Message = "You Did Not Enter a Valid Value For This Field:  " & Me.ActiveControl.Name
Response = MsgBox(Message, vbExclamation, "Data For This Field is Not Valid/Complete")
Response = acDataErrContinue
End If
End Sub

This message will popup if the user enters a partial or invalid date and exits the field or tries to move to another record. When the messagebox is cleared the cursor will still be in the textbox.

If the user enters a partial/invalid date and attempts to close the form the messsagebox will also come up. When it's cleared this time, an Access messagebox will come up telling the user that the record can't be saved because the data is incorrect and asking if they want to close anyway. If they decide to exit anyway (and there may be times when they want to do this) and hit Yes, the record will be dumped. If they hit No, the cursor will still be in the textbox so that they can correct the data.

The only caveat here is that all this will happen for any field on the form that has an Input Mask in place. Because of this, the message I put in the messagebox is generic "You Did Not Enter a Valid Value For This Field" and I included code that will add the name of the field at the end of this, so the user will see

"You Did Not Enter a Valid Value For This Field: YourTextboxName"

where YourTextboxName will be the name of the control that actually tripped error.

 

[missinglinq]

Since we've talked about using Input Masks I'm going to post this mini-tutorial I give people when the subject comes up:

A good point to remember when using imput masks is that the user may not always arrive at the control by tabbing to it from the previous control, but may click on the control. If a mask is being used and the user doesn't click to insert the cursor at the very beginning of the text box, he/she may start to enter data without realizing that they're not at the beginning. When they leave the control they'll get an error message because the data wasn't entered as the input mask dictated. They'll then have to go back and re-enter the info. The way to avoid this is to use something like this:

Private Sub YourControlName_Click()
    YourControlName.SelStart = 0
End Sub

Even tabbing to the control can be problematic if in the Options for the database the default behavior for "Entering Field" is set to anything other than "Go to start of field." The answer to this is to set the cursor to the beginning of the field when the field is tabbed into:

Private Sub YourControlName_GotFocus()
    YourControlName.SelLength = 0
End Sub

Linq

 

[kodiak385]

Given that a control has to have focus before it can be manually edited... is there any reason not to just circumvent the On Click event and only use On GotFocus? I'm still learning this, but it seems to me that GotFocus would cover both methods here.

Comments?

 

[missinglinq]

You don't really want/need to run validation code every time the user happens to tab thru the control (which using the GotFocus event would do) but only if the user actually enters/edits data in the control.