On Open Event...

[gmlwong]

I have the following on the "On Open" event of a form:

If [Check911] = True And [CustState] = "AZ" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "CA" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "CT" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "FL" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "HI" Then
MsgBox "File Pending... DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "IA" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "KS" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "MN" Then
MsgBox "NO TM ALLOWED. Motorcycle & Watercraft Only. DO NOT proceed with this contract unless it is an AMC or WAW. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "NH" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "NY" Then
MsgBox "NO TM ALLOWED. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "OK" Then
MsgBox "NOT AN APPROVED STATE. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "WI" Then
MsgBox "NOT AN APPROVED STATE. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = True And [CustState] = "WY" Then
MsgBox "File Pending... DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

If [Check911] = False And [Text815] = "HI" Then
MsgBox "File Pending... DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = False And [Text815] = "OK" Then
MsgBox "NOT AN APPROVED STATE. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = False And [Text815] = "MN" Then
MsgBox "NO TM ALLOWED. Motorcycle & Watercraft Only. DO NOT proceed with this contract unless it is an AMC or WAW. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = False And [Text815] = "WI" Then
MsgBox "NOT AN APPROVED STATE. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
If [Check911] = False And [Text815] = "WY" Then
MsgBox "File Pending... DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
Me.CustState.Enabled = False
End If
End If
End If
End If
End If

*** All I'm trying to accomplish with this is for the form, on it's "On Open" event, to recognize if [Check911] check box = True and the [CustState] field has any of these values, the respective message box should appear to alert the user of the issue described. And, if the [Check911] check box = False and the [Text815] field has any of the listed values, the respective message box should appear to alert the user of the issue described...

My refresh statement is placed correctly and does work but none of the above code is being recognized for this event; the [Check911] box does refresh and hits during the "On Open" event of the form as the form blinks to confirm the refresh command is being recognized.

I did try placing this code in the "On Current" event but nothing happens...

*** The [CustState] field is tied to a separate table called[tblStates] and appears as a combo box on this form...can it be that this code cannot be used because the field being called on is a combo box?

Mr. Hartman, Miles, Vass, AutoEng, Idjit...anyone else, if you can help, very appreciated.

 

[ghudson]

My that is some ugly IF ing you have written. I would combine the states that have the same error into the same IF statement like I did below as an example. Using ElseIf will simplify things a bit. I would then put this into a function and call the function in the forms OnOpen and OnCurrent events. Using some type of naming conventions [i.e. txtTextBox, btnCustState] with your objects would also be a good start since you are using VBA.

If [Check911] = False And [Text815] = "HI" Or [Text815] = "WY" Then
    MsgBox "File Pending... DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
    Me.CustState.Enabled = False
ElseIf [Check911] = False And [Text815] = "OK" Or [Text815] = "WI" Then
    MsgBox "NOT AN APPROVED STATE. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
    Me.CustState.Enabled = False
End If

 

[gmlwong]

1,000 thanks!!!

Mr. Hartman: thanks so much for this. I'm still new to all this VBA but I think I'm getting a little better. I enjoy writing routines and getting them to fire and this one is really going to help prevent a legal matter with respect to a database that I manage.

The code belongs in the on Current event and you should use a Select Case. Additionally, you need to enable the state field in the else case:

If Me.Check911 = True Then
    Select Case Me.Text815
        Case "AZ", "CA", "CT", ...., "WY"
            MsgBox "File Pending... DO NOT proceed with this contract. Contact
                        TY ASAP to deactivate this record.", vbExclamation, "This 
                        is an UNAPPROVED STATE for this Direct Marketer Dealer."
            Me.CustState.Enabled = False
        Case Else
            Me.CustState.Enabled = True
     End Select
Else
    Select Case Me.Text815
        Case "HI", "OK", "MN", "WI", "WY"
            MsgBox "File Pending... DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
            Me.CustState.Enabled = False
        Case Else
            Me.CustState.Enabled = True
    End Select
End IF

PS GHudson, you can't combine AND and OR relational operators without parentheses as you have.

 

[gmlwong]

Thank you for your help!

ghudson:

Thank you for your response. I appreciate you taking the time to read through my mess and you're right, what a mess!

My that is some ugly IF ing you have written. I would combine the states that have the same error into the same IF statement like I did below as an example. Using ElseIf will simplify things a bit. I would then put this into a function and call the function in the forms OnOpen and OnCurrent events. Using some type of naming conventions [i.e. txtTextBox, btnCustState] with your objects would also be a good start since you are using VBA.

If [Check911] = False And [Text815] = "HI" Or [Text815] = "WY" Then
    MsgBox "File Pending... DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
    Me.CustState.Enabled = False
ElseIf [Check911] = False And [Text815] = "OK" Or [Text815] = "WI" Then
    MsgBox "NOT AN APPROVED STATE. DO NOT proceed with this contract. Contact TY ASAP to deactivate this record.", vbExclamation, "This is an UNAPPROVED STATE for this Direct Marketer Dealer."
    Me.CustState.Enabled = False
End If