Multi-Select Checkbox Form/Sub-Form

[vbaInet]

Well guess what I just remembered? A new feature in access 2007 that will let you accomplish this task with ease. Do you use 2007?

 

[leem888]

yes I use 2007. What is the feature???

 

[vbaInet]

Heard of multivalued fields before?

 

[leem888]

only yesterday when I was looking for code examples... what's the process for the approach?

 

[leem888]

just to bring you up to date, I thought I'd just simplify the process for the user and just add a refresh button that they would click whenever they felt they needed to refresh the Client Metrics table with records.

The procedure would loop through the clients table, metrics table and client metrics table, then compare IDs to see which metrics are missing and insert those to the client metrics table accordingly.

This way, I won't have to concern myself with the addition of a new client and all the metrics they will need or with the addition of a new metric and the fact that metric will need to be associated to a client.

please tell me the multi-value is easier???

 

[vbaInet]

Nice. Doesn't it slow things down having to loop through the Clients table? It would have been more efficient to filter using the ClientID on the Client Metrics table, then loop through and update. Obviously using recordsets or even a hidden listbox since the number of values are rather small.

The only downside with multivalued lists is that they are not relational and you would use recordsets to iterate through the child list. Have a quick read :

http://office.microsoft.com/en-us/help/HA100241851033.aspx
Search for "Multivalued fields for complex data". Then check your help file for an example.

 

[leem888]

will do. I'll update the thread once I'm finished.

 

[dfenton]

Heard of multivalued fields before?

Multivalued fields are for end users and for Sharepoint compatibility. No one who can actually program in Access should use them.

It would be nice, though, if MS would add the checkbox functionality that you get with the MV lists to its standard combo/listbox controls.

 

[vbaInet]

You're right David. I've never actually used them, just had a quick read about them. Since they aren't relational I don't see how practical they are?

 

[dfenton]

You're right David. I've never actually used them, just had a quick read about them. Since they aren't relational I don't see how practical they are?

The weird thing about them is that they actually *are* relational, but the whole thing is hidden from you. And it's quite difficult to work with them in code (or it seems so to me), as you can interact with them only through recordsets, unlike any other data type in Jet/ACE. Once you get to that level of difficulty, for me, it's just easier to implement a standard N:N myself, which requires only trivial coding if any at all.

 

[vbaInet]

They are relational only to the field in which they are contained though right? Yes I saw an example in the help files using recordsets. This whole behaviour can be easily replicated using already existing VBA functions. Do you have any idea why they thought it would be useful? Developer demands maybe (which I would doubt)?

 

[dfenton]

They are relational only to the field in which they are contained though right?

Behind the scenes, they are actually stored in the usual way, with a join table and a lookup table. This is all hidden from the user, though, and is harder to manipulate for someone with programming chops than if the programmer just implemented it explicitly.

Yes I saw an example in the help files using recordsets. This whole behaviour can be easily replicated using already existing VBA functions. Do you have any idea why they thought it would be useful? Developer demands maybe (which I would doubt)?

Sharepoint compatibility was the driving factor, seems to me, as is the case with almost all the new features the ACE has gotten since its creation.