Access Boolean Matrix

Question

I am developing a form at work where I need to set "Properties" for a large number of (accounting) "structures". I have a “Value” field in which the user enters the value that the property should accept, and then I have 1 column for each structure, where the user should be able to check / uncheck each property for each structure. In addition, I need to be able to offer the user flags (for example, mapping properties to structures) so that he / she does not manually include all the flags that always need to be specified. Finally, the number of properties (rows) and the number of structures (columns) should not be considered fixed, although I do not want the user to modify it himself. I just want dev (maybe me) to not have time to add or remove structures.

At the moment, I used a local table, where each structure is a column, and I hard-coded my properties (which is good). However, I'm not sure that using a local table is a good design. We usually avoid using forms and tables in the same access database to separate forms and data. Also, I am wondering if there is an elegant solution that I am missing. There will be at least 10-15 structures and 11 properties that will handle the 110 flags (11 * 10) so that I cannot do this manually (i.e., create 110 flags and check 110 values ​​each time).

Here you can see how this part of the form looks.

I know that this will be a more frequent question, but I really need a design check on this, here are a few questions that I try to make as general and objective as possible:

• In Access, how can I create a matrix of controls, where 1 column contains a fixed (but variable using dev) number of properties, a “value” field that can accept text, and then more than 10 columns using Yes / No values?
  • Is it possible to do this without a local table?

VBA is perfectly valid.

Thanks.

Answer 1

In Access, how can I create a matrix of controls, where 1 column contains a fixed (but dev variable) number of properties, a “value” field that can accept text, and then more than 10 columns with Yes / No values?

I have never seen VBA code that does what you describe. VB6 allowed me to create "control arrays" for the logical grouping of controls (and bypass some restrictions on the number of controls on the form), but I never saw this for VBA.

Is it possible to do this without a local table?

Is it possible? Perhaps because you can change the form through code by opening it in the Design View and using CreateControl() to add controls.

Is it practical? Probably not, because the "Access Specifications" section of the Access Help mentions the following limit ...

"The number of controls and sections that you can add over the life of a form or report: 754"

.. so it sounds like code that changes shape many times can break down very badly after a while.

My recommendation would be to create a temporary table, use it, and then delete it. If you are worried about the popup interface, you can create a temporary table in a temporary .accdb file and then link it.

Answer 2

Maybe I missed something, but it seems very easy to me.

Make each structure a record.
Then, in another table, make each property a record with a 1-to-many relationship with structure. Thus, each structure will have many properties.

Then a form based on a structure with a sub-form based on its properties.

Default property values ​​can be set in the table structure.

And, of course, tables can be associated with another database.

Answer 3

In Access, how can I create a matrix of controls, where 1 column contains a fixed (but dev variable) number of properties, a “value” field that can accept text, and then more than 10 columns with Yes / No values?

I'm still not sure why you need multiple bit / boolean elements in one column, but you can use bit logic and a long integer column. VBA does something similar with its constants. For example, in MsgBox you create your type = vbCritical + vbYesNo . Its result is an integer value that VBA interprets to create a Critcal MsgBox with only Yes and No buttons. The reason for this is that the value of each constant is so different that any summation with the same constants leads to a unique value that can be de-analyzed. It’s pretty elegant from a user’s point of view, but I don’t want to do the math in the background to do such a job.

It might be better to maintain a separate table of available properties and / or sets of properties and build / maintain your properties page in this way. You can assign property sets as well as individual properties for specific controls. You can also indicate in any table whether there is a default value for this property and / or what value it is.

It is also likely that you do not need so many bits. I would be glad to narrow down my answer to your situation if you could clarify your question with more detailed information about the nature of your need for a bit.

Is it possible to do this without a local table?

You might be able to dynamically create DAO.Recordset in Access.

It might be better to set up property tables (see above) as a permanent table in one of your databases, rather than re-populating the same data every time.

Answer 4

You can use a more general / detailed approach by combining your bit fields into a string description describing the checkboxes that are checked.

For example, if you have a structure called 'structure1', the value is 100 and 5 bits of the condition, you can have one column of the row with the text 'cond1, cond2, cond7, cond8, cond9', and then another button that allows them to change this set of conditions for this single structure.

In any case, you will have fewer flags.

The only thing I can imagine in the equivalent of " a matrix of controls where 1 column contains a fixed (but changeable by a dev) number of properties " is an editable set of query results.

You will need a table for your structure -> default conditions, if nothing else, but it should not be in the same bar as the front.

NTN