Allowing non-programmer to edit options in an Access Database (1 Viewer)

[padlocked17]

All,

I'm working on a new database that I'm hoping will be easy for someone with no programming experience to pick up and add certain options to the database.

One example is below:

I want the end user to be able to add collectible information of varying data types. The database will be used to collect information about trips the individuals travel on. It will collect base information such as location to location, times, and dates. There will then be the option for the individual to add additional data types to enter values on from a listbox or something similar.

The additional information will be items such as cargo moved, passengers moved, Trip Delayed, etc.

I want these additional information types to be scalable so that I can build the database and create a form where someone else with no Access experience can add additional pieces of information they want to collect. The problem I'm running into is that I want the non-experienced Access person to be able to add items of different data types (Text, Memo, Currency, Yes/No) fields from a form and then I need to devise a way to store the options they create and the actual values of the those options when someone goes to use the database.

I've got a table with the trips individual travel on that have the TripID field. I then have a tblLegs table that has the MissionID and a LegID Autonumber column to associate with trips and legs of the trip.

My initial thought was to create a table like below to house the possible options:

tblOptionalData
--------------------
OptionID........FieldType
1..................Memo
2..................Yes/No
3..................Text
4..................Currency


Then when the user added a particular option to the trip, I would have some VBA that would show/hide either a Memo, Text, or Checkbox depending on the FieldType of the option. Some VBA would then further add the value to the correct column in the following table:

tblOptionalDataValues
-------------------
ValueID........OptionID........Text..........Memo..........Currency........Yes/No
1.................1..................................All text here................................
2.................2........................................................................Checked
3.................3.................All text here.................................................
4.................4....................................................234.5.......................

Obviously there would be more columns in each table to match them all up, but is this a good concept to go with.

Again the option is to allow different collection fields to be added by someone with no Access experience of varying data types. I would then need to save the collections fields that admin creates and save the data for each field somewhere, somehow in the correct datatype for calculations at a later point.

I know this was long-winded. If anyone has any experience in making something like this I would appreciate it.

 

[spikepl]

Hmm ..

Again the option is to allow different collection fields to be added by someone with no Access experience of varying data types. I would then need to save the collections fields that admin creates and save the data for each field somewhere, somehow in the correct datatype for calculations at a later point.

So which is it? If individual users can individually add fields, you'll wind up with a useless mess: User1 adds NIceGirlsMetOnTrip and User2 CellNoOfAllTheGirlsIsaw - and what will you do with this? The information will only be of value to the one that established it. If this is related to your previous post about uploading trip-info to some main DB, how do you imagine to accomplish that? You are better off making a tool so that all the "clients" (ie the bring-along Access-dbs) can be expanded on demand or automatically on next connect- but that means ALL

 

[padlocked17]

Hmm ..



So which is it? If individual users can individually add fields, you'll wind up with a useless mess: User1 adds NIceGirlsMetOnTrip and User2 CellNoOfAllTheGirlsIsaw - and what will you do with this? The information will only be of value to the one that established it. If this is related to your previous post about uploading trip-info to some main DB, how do you imagine to accomplish that? You are better off making a tool so that all the "clients" (ie the bring-along Access-dbs) can be expanded on demand or automatically on next connect- but that means ALL

Bad wording. I was trying to be concise, but lacked a little.

Assumptions:

1. All users who will use this have no clue about Access.
2. There will be an Admin of the database and they are the only one allowed to create additional collections fields.
3. The end user will only be able to select from the fields the admin created.

Does that clarify a little?

 

[plog]

From the sounds of it you are trying to build Access using Access.

You want software that lets people add fields to tables, name those fields and assign datatypes to them. That's Access.

You want that software to then allow people be able to add those fields to forms. That's Access.

I can see where this is going. Soon, your users are going to want to be able to use those fields to sort their data, so next you will be building a system that allows them to build queries. Then they will want those fields to show up on hard copy outputs so you will have to build a system that allows them to build their own reports.

You're building something that exists using the exact same thing you are trying to build. The simple solution is just to teach them how to use Access to do all those things. The correct solution is to have them ask someone who knows what they are doing to accomplish the things they want to accomplish. Never, ever allow users to add fields to databases--they have no idea of normalization, they will use improper datatypes, they will add fields that only they know how to operate, if you have multiple people adding fields I'm sure they will add fields that duplicate fields others have added. Bad idea all the way around.

 

[padlocked17]

From the sounds of it you are trying to build Access using Access.

You want software that lets people add fields to tables, name those fields and assign datatypes to them. That's Access.

You want that software to then allow people be able to add those fields to forms. That's Access.

I can see where this is going. Soon, your users are going to want to be able to use those fields to sort their data, so next you will be building a system that allows them to build queries. Then they will want those fields to show up on hard copy outputs so you will have to build a system that allows them to build their own reports.

You're building something that exists using the exact same thing you are trying to build. The simple solution is just to teach them how to use Access to do all those things. The correct solution is to have them ask someone who knows what they are doing to accomplish the things they want to accomplish. Never, ever allow users to add fields to databases--they have no idea of normalization, they will use improper datatypes, they will add fields that only they know how to operate, if you have multiple people adding fields I'm sure they will add fields that duplicate fields others have added. Bad idea all the way around.

Thanks for knocking some sense into me. I never quite thought of it that way. I will be putting the ax to this idea and go with developing it how I normally would.

Thanks for knocking some sense into me.

 

[Rx_]

You are getting some very good advice in the previous post.

Let me share with you something that might help or just some food for thought. You mentioned having a user form and an Admin module. In the Admin module my preference is to allow selected non-programmers administrate things such as what appears in a pull-down listbox.

In the table, there is a column named Active. It has one character that will hold "A" for active and "I" for inactive. The default for a new record is "A".

Lets say that this month your admin wanted to add a new item to the pull-down list box for Search And Rescue "SAR Nov_2011" - The admin user via the admin module adds a new record. The list box for your form uses the table and has a filter to only show the records with an "A". Now users can add this to their SAR form entry. Next month, the SAR Nov_2011 can be retired. The Admin user simply replaces an A with the I and this record no longer appears as a line item on the pull-down list box.
But, this is very important: This record with its primary key and description is not deleted. It is just not available for a user to choose. The quarterly and yearly reports still have this primary key and description to be used with all the linked or associated records. If this record and primary key were deleted, it will mess with your references.

Attached is a table (list items hidden) - This list of items supports the list box on 5 different forms. Each Form's list box has a filter for Activity and then the column matching that form. The Activity turns off the listing for all forms. As you can see, some forms list the item, and some dont based on if an A or I is in the column.
If a report is created later, an ID can still be used even if that ID is now Inactive (not available on a form).

An Admin module gives the administrator this table view. They have instructions about adding a new ID and name, and how to control if that record is available on the pulldown listbox for 5 different forms. KISS principle.

Non-programmer administrators (with a little documentation) get this idea very quickly. And, you are no longer bothered with updating list-boxes.

Hope that helps to give you some ideas.
Thank you for your service to our country.
Programmer by Day - US Air Force Aux (Civil Air Patrol) by night