Access apps with user definable fields?

  • Thread starter Thread starter user
  • Start date Start date
U

user

Anyone have experience in building Access apps with user definable
fields? (Not the kind of fields where you just let the user define the
label for a pre set number of predefined fields.) I recently saw an
app that uses an MDB database and allows the user to define totally new
fields. The app maintains a table of the data fields. When a new
field is added, it is actually added to the table in the MDB.

He uses multi-column form into which he places the fields, using
predefined formats. His app is not Access so I can't just decipher how
he does it. However, I would be more interested in a way to
dynamically add new fields to specific that they be displayed at
specific locations on the form.

Would appreciate any information you may have from experience doing
such.

Bob

bobalston9 AT yahoo dot com
 
I have never seen this type of functionality in an Access application. I
guess I have never seen an application that needs to allow users to define
new fields. New fields would need to added to tables, queries, forms,
reports,...

This raises a red flag regarding your application design. It might be
justifiable but I can't see it.
 
Duane said:
I have never seen this type of functionality in an Access application. I
guess I have never seen an application that needs to allow users to define
new fields. New fields would need to added to tables, queries, forms,
reports,...

This raises a red flag regarding your application design. It might be
justifiable but I can't see it.
Click to expand...
I saw this concept it in a nice contacts manager software from Australia
http://www.biztechsoftware.com.au/

Their forms are not built in Access forms but the data is stored in an MDB.



In this app - from Australia, there is a very nice capability to add new
fields you want and even remove standard fields you don't. The changes
result in changes to the table definition. They have some kind of list
controlled form where they show all fields in user defined sequence in
3-5 columns on the form. Of course they have their own built in report
writer so impact on queries and reports isn't so great. Of course you
could define queries to select all/* fields allowing dynamic contents in
the table.

It seems fairly common for contacts management types of apps to have
varying field requirements. Most I have seen use the user definable
field approach where the user definable fields already exist.

I have an app I built for nonprofits (provided for FREE)who provide FREE
food, clothes and financial assistance such as prescriptions and to
avoid utility cutoff, etc. I have it installed in 8 locations. Every
user has new field requirements. Much of the data is only accessed via
online forms.

So adding fields by user, causing the table to be modified and providing
a way to show them on the form would be great for me. Instead what I
now do is add new fields that others won't mind that for really unique
fields, I add control switches to control whether certain fields are
displayed or not.

Hopefully that clarifies where I got the idea and why it appeals to me.

Bob
 
One solution that I have seen used in many applications is to create a table
or tables of user-defined "fields". Assuming you have a table of people with
a primary key of PeopleID. Now, create a table of attributes that a user
might want to use in the application. These would be like fields.

tblAttributes
================
AttribID
AttribTitle

Then create a table of people attributes:

tblPeopleAttribs
=================
PeopleID
AttribID
AttribValue

You could add a record to tblAttributes for "Favorite Color". Then use a
subform on your People main form bound to tblPeopleAttribs. You can either
select or automate the adding of the AttribID and then enter the person's
favorite color into the AttribValue field.

This type of structure is fairly common. It isn't too difficult to manage
the "attribute" table and values.
 
I saw this concept it in a nice contacts manager software from Australia
http://www.biztechsoftware.com.au/

Their forms are not built in Access forms but the data is stored in an
MDB.



In this app - from Australia, there is a very nice capability to add new
fields you want and even remove standard fields you don't. The changes
result in changes to the table definition. They have some kind of list
controlled form where they show all fields in user defined sequence in 3-5
columns on the form. Of course they have their own built in report writer
so impact on queries and reports isn't so great. Of course you could
define queries to select all/* fields allowing dynamic contents in the
table.

It seems fairly common for contacts management types of apps to have
varying field requirements. Most I have seen use the user definable field
approach where the user definable fields already exist.

I have an app I built for nonprofits (provided for FREE)who provide FREE
food, clothes and financial assistance such as prescriptions and to avoid
utility cutoff, etc. I have it installed in 8 locations. Every user has
new field requirements. Much of the data is only accessed via online
forms.

So adding fields by user, causing the table to be modified and providing a
way to show them on the form would be great for me. Instead what I now do
is add new fields that others won't mind that for really unique fields, I
add control switches to control whether certain fields are displayed or
not.

Hopefully that clarifies where I got the idea and why it appeals to me.
Click to expand...

I once had a database design where I had an "objects" table and a
"parameters" table. The parameters table defined everything about the
object, including what type of object it was. A setup like this allows
pretty much infinite flexibility with the data, but might require some fancy
footwork with the SQL.

HTH;

Amy
 
Back
Top