Continuous Form with independent CheckBox for each record

[ggfota]

I have problem creating Access (Continuous) Form with indepenent CheckBox for each record.
The user should be able to select records that will be processed by VBA based on ChceckBox value.
When I use Table as a source then there's no problem. I have field of Yes/No values, and I'm able to select each record inependently.
But when I want to use Query result as a source then it gets hard to get it working (probably it is not possible, but I need to confirm that).
I just need to populate Form with data of query result (AS Simple As Possible), to show data in a user friendly manner and be able to handle selected records with VBA script.

Example:

Let say that the user wants to do some operation on customers whose total order value is over some amount of money. Query result that is viewed in the Form and helps make decission is written bellow

select orders.cust_id, sum(orders.value), 0 as DoSomething
from orders
group by orders.id

0 is default value for the DoSomething field that should be rendered in a Continuous Form as CheckBox

Now I should be able in the form select those field that will be processed.
In VBA function I run across the Recordset of Form to obtain cust_id of selected orders and do with this value whatever I want to do.


I don't want to use "Select Into" in order to save results to table that will serve as source for what will be displayed in the Form.

Any comments will be apreciated.

[ChipR]

Your DoSomething field can not be editable because it doesn't exist in a table, and there's no record to edit.

[ggfota]

I do not intend to edit any source data.

I need to render CheckBox in the Form for each record of data displayed.
Let say that I want give a user a Check List interface (some information data + checkbox field), and command button that will fire any VBA script that will process checked item on the list.

If I add CheckBox component in Design View then selecting one will select also the rest. That's not what I expect.

Imagine a Form made in HTML. Form is rendered result of query that is viewed to the user. I can add independent check box to each row of data and determine (either by Java Script or by prcessing POST data on the server side) which cust_id have been checked.

[ChipR]

A checkbox in a continuous form must be bound to a table. Changing any one unbound or calculated control will change all of them. Your form has to have somewhere to save the Value of the checkbox for each record.

[ggfota]

So the conclusion is that there's no way around except do some mess by saving temporary query result to the table and use this table as data source for the Form.

[ChipR]

The check box on a continuous form solution is going to be messy, yes. Have you considered other options? If you use a main form with a list box, and a continuous subform, you could click a button in the record to add it to the list box of things to be processed.

[ggfota]

There no simple solution to create one (sometimes reusable) component i MS Access. Patterns and solutions (like MVC) existing/available in other programming languages proove that MS Access is rather simple tool.

I had to stay with table that holds form's data and keep in mind that if I copy the form I should also copy the table.

[poucedeleon]

OnClick Code

A workaround that I have found works is to create a checkbox control that is bound to the true/false value in your table or query. Make that checkbox hidden and name it something like checkboxname_va lue.

Then, add an unbound text control to the form with a default value of "X" or whatever symbol you want it to show. Make the control Locked so that users cannot change this value.

Then, to simulate the effect of checking or unchecking the "checkbox" add a Conditional Formatting rule to the textbox control. If the checkboxname_va lue control is "true" then make the font color of the textbox something visible. If the checkboxname_va lue is "false" then make the font color of the textbox the same as its background.

Then, add an OnClick event to the textbox control that changes the value of the checkboxname_va lue control to True or False. When the hidden checkbox value updates so will the formatting color of the textbox control itself, and you are therefore able to simulate a sizeable checkbox on a continuous form.

I have been able to use your post and get all of it to work, except I am stuck on the OnClick code. Did you write a code to change the checkbox value with each click in the txtbox? I was able to use the value "X" to change the value, but with the txtbox being locked that does not work for this situation. I would appreciate any help you can offer.