?
Solved

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

Posted on 2006-04-26
12
Medium Priority
?
642 Views
Last Modified: 2012-06-27
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-
0
Comment
Question by:ottenm
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16547774
On main form put checkbox ,name chkAll

On checkboks after Update

db.Execute "UPDATE YourTableName SET FieldName= " & me!chkAll
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16547780
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 16547817
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 19

Expert Comment

by:dmitryz6
ID: 16547892
in my

currentdb.Execute "UPDATE YourTableName SET FieldName= " & me!chkAll
0
 
LVL 58

Accepted Solution

by:
harfang earned 400 total points
ID: 16549109
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
 
LVL 19

Assisted Solution

by:dmitryz6
dmitryz6 earned 400 total points
ID: 16549349
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
 
LVL 58

Expert Comment

by:harfang
ID: 16549530
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
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16549693
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
 
LVL 58

Expert Comment

by:harfang
ID: 16549834
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
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16549901
Markus

I am completly agree with you.

Regards
  Dmitry
0
 

Author Comment

by:ottenm
ID: 16553495
Gratitude all around.  Thanks for the solutions and the insightful comparisons.

Mike
0
 
LVL 58

Expert Comment

by:harfang
ID: 16554433
Glad to help, good luck with you project!
(°v°)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question