Sorry folks, Access World will not accept my posts (3 Viewers)

[Gasman]

So for the sake of helping @DakotaRidge, I created a prototype app that uses a multiselect list box for storing employee certifications in an MVF.

***I tried attaching the accdb to this post, and it attached, or didn't, I dunno. Guess I need 100 posts to do that?***

Can someone show @DakotaRidge and me how you can use a multiselect list box to store selected values in a normal child table, one record per value?

I imagine a vba public function that accepts arguments can be reused to accomplish the above?

View attachment 120720View attachment 120721View attachment 120722

***I tried attaching the accdb to this post, and it attached, or didn't, I dunno. Guess I need 100 posts to do that?***

DB is attached?

Very neat. How is it done?

 

[BlueSpruce]

DB is attached?

I think so. Are you able to download and open it?

 

[Gasman]

You can see it in your post?, and Yes, I downloaded and looked at it.
How is it done?

 

[BlueSpruce]

You can see it in your post?, and Yes, I downloaded and looked at it.
How is it done?

Yes, I see it. What do you mean by "How is it done?"... I drag/dropped the accdb to the post.

 

[Gasman]

No, how does the combo work?
I see no event code?

 

[BlueSpruce]

No, how does the combo work?
I see no event code?

Oh, it's not a fake cbo, it's just a plain vanilla multiselect list box I created with the wizard by dragging/dropping the control from the ribbon. I think this is how @DakotaRidge creates his MVF's, and I was hoping someone could show us how to store selected values in normal child tables.

 

[arnelgp]

The tables starting with Copy still have data in those fields. To run this again Copy the copies and rename those so they do not start with the word copy. Or add some tables and mark certain fields sensitve.

only problem with the code (possible to revise) is when the field you want to set to Null has a Property="Required". otherwise the only problem is if the field to set to Null is MVF (which you can't just set to Null, you need to open it as recordset and delete one by one).

 

[BlueSpruce]

only problem with the code (possible to revise) is when the field you want to set to Null has a Property="Required". otherwise the only problem is if the field to set to Null is MVF (which you can't just set to Null, you need to open it as recordset and delete one by one).

@MajP said he misunderstood the requirements. It's not clearing sensitive fields, rather deleting all records containing sensitive info.

 

[Gasman]

Oh, it's not a fake cbo, it's just a plain vanilla multiselect list box I created with the wizard by dragging/dropping the control from the ribbon. I think this is how @DakotaRidge creates his MVF's, and I was hoping someone could show us how to store selected values in normal child tables.

I expect you would have to parse the values and either append/delete/update records?
Probably easier to delete them all and re-add?

 

[BlueSpruce]

I expect you would have to parse the values and either append/delete/update records?
Probably easier to delete them all and re-add?

I think that's what @MajP's fake mvf code does.

 

[MajP]

@BlueSpruce,
Kind of confused on what you do not like on the fake MVF demo. It already does things that these other multi select demos do not and very closely mimics the MVF control.
1. It is parameterized allowing you to use it for multiple controls
2. It demos the concatenation that an MVF does to display the multiple choices
3. Unlike these multi select listboxes, the fake MVF limits the real estate and simulates drop down
4. Provides the checkbox functionality

Unless you build your own wizard all of these other approaches will still have the same limitations.
All of these will write to and delete from a junction table

Since the user can have all kinds of field names and datatypes for the lookup table, you are stuck with having to write use specific code for the insert and delete queries into the junction table. If you built a wizard it could do the same thing as the MVF and build the junction table based on the user inputs. Then you could make this more generic and have common insert and update queries because you could control the table name, field names, and data type. And that is why I a believe creating the MVF also requires a wizard because it too needs to know to build these hidden tables and what fields to use for them.

You can do this by adding a multiselect control on to a form. You could make this expand and contract to mimic a drop down and you can still do the concatenation. But that is far less user friendly than the fake MVF. You would have to build a control on the form. The pop up form gives you a lot more flexibility and reuse.

Your question is if you can use the MVF as a stand alone control related to a standard table is No. MS does not expose the MVF controls in any way through code or the user interface. If someone can do this, I have never seen it nor do I know anyone who has seen an example. Further, even if you could there is still the table relationship to the hidden table. So even if you could write to a visible table, you would be writing still to the hidden table.

If interested I will try to genericize it.

However, I deleted the whole Fake MVF because you kind of hijacked to be all about other things like multi select listboxes. You can always do that but that does not really mirror an MVF.

 

[mike60smart]

BlueSpruce, I would ike to see both a form and a report for the same recordset. Here's a pair that I have in my Finance and Health database for pet care activities. When I opened the lookup form, I noticed that it has 126 items. That indicates that an MVF can have more than 100 items, which is what I have read in the literature. I have no idea now what the maximum number of attributes is if you use lookup tables in MVFs the way that I do.

The form.
View attachment 120724
The report.
View attachment 120725

Hi DakotaRidge
Just out of interest as I do not know how, in the attached database supplied by BlueSprice, how would you calculate the number
of Employees whose Certification = CSP ?

 

[KitaYama]

Kind of confused on what you do not like on the fake MVF demo.

@MajP what happened to your faking MVF post? Did you deleted it? Why it does show up?

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

 

[The_Doc_Man]

For the record: The report filed Tues 7/29/25 (regarding selective blockage of a specific user in a specific thread) is now "Resolved" since Jon has clarified that the hypothesized method of blocking does not exist in the Xenforo software.

 

[MajP]

@MajP what happened to your faking MVF post? Did you deleted it? Why it does show up?

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

Yes. It was kind of hijacked with a lot of other things unrelated to trying to fake an MVF. Should have started a new thread instead. I am going to try to do a better example.

However, this example may interest you. Did not get much interest when I posted, but I think the concept is really useable. Demos multiple fake MVF controls to filter a form.
Expands and contracts
Concatenates the filter
Has checkboxes for selections

 

[KitaYama]

However, this example may interest you. Did not get much interest when I posted, but I think the concept is really useable. Demos multiple fake MVF controls to filter a form.

Thanks for posing it here. I just wanted to see how you've done it.
Thanks again.

 

[Joan Wild]

On a side note, it seems like a waste of time to maintain a lists of recalled products and the specifics. This is constantly changing information so attempting to maintain it locally seems like a horrible waste of time. Anyone needing information can open any browser and search for "recent food recalls" and get up-to-date information in an instant.

I've been scratching my head over this too. Can't you sign up for notification? Here (Canada) we can and even specify the type of recalls you want to be notified of.

 

[DHookom]

Hi Joan. Welcome to AWF. I assume you are the famous Joan Wild, former MVP!

 

[BlueSpruce]

@BlueSpruce,
Kind of confused on what you do not like on the fake MVF demo. It already does things that these other multi select demos do not and very closely mimics the MVF control.
1. It is parameterized allowing you to use it for multiple controls
2. It demos the concatenation that an MVF does to display the multiple choices
3. Unlike these multi select listboxes, the fake MVF limits the real estate and simulates drop down
4. Provides the checkbox functionality

Unless you build your own wizard all of these other approaches will still have the same limitations.
All of these will write to and delete from a junction table

Since the user can have all kinds of field names and datatypes for the lookup table, you are stuck with having to write use specific code for the insert and delete queries into the junction table. If you built a wizard it could do the same thing as the MVF and build the junction table based on the user inputs. Then you could make this more generic and have common insert and update queries because you could control the table name, field names, and data type. And that is why I a believe creating the MVF also requires a wizard because it too needs to know to build these hidden tables and what fields to use for them.

You can do this by adding a multiselect control on to a form. You could make this expand and contract to mimic a drop down and you can still do the concatenation. But that is far less user friendly than the fake MVF. You would have to build a control on the form. The pop up form gives you a lot more flexibility and reuse.

Your question is if you can use the MVF as a stand alone control related to a standard table is No. MS does not expose the MVF controls in any way through code or the user interface. If someone can do this, I have never seen it nor do I know anyone who has seen an example. Further, even if you could there is still the table relationship to the hidden table. So even if you could write to a visible table, you would be writing still to the hidden table.

If interested I will try to genericize it.

However, I deleted the whole Fake MVF because you kind of hijacked to be all about other things like multi select listboxes. You can always do that but that does not really mirror an MVF.

I never said I disliked anything about your mvf demo, on the contrary, it's cleverly crafted. I do kno someone else criticized it, something about fake criminal products made in china. I'm not going to mention names. Search the posts if it's still around.

What I am looking for is the best of both worlds, use multiselect list box and store the values in normal child tables. It would be great if somehow a generic function can be crafted so I can include the module in all my apps.

 

[Joan Wild]

Hi Joan. Welcome to AWF. I assume you are the famous Joan Wild, former MVP!

Hi Duane, yes, but don't know about 'famous'