Create list of Mandatory fields - that can be modified by the user

I have an unusual request- is there code that will allow the user to update a table that list the name of the Mandatory fields, that can be used to validate which fields are required on which form at any given time?

My client is requesting some flexibility in the design of the database allowing the Admin to modify which fields are required (mandatory).

My thought was to create a table that will store the name of the fields, table name.  Then use this table to verify which fields required data and this will allow the admin user to turn on and off which are mandatory on the fly without coding.

any ideas will be greatly appreciated.

Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Not sure if this is a good idea.
Required fields should be set by the developer and (for the most part) should not change very often.

If the ability to change these fields is granted, you may end up with a situation where certain fields are required one day, then not the next.
In other words, a field need to be completed one day, ...may be left blank in the future.

What if a field is required, then it is not... is it OK if there are empty values in the field? ...Then what happens if the field is changed back to requited with the empty values?
Who will manage the list?
Suppose a mistake is made?
What if two fields have the same name across tables?

The "Admin" wold have to be intimately familiar with all the relationships and consequences of changing a field property.
...As many unforeseen and unexpected data inconsistencies will result.
...(Then guess who they will call to fix these issues...?, or blame them on...)

Requests like this can be cumbersome to implement and manage.
They can also lead to other "unusual" requests.

just my 2c...
Lets see what other experts have to say...

Karen SchaeferBI ANALYSTAuthor Commented:
Thanks Jeff for your 2c worth, as usual you have a lot of could points.  I told my client I would research this issue.  I agree with what you say and this will back up my point with the client.  The client was planning for when I am no longer available to make the changes on their side of things.  Keep in mind that my client is use to keeping all the data in an Excel sheet and being able to change data sources and cell names on the fly.  I have been slowly educating them on the limitation of a Database design.  Which they decided to go, due to lack of data integrity, they don't seem to understand the willy-nilly approach will cause more problems then solve, so they better decide how they want to proceed in the design.  I am being a stickler for requirements and documentation prior to design, trying to insure that we have all the bases covered.

Thanks again for the input.

Jeffrey CoachmanMIS LiasonCommented:
Yes, always, ...lets see if other experts chime in with additional insight...
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
From their point of view, I see "mandatory" as something of a business rule matter. For example, for certain types of Customers they may require a BillingAddress. For others, they may not.

If THAT is the sort of functionality they're after, then I can see the need for this, and your approach of using a table to store those things is about the only way I know to do this.

Your data relationships, on the other hand, should be designed into the structure, as Jeff indicates. For example, in an InvoiceHeader table, a Customer foreign key is always required, and that requirement could never change (unless you wanted to change the entire program, of course).

So I see two distinct versions of "mandatory" - one the user could define at runtime, and the other the developer would build into the app.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
thanks Scott,  for you insight.  I will point out these issues with my client and let them make the decision, if they want me to spend the time writing code to change mandatory fields.  It may be a simple of turning the field color to mandatory color to give them illusion of manadtory data entry.

Thanks to both of you for your time and input.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.