Clear out data

[The_Doc_Man]

Are you using MVF's in this application?

He mentioned that usage in passing in one of the long-winded discussions.

 

[June7]

Could but don't expect OP to be open to idea. Doesn't like to use VBA. Lost cause here.
MVFs are essentially a normalized structure, just hidden. Beyond that, OP has rejected advice to normalize schema. See post 9.

 

[MajP]

Virtually all Access developers avoid using MVF's because the values are stored in denormalized hidden tables and querying them is cumbersome.

Although lots of people avoid mvf for many reasons, the hidden table is properly normalized. It is either a junction table contraining the foreign key to the main table and a foreign key to a lookup table. Or it is a child table with the FK to the main table and the values.

 

[MajP]

Please explain how those hidden tables can be adequately queried to read, update, or delete values. It's my understanding that in order to retrieve one or more elements stored in arrays you have to use non_standard .value subscripted queries.

@isladogs has a good explanation

Multivalued Fields

Multivalued fields (MVFs) were introduced with Access 2007. This article explains what MVFs are and how they can be used. However, most experienced developers avoid them completely. This article explains why this feature is really not a good idea

isladogs.co.uk

Also this demo shows a lot of functions that can be used on a MVF.
This demo uses three MVF to query three MVFs.

At the top you can use the MVFs for Region, Work Category, and Work Type. This will filter the corresponding records in the Company table. You will see additional functions to write and delete from these lists.

I am not saying I use these things, I am just trying to describe the reality of what they are and how they work.

 

[GPGeorge]

I think an essential point to understanding the problem that is MVFs would be that they are seductive to new, inexperienced people, who are more likely to abuse and misuse them. At the same time, experienced, knowledgeable developers, who do understand the inner workings of MVFs, tend to be less motivated to use them, precisely because of that knowledge.

 

[LarryE]

I think an essential point to understanding the problem that is MVFs would be that they are seductive to new, inexperienced people, who are more likely to abuse and misuse them. At the same time, experienced, knowledgeable developers, who do understand the inner workings of MVFs, tend to be less motivated to use them, precisely because of that knowledge.

I am just glad the OP is only using ACCESS for his own purpose and not an architect designing buildings or bridges.

 

[Uncle Gizmo]

This is an excellent video on Multi Value Fields from >>> Takeshi K --- demonstrating lots of ways you can programmically interact with multi-value Fields --- for more information see here :-

Multi Value Fields Links​

Multi Value Fields Links - Nifty Access

Multi Value Fields Links Consider a scene at a Microsoft office where an innovative individual, perhaps from the marketing team, suggests, "MS Access requires 'Multivalued Fields' 💡 - our aim is to simplify the usage of MS Access for individuals!" Envision the developers emphatically voicing...

niftyaccess.com

 

[Mark_]

@MajP, but that intersection works just perfectly when you realize that each line is intended for a different type of traffic! One for blue traffic, one for yellow, one for red, one for green, and an extra to cover non-standard traffic that doesn't adhere to the standards! Also has a bypass for overly slow or quick traffic, just to be safe!

 

[LarryE]

The OP clearly stated he is not a developer so I feel it's inappropriate to humiliate him, or any other AWF member who is not an experienced developer.

Thousands of non-developers and beginning ACCESS users have come into this forum and taken the advice given to them. They wished to design their projects properly. This OP clearly stated he knows he is designing his project improperly; he wants to design it wrong, and several people have given advice as to how to do it correctly. He refuses to take the advice given and states he wants thousands of tables and forms and doesn't understand what is wrong with that. No one is humiliating him. He did that to himself. He does not want to use the product in the manner that it was designed to be used and refused help. I do not have any sympathy. And I still don't want him designing anything that matters if he is going to do it backwards.

 

[Gasman]

The OP clearly stated he is not a developer

Not anymore perhaps, but he used to continually boast of his main DB he created (that had even more objects) that dealt with I cannot remember what now, such is my memory, but involved million dollar projects.
This DB he mentions here started off as a finance DB like Quicken, and evolved to encompass medical data.

 

[Gasman]

