Return to Snippet

Revision: 13314
at August 2, 2011 17:46 by heislekw


Updated Code
Private Sub cboColor_BeforeUpdate(Cancel As Integer)
    
    ' Validation for combo box with a corresponding text field. 
    ' When users select "Other" in cboColor, they are encouraged to specify in txtOther what they mean. 
    ' If they enter something in txtOther and then decide to change or delete their answer in cboColor, this routine
    ' tells them the data in txtOther will be deleted. This prevents orphaned data being stored in txtOther 
    ' when cboColor is blank or <> "Other".
    
    If Not IsNull(Me.txtOther) Then     				' If txtOther has data
        If Not Me.cboColor.Value = "Other" Or IsNull(Me.cboColor) Then     ' and the user selects something different than "Other", ask the user to confirm the change
            If MsgBox("Changing this answer from 'Other' will delete the information in the adjacent text field. " & _
            Chr(13) & Chr(13) & "Continue?", vbQuestion + vbYesNo + vbDefaultButton2, "Change Answer?") = vbYes Then     
							' If the user clicks 'Yes'
                Me.txtOther = Null     			' delete the data in txtOther
                 Else                                	' Otherwise, if the user doesn't click 'Yes'
                Cancel = True                   	' don't go through with the change (i.e., cancel the update),
                Me.cboColor.Undo                      	' restore the value of cboOther to the original answer (i.e., 'Other')
                Exit Sub                        	' and leave this routine
            End If
        End If
    End If
    
    'Check the final value in cboColor to determine whether to enable or disable txtOther.
    'If cboColor = 'Other', set txtOther's enabled property to True (i.e., -1).
    'If cboColor <> 'Other', set txtOther's enabled property to False (i.e., 0).

    Me.txtOther.Enabled = IIf(Me.cboColor = "Other", -1, 0)
     
End Sub

Revision: 13313
at April 19, 2009 12:32 by heislekw


Updated Code
Private Sub cboColor_BeforeUpdate(Cancel As Integer)
    
    ' Validation for combo box with a corresponding text field. 
    ' When users select "Other" in cboColor, they are encouraged to specify in txtOther what they mean. 
    ' If they enter something in txtOther and then decide to change or delete their answer in cboColor, this routine
    ' tells them the data in txtOther will be deleted. This prevents orphaned data being stored in txtOther 
    ' when cboColor is blank or <> "Other".
    
    If Not IsNull(Me.txtOther) Then     				' If txtOther has data
        If Not Me.cboColor.Value = "Other" Or IsNull(Me.cboColor) Then     ' and the user selects something different than "Other", ask the user to confirm the change
            If MsgBox("Changing this answer from 'Other' will delete the information in the adjacent text field. " & _
            Chr(13) & Chr(13) & "Continue?", vbYesNo + vbDefaultButton2) = vbYes Then     
							' If the user clicks 'Yes'
                Me.txtOther = Null     			' delete the data in txtOther
                 Else                                	' Otherwise, if the user doesn't click 'Yes'
                Cancel = True                   	' don't go through with the change (i.e., cancel the update),
                Me.cboColor.Undo                      	' restore the value of cboOther to the original answer (i.e., 'Other')
                Exit Sub                        	' and leave this routine
            End If
        End If
    End If
    
    'Check the final value in cboColor to determine whether to enable or disable txtOther.
    'If cboColor = 'Other', set txtOther's enabled property to True (i.e., -1).
    'If cboColor <> 'Other', set txtOther's enabled property to False (i.e., 0).

    Me.txtOther.Enabled = IIf(Me.cboColor = "Other", -1, 0)
     
End Sub

Revision: 13312
at April 19, 2009 12:07 by heislekw


Updated Code
Private Sub q3_BeforeUpdate(Cancel As Integer)

    ' Validation for a combo box with specify field
    
    If Not IsNull(Me.q3_specify) Then           
        If Not Me.q3.Value = 7 Or IsNull(Me.q3) Then
             If MsgBox("Changing this answer from 'Other' will delete the information in the adjacent text field. " & _
            Chr(13) & Chr(13) & "Continue?", vbYesNo + vbDefaultButton2) = vbYes Then
                                                
                Me.q3_specify = Null
            Else                    
                Cancel = True
                Me.q3.Undo                                      
                Exit Sub                                    
            End If
        End If
    End If
    
    Me.q3_specify.Enabled = IIf(Me.q3 = 7, -1, 0)

End Sub

Revision: 13311
at April 19, 2009 11:57 by heislekw


Initial Code
Private Sub q3_BeforeUpdate(Cancel As Integer)

    ' Validation for a combo box with specify field
    
    If Not IsNull(Me.q3_specify) Then           ' If q3_specify has data (i.e., is not blank)
        If Not Me.q3.Value = 7 Or IsNull(Me.q3) Then  ' and the user changes q3's answer to something other than 7 (i.e., Other),
                                                ' or deletes the answer (i.e., Null),
                                                ' ask the user to confirm the change.
            If MsgBox("Changing this answer from 'Other' will delete the information in the adjacent text field. " & _
            Chr(13) & Chr(13) & "Continue?", vbYesNo + vbDefaultButton2) = vbYes Then
                                                ' If the user clicks 'Yes'
                Me.q3_specify = Null            ' delete the data in q3_specify (i.e., make it blank).
            Else                                ' Otherwise, if the user doesn't click 'Yes'
                Cancel = True                   ' don't go through with the change (i.e., cancel the update),
                Me.q3.Undo                      ' restore the value of q3 to the original answer (i.e., 7)
                Exit Sub                        ' and leave this routine
            End If
        End If
    End If
    
    'Check the final value in q7 to determine whether to enable or disable q7_specify.
    'If q7 = 2 (i.e., Yes), set q7_specify's enabled property to True (i.e., -1).
    'If q7 <> 2 (i.e., it's No, Missing, or blank), set q7_specify's enabled property to False (i.e., 0).
    Me.q3_specify.Enabled = IIf(Me.q3 = 7, -1, 0)

    ' This would also work:
    'Me.q3_specify.Enabled = Nz((Me!q3 = 7) - 1, 0)
    
    ' As would this:
    'If Me.q3.Value = 7 Then
    '    Me.q3_specify.Enabled = True
    'Else
    '    Me.q3_specify.Enabled = False
    'End If

End Sub

Initial URL


Initial Description


Initial Title
Validation for a combo box with a corresponding text field

Initial Tags
validation

Initial Language
Visual Basic