checkbox on continuous form

Question

I've got a continuous form with checkboxes.  I've created a button to turn all the checkboxes on of off when clicked.

below is the code i've got but of cause its not working, can someone point me in the right direction?

Dim strSql1 As String

If IsNull(Me.chkArchive) Then

    strSql1 = "Update tblSchedule Set scheduleArchive = -1"

        Else

    strSql1 = "Update tblSchedule Set scheduleArchive =0"

            DoCmd.SetWarnings False

        DoCmd.RunSQL strSql1

    DoCmd.SetWarnings True

    Me.Refresh

End If

Answer 1

A toggle button's value will be TRUE or FALSE depending on whether it's 'up' or 'down', so you could have code like this in its AfterUpdate event procedure along these lines:

    Dim strSQL As String

    With Me.ActiveControl

        strSQL = "UPDATE tblSchedule SET scheduleArchive = " & .Value

        Me.Dirty = False

        CurrentDb.Execute strSQL, dbFailOnError

        Me.Refresh

        .Caption = IIf(.Value, "Set all NO", "Set all YES")

    End With

The toggle button's Caption property would be Set all YES by default in its properties sheet.  If the caption is currently showing Set all YES when clicked then the scheduleArchive column for all rows in the table would be set to TRUE, and the caption would change to Set all NO.  If then clicked the scheduleArchive column for all rows in the table would be set to FALSE.  You can of course have whatever captions you wish by amending the code and the control's default Caption property accordingly.

Note that the code ensures that the current record is saved by setting the form's Dirty property to False before executing the UPDATE statement to avoid any conflict.

One thing to note when adding a toggle button to a form is that if you switch in and out of design view its last Value property will be retained, so the code might do the opposite of what's expected the first time clicked.  In normal use this won't happen of course, but it's something to be aware of when testing the form at design time.

Answer 2

brilliant Ken thankyou.

I have had to comment out the following as its throwing an error - Run-time error: '438': Object doesn't support this property or method

 '.Caption = IIf(.Value, "Set all NO", "Set all YES")

Answer 3

You are both pointing me in the right direction, thanks.

Ken, I haven't used toggles before, I've searched about them but can't find an example that would update all.  I've got a continuous form with a maximum of 250 yes/no records in that field.

any chance of a heads up please?

Answer 4

A Boolean (yes/no) column can only be TRUE or FALSE, never NULL.  A bound check box control will only be NULL when you navigate to an empty new row in a form.  As soon as any data is entered it will become FALSE (unless the check box is the first control by which a value is entered of course).  So, your code should update the value of the scheduleArchive column to TRUE in each row only if the form is at an empty new row, and to FALSE otherwise.  Is this what's intended?

If you simply want to update the value of the scheduleArchive  to TRUE or FALSE in all rows why not use a toggle button?  You then just need to update the column's values to the value of the button.

Answer 5

You did not say what is not working.

Why are you checking for IsNull, while you set the checkbox to True or False (and thus not null)?