Validating Data

[StacyStacy]

I have written some code to do the following:

When keying data into a new record and/or clicking onto the "Menu" button, which closes the form.

The User must enter a 3-digit Project Code. If the project code contains a null value, a message appears stating they must enter a 3-digit project code. If the Project code is less than 3 digits, another message appears stating that the Project Code must be 3 digits.

If the user does not enter the 3-digit code or leaves the Provider Code field blank, I want the message box to appear and the cursor placed again in the Provider Code field. If the User enters a 3-digit code, then I want the cursor to be placed in the "Fed Tax ID" field.

***************************

Here's my code placed in the "After Update" and "Lost Focus" of the Provider Code field:

Private Sub Provider_Code_AfterUpdate()
If IsNull(Me!Provider_Code) Then
MsgBox "This Field, 'Provider Code' Must Be Completed. Please Enter A 3-Digit Privider Code!", vbExclamation
Provider_Code.SetFocus
Cancel = True
End If

If Len(Me.Provider_Code) < 3 Then
MsgBox "Please Enter A 3-Digit Privider Code!"
Else: Me.Provider_Code.SetFocus
End If

If Len(Me.Provider_Code) > 3 Then
Me.Fed_Tax_ID__.SetFocus
Else
End If

End Sub

*****************
Here's my code placed in the "After Update" of the "Form":

Private Sub Form_AfterUpdate()
If IsNull(Me!Provider_Code) Then
MsgBox "This Field, 'Provider Code' Must Be Completed. Please Enter A 3-Digit Privider Code!", vbExclamation
Provider_Code.SetFocus
Cancel = True
End If
End Sub


It somewhat works. The messages appears, but the cursor is not placed into the Project Code field if there is a null value or less than 3 digits entered.


Please help. Thanks.

 

[dcx693]

You didn't post what the problem was. Is Provider_Code a numeric or text field? If it's numeric, that's your problem right there.

 

[StacyStacy]

The Provider Code field is a TEXT field.

 

[dcx693]

You do have this code:
Else: Me.Provider_Code.SetFocus
telling your program only to set the focus to the Provider_Code field if the entered code is not < 3 digits. Remove the Else: keyword.

 

[StacyStacy]

I tried it. It didn't work. Here's the edited code based on your suggesstion:

*************************

Private Sub Provider_Code_AfterUpdate()
If IsNull(Me!Provider_Code) Then
MsgBox "This Field, 'Provider Code' Must Be Completed. Please Enter A 3-Digit Privider Code!", vbExclamation
Me.Provider_Code.SetFocus
Cancel = True
End If

If Len(Me.Provider_Code) < 3 Then
MsgBox "Please Enter A 3-Digit Privider Code!"
Me.Provider_Code.SetFocus
End If

If Len(Me.Provider_Code) > 3 Then
Me.Fed_Tax_ID__.SetFocus
End If

End Sub

************
What is incorrect?

 

[dcx693]

OK, there are a few problems here.

First, I wouldn't place the code in the AfterUpdate event of the text box. Why store the field (after the update) when you don't even want an incorrect code? I'd place it in the before update event. Use code like this:

Private Sub Provider_Code_BeforeUpdate(Cancel As Integer)
  If Len(Me.Provider_Code) < 3 Then
    MsgBox "Please Enter A 3-Digit Privider Code!"
    Cancel = True
  End If
End Sub

No need to reset the focus because the Cancel=True part stops the focus from moving to another field.

Also, you might want to use a select case statement for the Len of the Provider_Code field or some Exit Sub statements so that you don't wind up testing out all the options. Right now, your sub is testing if the Provider_Code is Null, then it tests to see if the Len is <3, then it tests if it's greater than 3. Why run through all the tests if only one will be true?

Why is that last statement ">3" don't you want the focus moved if the Provider_Code is exactly 3 digits? I wouldn't even explicitly use a SetFocus statement if the Len =3. Just set the tab order of your controls.

 

[StacyStacy]

It doesn't work. The message doesn't appear.

Also, please explain to me why I would place this code in the before update when it needs to validate what has been placed into the field, which would seem to be the "After Update"?

