Testing for 'un-clicked' checkbox

[knarfreppep]

Hello ... Any help would be appreciated ...

I have a table with a Yes/No field ysnPack with 'Default Value'=Null ... on a form I have a CheckBox chkPack with 'Control Source'=ysnPack, 'Triple Sate'=Yes, and 'Default Value'=Null ... When I validate that data has been entered in all required fields in the Form_BeforeUpdate event having not clicked ysnPack, I find chkPack.Value=0 and chkPack.OldValue=Null ... there having been no click, why is chkPack.Value not also Null please? ... Anyone know how I can test whether chkPack has been clicked ... If I don't test, chkPack may be = 0 only because the operator forgot about it.

Thanks in advance ...

[CJ_London]

ysnPack with 'Default Value'=Null

to be clear, you mean you have left the default value blank? or you have entered Null?

[knarfreppep]

to be clear, you mean you have left the default value blank? or you have entered Null?

No, I have set the 'Default Value' property (field and CheckBox) to 'Null'.

[knarfreppep]

Here's my code ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo Error_Form_BeforeUpdate

Dim ctlControl As Control

For Each ctlControl In Controls
Select Case ctlControl.ControlType
Case acTextBox, acComboBox
If Len(Nz(ctlControl.Value, "")) = 0 And Len(ctlControl.Tag) > 1 Then
Cancel = True
gsubMessage_MissingData ctlControl.Tag
ctlControl.SetFocus
GoTo Exit_Form_BeforeUpdate
End If
Case acCheckBox
If IsNull(ctlControl.Value) And Len(ctlControl.Tag) > 1 Then
'>>>>>>>>>>>>>>>>>>
' a control with Tag length > 1 (e.g. Tag=Pack for mandatory CheckBox chkPack
'>>>>>>>>>>>>>>>>>>
' this code is not being execued because the CheckBox Value is 0 not Null even though the CheckBox has not been clicked
Cancel = True
gsubMessage_MissingData ctlControl.Tag
ctlControl.SetFocus
GoTo Exit_Form_BeforeUpdate
End If
End Select
Next

If NewRecord Then
txtID = Nz(DMax("lngID", "tblOvernights"), 0) + 1
txtCreatedBy = gintCurrentOperatorID
txtCreated = Now()
End If
txtLastEditedBy = gintCurrentOperatorID
txtLastEdited = Now()

Exit_Form_BeforeUpdate:
Set ctlControl = Nothing
Exit Sub

Error_Form_BeforeUpdate:
gsubMessage_Error Err.Number, Err.Description, Name, "Form_BeforeUpdate"
Resume Exit_Form_BeforeUpdate

End Sub

[CJ_London]

for the future please use the code tags (the # button) to surround your code to preserve formatting/indentation

Private Sub Form_BeforeUpdate(Cancel As Integer)

    On Error GoTo Error_Form_BeforeUpdate

    Dim ctlControl As Control

    For Each ctlControl In Controls
        Select Case ctlControl.ControlType
            Case acTextBox, acComboBox
                If Len(Nz(ctlControl.Value, "")) = 0 And Len(ctlControl.Tag) > 1 Then
                    Cancel = True
                    gsubMessage_MissingData ctlControl.Tag
                    ctlControl.SetFocus
                    GoTo Exit_Form_BeforeUpdate
                End If
            Case acCheckBox
                If IsNull(ctlControl.Value) And Len(ctlControl.Tag) > 1 Then
 '>>>>>>>>>>>>>>>>>>
 '   a control with Tag length > 1 (e.g. Tag=Pack for mandatory CheckBox chkPack
 '>>>>>>>>>>>>>>>>>>
 '   this code is not being execued because the CheckBox Value is 0 not Null even though the CheckBox has not been clicked
                    Cancel = True
                    gsubMessage_MissingData ctlControl.Tag
                    ctlControl.SetFocus
                    GoTo Exit_Form_BeforeUpdate
                End If
        End Select
    Next

    If NewRecord Then
        txtID = Nz(DMax("lngID", "tblOvernights"), 0) + 1
        txtCreatedBy = gintCurrentOperatorID
        txtCreated = Now()
    End If
    txtLastEditedBy = gintCurrentOperatorID
    txtLastEdited = Now()

 Exit_Form_BeforeUpdate:
    Set ctlControl = Nothing
    Exit Sub

 Error_Form_BeforeUpdate:
    gsubMessage_Error Err.Number, Err.Description, Name, "Form_BeforeUpdate"
    Resume Exit_Form_BeforeUpdate

 End Sub

I am able to replicate the issue, not sure what is causing it, but suggest perhaps a different way

have a public Boolean variable for the form (or perhaps an array if you have many checkboxes
set to false in the form current event
set to true in the control after update event

then use that to determine if a control has been updated or not

[knarfreppep]

I've found a workaround ...

Private Sub chkPack_Click()
chkPack.ValidationText = 1
End Sub

then change Form_BeforeUpdate -

Case acCheckBox
If Len(ctlControl.ValidationText) = 0 And Len(ctlControl.Tag) > 1 Then
Cancel = True
gsubMessage_MissingData ctlControl.Tag
ctlControl.SetFocus
GoTo Exit_Form_BeforeUpdate
End If

Any better ideas please?

[knarfreppep]

I like your thinking ... one problem though ... my code depends only on the control itself ... yours would require multiple variables on forms with multiple CheckBoxes (or an array which also held the control names) and more complex code to do the check ... my solution of using an otherwise unused property of the very control as my 'variable' seems less complex.

Thanks for your thoughts though ... much appreciated.

[CJ_London]

think your solution meets your needs better than mine, not thought of using validation text as a 'tag'. Be sure to set it back to null or zls on the form current event otherwise your next record may be flagged as entered, even if it isn't

repeat, please use the code tags for your code, if you can't be bothered to present your issue in a readable format, I can't be bothered to try and read it

[knarfreppep]

PS Thanks for the hint about #

#
For Each ctlControl In Controls
Select Case ctlControl.ControlType
Case acTextBox, acComboBox
If Len(Nz(ctlControl.Value, "")) = 0 And Len(ctlControl.Tag) > 1 Then
Cancel = True
gsubMessage_MissingData ctlControl.Tag
ctlControl.SetFocus
GoTo Exit_Form_BeforeUpdate
End If
Case acCheckBox
If Len(ctlControl.ValidationText) = 0 And Len(ctlControl.Tag) > 1 Then
Cancel = True
gsubMessage_MissingData ctlControl.Tag
ctlControl.SetFocus
GoTo Exit_Form_BeforeUpdate
End If
End Select
Next
#


Clearly I don;t know how to tag code?

[CJ_London]

in the buttons above where you enter your post, right at the end is the # button. Highlight your code and click the button

[knarfreppep]

Yes to resetting in OnCurrent ... thanks for the reminder.

Also, only need to run check if NewRecord.

Thanks again.

[knarfreppep]

Got it now ... one last thanks.

            Case acCheckBox
                If NewRecord Then
                    If Len(ctlControl.ValidationText) = 0 And Len(ctlControl.Tag) > 1 Then
                        Cancel = True
                        gsubMessage_MissingData ctlControl.Tag
                        ctlControl.SetFocus
                        GoTo Exit_Form_BeforeUpdate
                    End If
                End If