boolean yes/no field: select all? unselect all?

Any suggestions on how to make a link or button to "check all" or "uncheck all" for yes/no column?  I am displaying (could be a query or a form) about 30 records at a time, with 10 columns.  4 of the columns are yes/no fields and I need an easy way for the user to check and uncheck all the boxes in that column (in addition to the default behavior of clicking on them one at a time).

Any help appreciated.  Working example *tremendously* appreciated (will up the points).

I've done a lot of programming, but very new to Access.

Thanks-
ottenmAsked:
Who is Participating?
 
harfangCommented:
Hi,

Let me add to mbizup's suggestion. You will display a datasheet view, either a query or a subform, on a main form. The button [Check All] will be on the main form, naturally. The code to check a box in all rows should then use a clone of the actual recordset of the subform/subtable. For example:

Private Sub cmdCheckAll_Click()

    Me.subYourSubform.SetFocus
    With Me.subYourSubform.Form.RecordsetClone
        If .RecordCount Then .MoveFirst
        Do Until .EOF
            .Edit
            !ysnYourField = True
            .Update
            .MoveNext
        Loop
    End With

End Sub

As you will need eight of these (four columns, each with check and uncheck all), you should create a common function:

Public Function CheckAll( pstrField As String, pysnValue As Boolean )

    Me.subYourSubform.SetFocus
    With Me.subYourSubform.Form.RecordsetClone
        If .RecordCount Then .MoveFirst
        Do Until .EOF
            .Edit
            .Fields(pstrField) = pysnValue   ' --- note this line
            .Update
            .MoveNext
        Loop
    End With

End Function

The buttons event then becomes just this:

Private Sub cmdCheckAll_Click()
    CheckAll "ysnYourField", True
End Sub

And in fact, you can even just write this in the button's property:

    On Click: =CheckAll("ysnYourField", True)

Cheers!
(°v°)
0
 
dmitryz6Commented:
On main form put checkbox ,name chkAll

On checkboks after Update

db.Execute "UPDATE YourTableName SET FieldName= " & me!chkAll
0
 
mbizupCommented:
Give this a try.  Put the following code in your command button click events and make the name substitutions necessary for you application.  Make a backup before running this.

private sub cmdUncheckAll_Click()
    dim rs as dao.recordset
    set rs = currentdb.Openrecordset(me.recordset)
    with rs
    do until.eof
         rs!YourYNfieldName = False
    loop
    end with
end sub

private sub cmdCheckAll_Click()
    dim rs as dao.recordset
    set rs = currentdb.Openrecordset(me.recordset)
    with rs
    do until.eof
         rs!YourYNfieldName = True
    loop
    end with
end sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
I forgot to actually make use of the With blocks, and close the recordset:

private sub cmdUncheckAll_Click()
    dim rs as dao.recordset
    set rs = currentdb.Openrecordset(me.recordset)
    with rs
    do until.eof
          !YourYNfieldName = False
    loop
    end with
    rs.close
end sub

private sub cmdCheckAll_Click()
    dim rs as dao.recordset
    set rs = currentdb.Openrecordset(me.recordset)
    with rs
    do until.eof
          !YourYNfieldName = True
    loop
    end with
    rs.close
end sub
0
 
dmitryz6Commented:
in my

currentdb.Execute "UPDATE YourTableName SET FieldName= " & me!chkAll
0
 
dmitryz6Commented:
for checkbox
one more may be specify

currentdb.Execute "UPDATE YourTableName SET FieldName= " & me!chkAll  & " where Your criteria"
Me!YourSubformobjectName.Form.requery

or if you like it as button


currentdb.Execute "UPDATE YourTableName SET FieldName= -1 where Your criteria"
Me!YourSubformobjectName.Form.requery

or

currentdb.Execute "UPDATE YourTableName SET FieldName= -1 where Your criteria"
Me!YourSubformobjectName.Form.requery

Execute statement faster to run then recordset

0
 
harfangCommented:
Dmitry,

I have to differ on this one. Looping through 30 records in an already open recordset, even while editing each one, is faster than: calculating a criteria, getting a database clone, running an update query (including opening a recordset and managing locks because the same table is already open), and requerying the subform.
VB may be slow, but not *that* slow...

Cheers!
(°v°)
0
 
dmitryz6Commented:
Markus.

I was expecting this posting from you(actualy my prevouse posting was specificaly for it).You know, how it in real life.Today 30,tommorow ...
But tell you true It depend of situation you can use different methodes.you will not have record locking there with execute statement.I am agree with your changes about recordset clone for VB Example,but I do beleive programer should be Lazy and write less code as posible to reach goal,but I still remember your answer to my Excel question.

Regards
  Dmitry.
0
 
harfangCommented:
Dmitry,

OK, you caught me ;)

And of course I must admit that there are cases where an update query is much faster, and also that in this case the time difference will not be noticeable at all! One could then ask which method is easier to develop and maintain? Again, there isn't much differenct in the end.

So it is good to have both possibilities exposed here!

Cheers!
(°v°)
0
 
dmitryz6Commented:
Markus

I am completly agree with you.

Regards
  Dmitry
0
 
ottenmAuthor Commented:
Gratitude all around.  Thanks for the solutions and the insightful comparisons.

Mike
0
 
harfangCommented:
Glad to help, good luck with you project!
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.