Everything almost worked with my original code but the cursor would not go back to the Provider Code field and the form would close when the value of the field was null or less than 3digits.

Another question: Why would it not be ok to have 3 different validations on one field? One for a null value. Another for less than a 3-digit # and the last one for a value that is 3-digits.

 

[dcx693]

Hmm...the code I posted did not work? Strange since I tested it and copied it straight from Access (your misspelling of Provider included).

You could place the code into the AfterUpdate event, but why have your database store the data before testing it? It actually doesn't need to. It can test the Len of the entry without storing it first.

If you wish to use your original code, perhaps you can just put a Cancel=True instead of the SetFocus statements. I haven't tested it.

It's certainly ok to have as many validations on your fields as you would like. You could put a million validations on one field. As a "programmer", you're concerned mostly with getting to the end result. That's job number 1. But in getting to the end result, you should be trying to get there the most efficient way. Only one of these conditions is going to be true:
IsNull(Provider_Code)
Len(Provider_Code)<3
Len(Provider_Code)>3
why test for all of them? Why not stop testing once you've found then the one condition that is true? Your code will barely run any faster, but the concept is applicable to many programming situations.

 

[StacyStacy]

I still need some help. Can anyone assist me? Thanks in advance.

 

[dcx693]

That sample code I posted was to test for the case when the Provider_Code entry was less than 3 digits. Are you saying that when you enter a 1 or 2 digit code that it doesn't correctly display the error message?

 

[dcx693]

Very good observation Pat.

 

[StacyStacy]

Thanks.

I removed all events on the Provider_Code field and placed it on the BeforeUpdate on the Form. Here's my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!Provider_Code) Or Len(Me.Provider_Code) < 3 Then
MsgBox "This Field, 'Provider Code' Must Be Completed. Please Enter A 3-Digit Privider Code!"
Me.Provider_Code.SetFocus
Cancel = True
End If

End Sub

It didn't work the way I need it to work. I can still go to the next field and even pressing the Menu button, which closes the form. Once the Menu button is pressed, the error message does display, but the form still closes. Help.

 

[dcx693]

You still need to have some code in the Before Update event of the Provider_Code field if you don't want the focus to move out of the field if the entry is not valid.

Pat Hartman's post about using the Before Update event of the form handles the case where a user never enters the Provider_Code field, but you want to keep the user in the Provider_Code field, so you need code there.

Put code like this in the BeforeUpdate event of the Provider_Code field:
Private Sub Provider_Code_BeforeUpdate(Cancel As Integer)
If Is Null(Me.Provider_Code) Or Len(Me.Provider_Code) < 3 Then
MsgBox "Please Enter A 3-Digit Privider Code!"
Cancel = True
End If
End Sub

I don't think you need the SetFocus statement there since it cancels updates to the field and won't move the focus somewhere else.

Let's try to get the field working, then we'll figure out why the form is still closing, OK?

 

[StacyStacy]

It works!
But ... When I add a new record, the first field is the Provider Code. If I tab to the next field without entering data, the error msg does not appear. I put this code in Lost Focus event. I received the error message, but the cursor did not stay in the Provider Code field.

 

[StacyStacy]

Question:

So I have to have code in the BeforeUpdate on the FORM and on the BeforeUpdate on the field also?

 

[dcx693]

I didn't consider the case where it would be the first field. I think the On Exit event is better than Lost Focus because lost focus only occurs after the focus has already moved off the field. Though I think you could use Lost Focus if you then placed a SetFocus statement in there.

If you successfully make this change to the code for the field though, you may not need code in the Before Update event of the form. If users are forced to enter a valid code for new records, and if they cannot exit the field in future without a valid entry in the field (and they can only edit the field by entering it) then code at the form level is not needed.

 

[StacyStacy]

Thank you. It worked. Please check back to periodically today to see if I have any more problems.

Thanks a million!

 

[dcx693]

No problem. "More problems"? Why would there be more problems?

 

[StacyStacy]

Thanks a million!!

 

[StacyStacy]

Thanks Pat. Can you provide me with a link only to expedite time? Thanks Again.