Check box to SELECT ALL?

[EPD Hater]

I would like to employ an unbound Check Box so that when it is checked, all the items in my form (their respective check boxes) are selected (updated to value -1). I would like to do this without using a RunSQL/UPDATE query in fear of getting "Write Conflict" messages left and right. Is this possible? I have been thinking on using a loop to go through each record and change the value of its check box, but I'm not sure if each record has an "observation" or "record" number.

Thanks...

 

[dcx693]

Here's some sample code to flip the value of all the checkboxes on a form. Just attach this code to a command button called cmdSetCheckBoxes:

Private Sub cmdSetCheckBoxes_Click()
Dim ctl As Control

    For Each ctl In Me
        If ctl.ControlType = acCheckBox Then
            ctl = Me.cmdSetCheckBoxes
        End If
    Next ctl

End Sub

 

[EPD Hater]

dcx693:

That works. I just attached the code to a checkbox instead of a command button...HOWEVER...This only works for one record. I need to be able to select checkboxes in ALL records (my form is displayed as "Continuous Forms").

 

[dcx693]

Actually, I made a mistake in my last post. I actually attached the code to a checkbox myself also because the code needs to flip between setting the values to true and false.

Anyway...why are you getting write conflict messages? Yes, you could write code to go to the first record in the form, flip all the checkboxes, then proceed onto the next record until you are at the last record, but it would be much faster to use DAO/ADO. Of course then, it would be easiest if all your true/false fields were checkbox fields so we didn't flip something not meant to be flipped.

 

[EPD Hater]

I think i has the solution!

This is on my SELECT ALL checkbox.

    DoCmd.SetWarnings False
    If Me.Check46 = -1 Then
        DoCmd.RunSQL "UPDATE tbl_Master_Compass ..."
    ElseIf Me.Check46 = 0 Then
        DoCmd.RunSQL "UPDATE tbl_Master_Compass ..."
    End If
    DoCmd.SetWarnings True
    Me.Refresh

Thanks for your suggestions.

 

[dcx693]

You can shorten your code to:

If Me.Check46 Then
     Currentdb.Execute "UPDATE this ..."
Else
     Currentdb.Execute "UPDATE that ..."
End If

I don't know if you need that refresh in there, but whatever works!

 

[EPD Hater]

Thanks. I put the REFRESH in there to refresh the form display so that it'll display the records with their new current values.