Custom Fields for End User?

[Doctor]

If it possible for me to set up a way for End users to add custom fields to
their database? If it is possible, how do I implement it? Surely someone has
done this before, but I can't find anything on it yet. Still looking...

Right now, I think I grasp what would be necessary in the table design: one
lookup table to define which types of fields are available (number, text,
etc...), one table to store the the custom fields, one table to store the
information that was entered into the custom fields, and a final lookup table
to hold options available for custom combo boxes. Is my thinking right here?

What I don't understand is how to display this in a form? I am thinking
about having an area on the form that would contain all custom fields, but it
would be empty if there are none to display.

Thanks in advance for your thoughts.

 

[Dorian]

This is really not a good idea. How would you query on these custom columns?
If you need something similar, set up a table with 3 columns:
UserName
DataItemName
DataItemValue
In this table, you can store for each user named data items each with a
single value.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Linq Adams via AccessMonster.com]

The statement "This is really not a good idea" is actually a gross
understatement! It's a terrible idea for so many , many reasons! There's a
reason why databases are developed by people trained in database development
instead of by end users!

 

[Gina Whipp]

Doctor,

I can only recall ONE instance where I allowed End-Users to *define* a field
thereby making it custom. So I would like to ask why you want allow
End-Users to do this? Do you expect them to be able to run reports on these
custom fields?

You should aslo note these fields cannot be *placed* on the form, you will
have to do this and then *make visible* should the End-User decide they want
this field added. (I know you don't want your End-Users in design view of
your form OR do you? Hmmm, that would also count for reports!)

So once again, why?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Linq,

Everybody has to learn someplace... I was once an End-User learning also!
SOMETIMES it's better to inform what the issues are before brow beating!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Doctor]

Thanks all for the responses. I wasn't aware that it was a bad idea. I don't
intend for end users to modify forms.

The whole idea seems like a good idea so that when inevitably the user calls
back to add some type of information that they realized they hadn't requested
to be tracked; then they could take care of it themselves.

Outlook allows for custom fields. PeachTree and Quickbooks all allow for
custom fields. Why is it taboo for Access?

I understand the point that it would be difficult to perform calculated
queries on these fields. But I had only planned to list the results of the
fields in a particular report.

 

[Gina Whipp]

Doctor,

It is not that it is a bad idea... it can be done BUT it's not the norm.
There are programs that allow for custom fields however, that does not make
it any easier to implement and those programs were not a quicky write
either. As I said, I have done this and got the fields on the reports and
forms but not for filtering.

The big concern would be that the End-User now wants to filter reports on
this newly added field OR wants it on a particular place on the form OR you
allowed for 4 extra fields and they want a fifth one.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

Outlook allows for custom fields. PeachTree and Quickbooks all allow for
custom fields. Why is it taboo for Access?

I understand the point that it would be difficult to perform calculated
queries on these fields. But I had only planned to list the results of the
fields in a particular report.

Because in addition to adding the field to the table, you would need to add
the field to SOME of the Queries referencing the table (those queries that
need that information); add a new textbox or other appropriate control to each
form referencing the table, and arrange it properly; do the same for each
Report which needs the field; possibly create a new lookup table if the new
field is designed to pick from a list of values, establishing referential
integrity....

If your database were just a table that the user is viewing in table datasheet
view, you *might* want to consider this - but if that's all you're doing you
would actually be better off using an Excel spreadsheet!