Solved Problem with Checkboxes on Continuous Form

[Kayleigh]

Hi I'm having significant trouble with unbound controls on a continuous form - whenever I change value of one, all change with it!
I need to display approx three checkboxes on each row and be able to check/uncheck it as necessary. I have managed to create a checkbox out of a textbox but it is still changing all boxes. Only method which has been slightly succesful is conditional formatting which is very limited (I have not tried in VBA though).
When the user has ticked the form, they should be able record these values to the table by iterating through DAO recordset but this won't work if all are being recognised as the same value.
Problem with using bound controls is the recordset is not updateable (union queries) so I can't see any way round this
I think only option is to store values of these textboxes in a temporary table...
Would appreciate any suggestions here please!

 

[theDBguy]

I think only option is to store values of these textboxes in a temporary table...

That would be my suggestion too. Sorry.

 

[isladogs]

Continuous forms only have one set of controls so if a control is unbound, any change will affect all records
In other words, the checkboxes must be bound so they can be updated individually on a continuous form.

If you form is based on a union query, it will be read only so you have two choices
1. Use a temp table as you suggested
2. Alter you table structure so a union query isn't needed and ensure the form is updateable.

Option 2 is by far the better solution. Union queries are usually a 'kludge' designed as a workround to poor table design.

 

[Kayleigh]

My union query was a last resort as the data basically must display from two diverse queries in a specific order - and all query defs are dynamic. The union query is created by iterating through a previous query to select specific rows...

I will give the temp table a shot and see if it is manageable.

 

[MajP]

Another option is to build an in memory ADODB recordset. This is not a trivial thing so if you are not a good VBA coder you may want to think twice. In this example the pop up control is a disconnected in memory recordset bound to a form. This is a little more difficult than a temp table, but no issues with bloating. The code shows you how to build, load, and bind an in memory recordset.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

 

[Kayleigh]

@MajP I have actually used that approach in the past - with your help! See here
Was considering it here but because recordset not updateable it wasn't going to work...

 

[MajP]

It would work since you are creating an in memory recordset with is updateable. Then you load it with information from your other non-updateable recordset. No difference than creating and updateable temp table and loading it with information, except that the temp table would be physical.

 

[arnelgp]

maybe the idea here can give you some idea.

 

[Kayleigh]

@arnelgp your example doesn't use a union query but I have implemented some of the ticking bit. thanks

 

[arnelgp]

here, i used qryUnion as recordsource of the form.

 

[Kayleigh]

Thanks - that's what I had in mind.

 

[Galaxiom]

Unfortunately I can't open arlelgp's sample so I don't know if I am duplicating his suggestion. (Something about a language dll.)

I have used an ADODB disconnected recordset based on a query having a Cartesian join between the data table and a single record in a boolean field of another table. (This isn't as complex as MajP has shown n the link on Post #5. His code is simulating a multivalue field on a form so is obviously more involved.) In your case, use three Boolean fields in the second table.

Disconnected recordsets are not that complex, yet very powerful.
You need to create the recordset with adClient CursorLocation and adLockBatchOptimistic LockType Properties.
Disconnection simply involves removing the ActiveConnection Property after creation.

Then Set the form's Recordset property to the disconnected recordset object.

The disconnection causes all field of all records in the form's recordset to become independently updateable including the Booleans constructed from just one record in a table.

Saving is done with the loop through the recordset running dynamic or parameterised update or insert queries as you indicated in the opening post. MajP shows this process in his code.

BTW I have posted a sample database in the code repository that shows a very sophisticated technique to enable and disable simulated checkboxes on Continuous Forms using Conditional Formatting. The chextboxes (as I whimsically dub them) are implemented using a Class so it is very easy to use especially where multiple instances of enable/disable checkboxes are required on a form. The complete behaviour of the checkboxes very closely matches that of a normal checkbox and its label in every way. It also includes the optional capacity to scale the checkbox to any size required.

 

[Kayleigh]

I appreciate you breaking down the method. However I'm not sure if I'm ready to take the plunge for such a complicated approach. Although it probably is the best way forward.

I've started working with a temp table and it seems to be going in the right direction.