Multi-select checkbox for form

The best way to do this would be with a multi-select list box.

However, the multi-select list box would not allow the user to check or uncheck a check box.

What you would have to do is show the check box with its current condition and when the user has selected a record or several record, go through the select list and flip the condition of the check box.

Then you would have to refresh the list box.  This would seem to do what you want and make the user think that he was actually checking the box by selecting a record.

The only problem is when would you know that the user was finished selecting records?  There would have to be a process button or you would have to write a routine which watched mouse clicks (not very easy).

I've been trying to think of a way to do this with a datasheet view.  I know that you can select a record or group of records but you would have to do some DoCmd.RunCommand coding which would take the selected records, add them to a temporary table, change the check box on a record by record basis in the table and then update the datasheet records with the new settings.

All you can do with selected records in either datasheet or form mode is Copy, Cut, Delete, Paste, PasteAppend, Replace (which doesn't really work for you), etc.

Not a lot of help is it?  In any case, you will have to do some code work.

It might just be easier to let the user click the check boxes and not offer any multi-record selection way to do check off any number of sequential boxes.

Jim

Edited text of question.

I guess in that situation you could work with the recordsetclone and starting with the current record, if it is checked, work backwards checking the boxes until you reach a box that is checked.

    Dim rstThis as Recordset
    If !Chkbox = True Then
       Set rstThis = Me.RecordsetClone
       With rstThis
            .Bookmark = Me.Bookmark
            .MovePrevious
            Do While Not .BOF
               If !ChkBox Then
                  Exit Do
               Else
                  .Edit
                  !Chkbox = True
                  .Update
                  .MovePrevious
               End if
            Loop
            .Close
       End With
       Set rstThis = Nothing
       Me.Requery
    End if

I didn't test this code but you should get the idea.

Jim

I've done this before, but it's been a while, so this will be general, but if need be, we can develop the exact code without too much broughhaha.  Here's the concept: use a dynaset type recordset as the record source for a continuous form with headings in the form header (looks datasheet-ish).  To the SQL for the form's record source add a literal boolean field (,false as checkbox,) which the user can toggle.  Put checkbox into a bound control on the form.  When the user is ready (made his selections), your code can run an SQL (or filter) against the dynaset looking for "checkbox = True" into a temp table, then use the temp table for your report.  The code then does the appropriate housekeeping to remove all unwanted "stuff".  Somewhat clunky, but it works.  There are lots of variations on this, filters before or after "checking", etc.  It all depends on just what you want the user ro "see".

Hope this helps.  If this is what you want and need some additional, e-mail me and I'll try to resurrect my code.

good luck as usual...

tomk
That's an interesting solution but does it require adding a field to the actual table. If it does then if two users were accessing the record at the same time one may want it unchecked and one checked which would be bad. Also if someone checked a few then quit the actual table would have prechecked records. You did say bound control right. I tried a non bound controll and it did not not stay unique for each row.

So where are you now with this problem?

Jim

So far it seems as though it can not be done as I visualized it. TimeMatters (a commercial program) does it but they use dataflex.
I am now investigating a different solution entirely. Instead of checking off lines perhaps I could doubleclick lines and have them go to another list box. Similier to the NT permission granting screen. I still would have no easy way to manipulate the resulting list with sql. So I would have to let the selected keys go to a table. But that would mean that I would have to keep track of the user so multiple users would not stomp each other. I need in-memory temporary table like MySql. Am I missing any A2000 tricks that would make this easier?

davidpm, no, the method I described doesn't add a field to the table, only to the record source dynaset, which is generated by the SQL statement in the forms's record source property.  It is a "literal" field.  The "field" is unique to each instance of the form (independent of other users' forms), and initialized to False. It is discarded as soon as the form is closed and reset to False in the form on any .Requery.  Hope that clarifies.

The recordset can be filtered for a True setting of checkbox to display only the selected records (IMHO, easiest way).

good luck as usual...

tomk
Your method looks promising. How do I create a query with literal checkbox in it. The simplest example would be fine. I can type "false" into the query grid and it displays 0 but how to make it a checkbox that the user can change?

You've lost me now.  What the heck are you guys talking about?

tomK is talking about something that is new to me too. If I understand him correctly, tom correct me where I'm wrong, I can make a query simier to this as the record source of a continous form. "SELECT False AS CheckBox, invdet.cust, invdet.desc
FROM invdet;"
Now in the form I can make sure the control for the checkbox field is a checkbox control.
Tom says he then can make it so his users can click in the check box and change it to true. After selecting several rows/forms the user can activate a filter on the form to select for true on the checkbox field.
Everything works for me as described above except that it beeps and will not let the checkbox field be toggled. I am waiting on Tom for the final trick.

I not sure what Tom's trick is either.  I thought that I knew ever query trick in the book but when I tried to do that, I was not successful either.  There simply is no way to change an expression (literal).

The only way that you can do it the way that Tom is talking about without adding a field to the table is to create a new table.  However, you must have a primary key field in the table that you want to add a checkbox in a continuous form.  Create a new table with two fields:  a primary key field which has the same data type as the primary field in your table and a Yes/No field.  Go into the relationship wizard and join these two tables by the primary keys.  Make the join such that you show all records in the table you want to show.

Before you add the check table each time you should empty it.  This will allow you to create new records in it to track the checked values.

Now in your query select your table and the check table.  They should have a relationship between the primary keys of both fields.  The check boxes will show up initially greyed out (null).  When the user checks the box, the check table will create an entry with the primary key value of that record marked true.  If the user unchecks the value, then it will be white (false).

To find True, change the criteria to True.  To find False, change the criteria to Null or False.

I've tested this and it works.  I did the testing in 97 but thinking that there might be some new feature in 2000, I tried both ways there as well.  Only the trick table worked in both.

As an aside, you can also use a trick table or subquery which has only one record, to add that value to every record in a form's recordset.  Put the trick table or subquery in the query but do not relate it to any field.  Pull the field(s) you want from the trick table/query to the columns and the values will show up in the form.  Don't let the user update these values in the form as you will get weird results.

Jim

OK, guys (blush), I brought out the old code from archives and found that I, too, created a temp table.  It is created when a form is opened and deleted when the form is closed, so you never see the table, and I didn't remember doing it.  Been a few years, so I guess Altzheimers has finally gotten to me.  Mea culpa, mea culpa!

OK, guys (blush), I brought out the old code from archives and found that I, too, created a temp table.  It is created when a form is opened and deleted when the form is closed, so you never see the table, and I didn't remember doing it.  Been a few years, so I guess Altzheimers has finally gotten to me.  Mea culpa, mea culpa!

Sorry about the double post, ISP problems.  Jim, I used the Select False as Checkbox... SQL in the query that made the temp table to add the Checkbox field.  You are right about not being able to change the value of the field in the *query* results, but, of course, once the table is made you can change its value in the temp table.  And the temp table is the record source for the record selection form.

Tomk
So we are making progress. What happens if two users try this process at the same time. What do you do to make sure the temp tables do not trash each other?

Temp tables should always be a part of the local DB that the users are working off of.  What, you don't have a local DB for each user?

The best way to setup a multiuser environment is to split your database into the program side and data side.  The data side is shared by all users but the program side, along with Access itself, reside either on the user's workstation (preferred) or in the user's home directory on the Network.

Since Access uses the program db to store temporary queries, results, etc., it is not wise to have everyone use the same program DB.

Jim

Jim - david,  suscinctly (?) - Precisely!  The front-end (FE) is the program (all the goodies that do things) and the back-end (BE) (i.e. all the data you don't want the user to mess with).

To elaborate, I had a FE that contained all the forms, etc., that the user needed to use the program, one for each user on their local machine, and one shared BE .mdb that "gathered" all the data on the server.  Note that you can use this structure even for a one machine stand-alone, and also note that this is good up to about 10 "seats".  More than about 10, use an SQL server...  The temp table is made on *each* user's FE and never interferes with any other user.

So, to recap, the original table resides in the BE, which is queried by the individual user's FE.  The temp table is made in the individual user's FE .mdb, and then deleted when no longer needed.

I'm probably going into unnecessary detail for you guys, but there it is.

I have a simple example of the forms and code if any of you would like to see it.  E-Mail and it's yours!

Merry Christmas (or your equivalent) and a prosperous new year.
good luck as usual...

Sorry, I'm not sure anyone knows the E-mail.  It is

tompenny@flash.net

good luck as usual...

The following is quoted from Jim

"The only way that you can do it the way that Tom is talking about without adding a field to the table is to create a new table.  However, you must have a primary key field in the table that you want to add a checkbox in a continuous form.  Create a new table with two fields:  a primary key field which has the same data type as the primary field in your table and a Yes/No field.  Go into the relationship wizard and join these two tables by the primary keys.  Make the join such that you show all records in the table you want to show.

Before you add the check table each time you should empty it.  This will allow you to create new records in it to track the checked values."

I think I have most of the concept. But one last puzzle remains. How is the above table created with the right number of records.
At this time is someone supposed to post an answer so It can be accepted? The answers seem to be spread out from different people and different comments. And by the way. Thanks guys you've been great.