Data Validation with Comboboxes

[bobbye_69]

Hello. I am hoping someone can help me with this.
My Situation: I have 2 combo boxes on a form. The 2nd box is not visible unless the 1st box is set to a specific value (Illness). Once the specific value is set, the 2nd box appears with appropriate data for selection. :banghead:

Goal #1:
I would like to have the form checked before it is closed to ensure that if the condition in box 1 is "Illness" then the 2nd box must have a value and can not be null.

Goal #2:
I am also having problems with the 2nd combo being visible when form opens or becomes current. The field is set to visible=false normally but needs to be visible when form opens if the conditions mentioned above are meet (true).

Any help would be appreciated and I have some code below. Thanks

Private Sub Form_Unload()
If IsNull(Me.Incident_Classification) Then
MsgBox "Please select a type of Illness"
Me.Incident_Classification.SetFocus
End
Else
If (Me.Incident_Classification) = "Illness" Then
ElseIf IsNull(Me.Incident_Class_Type) Then
Me.Incident_Class_Type.SetFocus
MsgBox "Please select appropriate Incident Class Type"
Else
If (Me.Incident_Classification) = "Illness" Then
ElseIf (Me.Incident_Class_Type) <> "" Then
End If
End If
End If
End Sub

Private Sub Form_Current()
If Me.Incident_Classification = "Illness" Then
Me.Incident_Class_Type.Visible = True
Me.Incident_Class_Type_Label.Visible = True
Me.Incident_Classification.SetFocus
Else
Me.Incident_Class_Type.Visible = False
Me.Incident_Class_Type_Label.Visible = False
End If
End Sub

 

[gblack]

1st.
Why are you doing this in the form unload event? Shouldn't you do the first goal in the onchange event of the first combo box?

2nd... I'd probably make a close button on the form and make that stuff happen on the OnClick event of the form-close button... and put your validation checking code just prior to closing the form. Then you're not dealing with the different form events.

 

[bobbye_69]

gblack: Yes, thanks. I realized this not long after I sent the post. I have placed it in the "OnChange" event and I added a close button as suggested. Thanks

 

[jdraw]

Bob,

When you post vba, you should use code tags.

Private Sub Form_Unload()
If IsNull(Me.Incident_Classification) Then
MsgBox "Please select a type of Illness"
Me.Incident_Classification.SetFocus
End
Else
If (Me.Incident_Classification) = "Illness" Then
ElseIf IsNull(Me.Incident_Class_Type) Then
Me.Incident_Class_Type.SetFocus
MsgBox "Please select appropriate Incident Class Type"
Else
If (Me.Incident_Classification) = "Illness" Then
ElseIf (Me.Incident_Class_Type) <> "" Then
End If
End If
End If
End Sub

and better still is to get the free Smart Indenter utility -- reformat the code and use code tags

Private Sub Form_Unload()
    If IsNull(Me.Incident_Classification) Then
        MsgBox "Please select a type of Illness"
        Me.Incident_Classification.SetFocus
        End
    Else
        If (Me.Incident_Classification) = "Illness" Then
        ElseIf IsNull(Me.Incident_Class_Type) Then
            Me.Incident_Class_Type.SetFocus
            MsgBox "Please select appropriate Incident Class Type"
        Else
            If (Me.Incident_Classification) = "Illness" Then
            ElseIf (Me.Incident_Class_Type) <> "" Then
            End If
        End If
    End If
End Sub