Some of my lookup tables have logically "Deleted" and "UserAdded" boolean columns. If a value has a Deleted checkmark, the value will still appear on the list, but an X will appear in the 2nd column of the dropdown list and users won't be able to select it. In certain comboboxes I allow users to dynamically add new values on the fly when users enter values not in list. However, a msgbox popup warns users to verify if any values already on the list should be used instead of adding the new value.

View attachment 120701View attachment 120702View attachment 120703

TBH, I would be asking why you are supplying something that whilst can be selected, should not even be shown?

I remember seeing a program where one had to select a town in England, yet the combo showed towns in Scotland and Wales.
If you selected one of those, then you got a similar message, yet a simple filter for the country would have prevented that.

When I mentioned it, I was told 'Well yes, in a perfect world, that would be nice'
One simple filter, 5 seconds to implement, 20 secs to test.

 

[cheekybuddha]

The OP clearly stated he is not a developer so I feel it's inappropriate to humiliate him, or any other AWF member who is not an experienced developer.

Welcome to AWF

 

[The_Doc_Man]

The OP clearly stated he is not a developer so I feel it's inappropriate to humiliate him, or any other AWF member who is not an experienced developer.

Can't speak for others, but this is how I see it.

When you are in a classroom and you ask a question or make a comment that is based on an erroneous assumption, don't you want the teacher to correct you? And in that public situation, should the teacher allow the others who heard the bad question be left unclear about the error?

Are we going to worry so much about the erroneous student's feelings as to be unable to correct him/her AND correct the impressions taken by others who witnessed the exchange? If we cannot offer correction in a public forum, what is the point of the forum existing, much less being public?

 

[GPGeorge]

There is a difference between correction and public humiliation, though, is there not?

 

[The_Doc_Man]

There is a difference between correction and public humiliation, though, is there not?

Since I became a moderator, I have tried to tone down my impatience with recalcitrant members who fall into the "lead them to water but can't make them drink" category. If I have any problem along those lines, it is sometimes that my directness is taken as a bit abrasive.

I try to call things as I see them and yet NOT call the "difficult" person "the flower of his family - a blooming idiot." We all need to be careful because directness is often the most verbally economical type of response, but also the most susceptible to misinterpretation of intent. It's like walking a verbal tightrope. There WILL be moments of misunderstanding. But that risk still doesn't mean we shouldn't answer the question or correct the erroneous comment. If we don't do that much, we fail in the overall mission of the forum.

 

[The_Doc_Man]

Tolerance and Patience are virtues.

True. So are "Willingness to learn" and "Willingness to correct errors."

Do you know why many of our forum members are considered as "experienced"? It has to do with Ambrose Bierce's definition of "Experienced" - which means "able to recognize your mistakes when you make them again." We offer lessons tinged with experience!

 

[LarryE]

Some ppl learn by watching others make mistakes, by taking advice, or making the mistakes themselves and realize they have to change.

Absolutely correct, but this particular OP was not one of those people. I don't care what you are building. If you knowingly design it and build it without a proper foundation, you know what happens. C'mon man...thousands of tables and forms is just stupid. Even if the OP was not going to distribute the project file to others. Anyway, he got the answer to his question in the 2nd post. You have to do it yourself.

 

[LarryE]

And the rest is just nonsense so why are we here?

Inherent sense of responsibility I suppose.

 

[DHookom]

This is the 79th post in this thread which should have moved to the Watercooler 70 posts ago.

 

[Isaac]

I have been working on a database since January. I am about ready to send it to members of my family, but first I want to remove confidential data that I used to test the application.

Of the 450 tables, perhaps only 50 of them have any sensitive information that I would remove. The sensitive data are medical records. There are no social security numbers or credit card numbers, but someone may want to store that information in the future.

Perhaps only one record in a table with a hundred records may have sensitive data.

I would also like to give my family an easy way to clear out their personal data if they ever want to send the database to another family member.

Is there a tool that will do this? The tool needs to only delete selected records in a table. I will add checkbooks to tables for this purpose.

Thanks.

Isn't that what queries are