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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dmitryz6Commented:
On main form put checkbox ,name chkAll

On checkboks after Update

db.Execute "UPDATE YourTableName SET FieldName= " & me!chkAll
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
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
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

dmitryz6Commented:
in my

currentdb.Execute "UPDATE YourTableName SET FieldName= " & me!chkAll
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°)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

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°)
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.
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°)
dmitryz6Commented:
Markus

I am completly agree with you.

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

Mike
harfangCommented:
Glad to help, good luck with you project!
(°v°)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.