Dynamic controls for multiple choice via db records

[1DMF]

Hi,

This is more a design concept request 'How would you do it' rather than an actual issue per sae.

I have a table that stores records relating to reasons why a checker may issue a charge against a case.

This is a dynamic table that could have reasons added at any time.

I wanted to design a form that has tick boxes next to the possible reasons for the user to tick the ones that apply - as the line manager making the request asked for tick boxes.

However, I understand dynamically adding controls to forms is a big no-no, especially as there is some lifetime limit on number of controls added / deleted to forms, not to mention you can't have a form in design mode when running on Access Runtime.

So I was thinking perhaps I could use a listbox with multiple select, and although it doesn't give the requested 'tick boxes', it will make dynamic population from a data set easy.

Or perhaps I could use a continuous form and have some toggle code to select/deselect the rows, perhaps background colour change?

How do you dynamically pull records from a table and offer them as multiple select options on a form?



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

You can add a boolean field ("Is_Selected") to the "charge" table. Then format the table on a continous sub form to make it look similar to a listbox. Then you have to give it functionality to appear as if it is a bound control. So assume the main form is "Cases" and the subform has the "ChargeCode" table with a boolean field "IsSelected" to look like a listbox with checks. I assume the charges are stored in a child table "CasesCharges" with a foreign key to the "Case" table. This is then the order to simulate the functionality.
1. Move to a new "Case" record on the main form
2. run a update query to clear the "IsSelected" field in the "charges" table and subform
3. Either using recordsets or sql read the charge codes related to the current case. Then if there are existing charge codes select (make IsSelected true) for that each charge related to that case. So if there are charge codes they show as being selected each time you change a record.
4. Click/unclick your choices in the subform
5. On the main form afterupdate, update the child table with the selected values. Easiest way is to do a delete query of all child records and then an append query for each charge selected.

With some careful formatting and some code it will appear to look and act like a listbox with checks.

Depending you your Access version you can also use a listview. The listview is an active x control MSCOMCTL.OCX and has this functionality built in. There is not much documented on using these and not sure if they are still supported. I have read there is no longer a 32 bit version, but cannot confirm that. However, these work well and look nice, but portability is always an issue.

 

[1DMF]

Hey MajP,

I looked at a similar process myself, but it seems very convoluted, so am working with a list box.

I don't like the idea of having additional fields in a table and I'm not sure it works with my CDM...

Basically cases go into Case_Checking, when the checker grades the case it is recorded in the Case_Checker_Grades (You'll see the one to many relationship 'Graded' as the case may be graded several times before it is fit to be passed of as compliant), depending on the grade and what may be wrong with the case, the case may incur an ACC (Additional Compliance Charge).

Originally Case_Checker_Grades simply had an 'ACC' column that stores either 'Yes' or 'No', now they want a refactor to include why the charge was made.

These reasons will be dynamically created with prescribed text, that the checker's choose (taken from ACC_Charge_Type).

When the grade is recorded if any of the ACC possible reasons are selected, those selected are stored in the table ACC_Reason entity.

So far I have this...

Basically a listbox with the ACC_Charge_Type as the data source.

The problem with this is that dialog window is rather large to fit the longest reason in (183 char), because I have found listboxes don't allow text wrap.

I looked at MSFlexGrid but that came up with an error when I tried to use the control and when I looked into it , it seemed there was a versioning nightmare with this control and I don't want to start adding a rod for my back with custom ActiveX controls. It seems this could be the same with your listview control?

I can't see any native word wrapping multiple select controls in MS Access 2010 - am I missing something here?

So the constraint of 183 chars and using the current listbox may be the quickest / simplest solution as I need to get a working app before I go on paternity leave in a week or two, and it will require properly testing before rolling out.

If I do stick with this listbox control, I need to sort out the default black highlighting, it's awful!






"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[MajP]

using the current listbox may be the quickest / simplest solution

Using a native control is always the fastest and safest solution. But personally I hate the extremely limited capabilities of the Access multiselect listbox, as you are finding out. I do not find them to be very intuitive either. I would not mess with the flexgrid, it has even more portability issues and again not sure if supported in 64 bit versions.

I looked at a similar process myself, but it seems very convoluted,

It sounds a little more complicated than it is. The amount of code is really quite small. I will try to post an example when I get some time. Looking at your image, I think you could create a more aesthetic control.

One final thing. I am not a big fan of multivalue fields, because most people do not understand how they work. However, if you spend some time to understand how they work then this looks like a case where they would work well. They come with a native control to check and uncheck. Not sure how that would work with the long text.

 

[1DMF]

I'm not familiar with a multi-value control, where is it and how do I put it on the form?

Edit: I'm going round in circles trying to work with the listbox.

There seems no way of selecting a row in a list box and changing its background / foreground colours for the highlighting.

This control sucks!

Can't manipulate the entries, can't text wrap the entries, can't manipulate the colours...bah continuous sub form with shitty parent / child bound code it is!

Edit: looks like you can't use conditional formatting to highlight the rows , as it only works for one row at a time, not multiple select!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music