Full Version: Validation If statement
UtterAccess Forums > Microsoft® Access > Access Forms
dashiellx2000
I have a field where the user must enter a claim number if the payment was made by an insurance company. I have set up a ComboBox for the payment type and a text box for the claim number. I tried using the following code but I keep getting an error for 'Object Required'.
Private Sub ClaimNumber_LostFocus()
If Me.PymtDesc = "Insurance Payment" & Me.ClaimNumber Is Null Then
MsgBox "A Claim Number Must be entered for all Insurance Payments", vbOKOnly
Me.ClaimNumber.SetFocus
Else: Me.RefundType.SetFocus
End If
End Sub
Any help is appreciated.
Thanks.
R. Hicks
Try this:
CODE
Private Sub ClaimNumber_LostFocus()
If Me.PymtDesc = "Insurance Payment" Then
  If Len(Me.ClaimNumber & vbNullString) = 0 Then
    MsgBox "A Claim Number Must be entered for all Insurance Payments", vbOKOnly
    Me.ClaimNumber.SetFocus
  End If
Else
  Me.RefundType.SetFocus
End If
End Sub

RDH
Millennas
Use the BeforeUpdate event, and don't use periods before controls that you have named yourself
Private Sub ClaimNumber_BeforeUpdate(Cancel)
If Me!PymtDesc = "Insurance Payment" AND IsNull(Me!ClaimNumber)=True Then
MsgBox "A Claim Number Must be entered for all Insurance Payments"
Me!ClaimNumber.SetFocus
Cancel = True
Else
Me!RefundType.SetFocus
End If
End Sub
R. Hicks
Where did you get this information ???
Using the dot (.) instead of the bang (!) is much faster in this case ...
The user created object becomes an object of the form .. so it can be referenced using the dot ...
DH
dashiellx2000
I tried using the both code examples and neither worked. I tabbed into and out of the field without any msgbox or error. I think I would like to try and refine the code provided by RHicks as I understand it better
If Me.PymtDesc = "Insurance Payment" Then
If Len(Me.ClaimNumber & vbNullString) = 0 Then
MsgBox "A Claim Number Must be entered for all Insurance Payments", vbOKOnly
Me.ClaimNumber.SetFocus
End If
Else
Me.RefundType.SetFocus
End If
Could the problem be with If Len(Me.ClaimNumber & vbNullString) = 0 and no =Null or is null?
I'm using the lost focus event as I don't want to annoy the user unless they attempt to bypass the field without entering anything.
Thanks.
dashiellx2000
Well, I changed it to = isNull and I get an Compile Error: Argument not Optional. So I'm guessing that's not the problem.
Thanks.
R. Hicks
Hmmm ...
Well .. in the code that I posted ...
"PymtDesc" must be "Insurance Payment" before the "ClaimNumber" will be checked and validated ...
Ousually use the Form's On Before Update to validate my entries instead of trying to do it at the control.
This way .. the user will get the message if they attempt to close the form or navigate to another record should the validation fail ...
RDH
R. Hicks
To test for null .. you use the IsNull() function ...
But the Len() expression I used in my example checks for a Null value and an Empty String value ...
Ofeel this is a better way to test ...
DH
dashiellx2000
I took your advice and moved the code to the form's before update event, but it's still not working. I can just tab through the field and tab to the next record or close the form and I don't get the error. I am assuming that means something is wrong with my form's property's or the fact that ClaimNumber is a text field and not a number field. However, I did try put "" after the = and this didn't work either.
Thanks.
R. Hicks
Post the code as you have at the moment ...
DH
dashiellx2000
Here it is:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.PymtDesc = "Insurance Payment" Then
If Len(Me.ClaimNumber & vbNullString) = 0 Then
MsgBox "A Claim Number Must be entered for all Insurance Payments", vbOKOnly
Me.ClaimNumber.SetFocus
End If
Else
Me.RefundType.SetFocus
End If
End Sub
Thanks for all the help. I really appreciate it.
R. Hicks
From what I see ..
If "PymtDesc" contains the value .. "Insurance Payment" ..
And .. "ClaimNumber" does not contain a value ...
Your msgbox should display if you attemp to close the form, navigate to a subfrom, or navigate to another record ...
If you do not get a message for the condition given above .. I really don't know what the problem might be ...
Unless .. this form is not bound ...
RDH
GlenKruger
Try changing this line:
If Len(Me.ClaimNumber & vbNullString) = 0 Then
to:
If IsNull(Me.ClaimNumber) Or Me.ClaimNumber = "" Or Me.ClaimNumber = Empty Then
Hope this helps.
dashiellx2000
I got it working. I was using Insurance Payment--which is what the combo box displays, but it was looking at the key column of the combo box which was 2.
Thanks for you're help.
R. Hicks
That is "great" .. you are very welcome ... wink.gif
DH
R. Hicks
Glen ..
sing: Len(Me.ClaimNumber & vbNullString) = 0
Will cover all of the conditions: IsNull(Me.ClaimNumber) Or Me.ClaimNumber = "" Or Me.ClaimNumber = Empty
RDH
GlenKruger
Hi Ricky,
Just thought another approach might have solved the problem but I see that he was getting the value from a combo box and was referencing the wrong column so it wouldn't matter how he did it until he fixed this.
Have a good one.......
Glen
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.