Access Form with check boxes

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I want to create a form from a query that has check boxes so that the user can select any combination
of rows. What is the best way to do this and what Access VBA code would I use to gather the key values from the ID column?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
To my knowledge there are 2 ways
1. The simple one..just pull the boolean fields on the form creating equal number of checkboxes
2. The binary way....you have a single field  e.g MultiBlnVal...that holds the representation of the state of checkboxes...e.g 0 --> False, 1 -->True
001101 means ch1,ch2 -->False,ch3,ch4-->True ch5-->False,ch6-->True
so you will create 6 unbound checkboxes that you will have events to modify the value of this fields according to their name/tag
e.g unboundchk1 -unboundchk6 ...to the click event the MultiBlnVal is changed accordingly
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
If you have a continuous or datasheet form, the Select field will have to be bound, otherwise checking one row would check all the rows.

Once you do that, you could simply iterate through the form's recordset and check for that column being checked:

Dim rst As DAO.Recordset
rst = Me.RecordsetClone
Do Until rst.EOF
  If rst("Select") = True Then
    '/ the user selected this record
  End If
Loop
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Way this is typically done is to have a temp table with the PK field and the yes/no "selected" field.

You fill the temp table based on your main table records, copying in the PK and setting the default value for the yes/no field. You then base the form on a join between your main table and the temp table.   At that point, your form has a yes/no check box available for selecting each row.

Jim.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I do it the way Jim described above.
John TsioumprisSoftware & Systems Engineer

Commented:
For the 2nd way of implementation a neat trick is to use Mid for value Replacement
Dim multBlnVal As String
multBlnVal = "0011101"
Mid(multBlnVal , 2, 1) = 0
debug.Print multBlnVal

Open in new window

It will return
0111101

Open in new window

For the position of insertion/replacement i use the Tag property for the control
e.g
unboundchk1 -->Tag =1
unboundchk2 -->Tag =2
................
Chief Technology Officer
Commented:
Before rushing off to build something, let's take a moment to understand why we are doing this.  There are numerous ways to select multiple records.  As you can see from the suggestions above, the solution depends on what you really want to do in the wider scope of the process.

The trick is understanding what we want to do with this information:  
Do we simply want to give the user a means of selecting several records, then clicking a button and processing the selections?
Do we want to keep the selection as part of a permanent record, or, once processed, blow the selections away?
How do we want/need to store the selections?  Do we need to store the selections at all for later use?
What kind of form?  A continuous form, or a datasheet?
Do we really want/need a form, or can a multi-selection listbox to the trick?

If you just want to select a few displayed records to be processed, you can use a muti-select listbox with the Key column as the bound column.  When the user finishes making their selection(s), they click the button and the code processes the selected records.

If you want to use a continuous form or datasheet, you can keep the selection in a Yes/No field on an underlying table, which would allow you to keep it permanently as part of the record.  The question would be, should the field be on the record-being-selected table, on a temp table, or on a separate table linked to the record-being-selected table?

Creating a query based on a table that has a Yes/No field on it and connecting it to a form with a checkbox on it seems so simple and basic that I'm wondering why you would ask this in the first place.  Is there anything "special" about what you want to do that you need additional help, or are you just trying to get a consensus from other developers?  I ask this because I've got a sneaky suspicion that there is more to the problem than what you have described.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
"Creating a query based on a table that has a Yes/No field on it and connecting it to a form with a checkbox on it seems so simple and basic that I'm wondering why you would ask this in the first place.  "

This method has a major problem in a multi-user environment, which is why I don't use this technique.  In a multi-user environment, this technique would allow multiple user to simultaneously check or uncheck these values.

I have not used multi-select listboxes for many years because you generally have to loop through the selected items, resulting in a where clause with something like:

WHERE [ID] IN (3, 5,7 , 9, ...)

which is generally much slower to evaluate than an inner join on a temp table with a WHERE clause that looks something like:

SELECT A.*
FROM TableA as A INNER JOIN temp_Table as B on A.ID = B.ID
WHERE B.IsSelected <> 0
Mark EdwardsChief Technology Officer

Commented:
Thanks Dale.  If it's a multi-user situation, then using a selection method that is independent between users (keeps them from stepping on one-another's work) is a must.  Let's assume that the author is seeking a solution for a multi-user environment.  If so, does one user's selections have to be kept separate from another user's selections?  Can one record be selected by more than one user and does that selection need to be stored?  Is the temp table used for selecting going to be in the front-end or in the database where everyone uses it? (It needs to be in the front-end.)  Do the users have their own copy of the front-end (which would greatly simplify things)?  If they don't, that brings up a whole slew of other problems.

The next question is what is to be done with the "selections".  Are they to be kept, or blown away after a process?

The user has asked for a "best way" to do this.  A "best way" is going to depend on what he is trying to achieve with the overall process - not just selecting a set of records.  I think he's expecting us to assume too much here.

By the way, I find processing a multi-select listbox a lot simpler and easier (not to mention very useful in a multi-user app) than dealing with a bunch of forms and tables, unless the process requires storing the selections for use later.  Maybe it's just me, but when it comes to just a few items that need to be selected, the difference in speed between methods is negligible.  I don't see any reason to fuss over a few microseconds.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Don't disagree, Mark, that we really need a better explanation from the OP.  

Murray, ball is in your court.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks everyone. I am going with the multiselect listbox

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial