Sorry folks, Access World will not accept my posts

[DakotaRidge]

@DakotaRidge,
I read the requirement wrong. I assumed you wanted to clear certain fields, not certain records. So that solution will not help.
If you are deleting records, let me do a new demo. Disregard that.

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

Similarly, a person may not want a spouse to know that he has kidney disease. He could give his spouse a copy of the database after deleting certain records about his kidney problem. Or he may want to delete alcohol purchases from the system to hide that he is back on the wagon.

You will DEFINITELY want to reduce the thousands of reports and forms you have then. 2 pages EACH? For a printed manual you'll be contributing to the deforestation of the planet!

 

[DakotaRidge]

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

Similarly, a person may not want a spouse to know that he has kidney disease. He could give his spouse a copy of the database after deleting certain records about his kidney problem. Or he may want to delete alcohol purchases from the system to hide that he is back on the wagon.

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

Similarly, a person may not want a spouse to know that he has kidney disease. He could give his spouse a copy of the database after deleting certain records about his kidney problem. Or he may want to delete alcohol purchases from the system to hide that he is back on the wagon.

Yes, Mark. The last time that I worked on the manual for my proposal management system, the manual was more than 400 pages. That was several years ago. For each form, there was a screenshot and a page of text. For my Finance and Health manual, no one will print it out in hardcopy. I don't have to worry about that.

 

[MajP]

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?

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

You can do this, but no matter how much I tried I could not think of a way to make this completely generic. So this will always require the user to do some coding. You will see that area in the example where you would have to write the insert and delete query.

 

[MajP]

Yes, Pete. The operation is to remove specific records from the database. For example, one table stores credit card information, and the person may not want an adult child to know about a transaction.

post 45 shows the solution now to delete selected records.

 

[DakotaRidge]

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

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.

The report.

 

[Gasman]

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

You can do this, but no matter how much I tried I could not think of a way to make this completely generic. So this will always require the user to do some coding. You will see that area in the example where you would have to write the insert and delete query.

Could you not have a table that defines what fields are used and what to be set with?
Then just read for a particular table and command and build the strings?

tblSQL
SqlID
SqlTable
SqlCmd
SqlField
SqlSetTo

 

[Gasman]

BlueSpruce, I suspect AW counts the number of posts that a person submits to AW and if that number is not above a certain threshold, then the system blocks new posts to a thread

And I think that is complete rubbish.
Willing to be proved wrong though.?
@Jon

 

[Jon]

And I think that is complete rubbish.
Willing to be proved wrong though.?
@Jon

You are correct @Gasman, there is no such system.

In many cases, the user has quoted a previous reply in their response and that quoted section has a link in it. This often flies under the radar of new users here because they didn't type out the link themselves, but they still included a link because it was in the quoted section.

 

[Jon]

Tell you what I'll do... I'll post this so we see about blocking.

@Jon - members @DakotaRidge and @BlueSpruce have asked the question of whether it is possible within the forum software for an admin to:

(a) block a specific user's ability to post to a specific thread BUT
(b) allow the same user to post to other threads AND
(c) allow other users to still post to the specific thread in question.

Can you answer this question?

Follow-up: If it IS possible, was this ability used on member DakotaRidge?

No facilities for this in the software.

 

[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?

 

[Gasman]

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

 

[Gasman]

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

 

[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).

 

[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?

 

[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.