I have a form that I would like the user to add fields when necessary  to

the form and give it a name.  I now I could  put the fields on but this

would not allow the used to select the name they want.  It is this ability

for the user to name teh field that is important in this application.

This is how I am thinking of doing it:



1. Create the table and give the field a generic name such as Field1,

Field2 and so on.  Set all the necessary field properties. 

2. Put these fields on the form and set the visible property to False and

set any desired field property.  A cmdButton on the form would allow the 

user to add the field by making it visible.

3. Write the coded that allows the use to enter the field name on the form

and this name is automatically updated in the table holding the field.



It is on this third step that I am stuck.



Question



Is this a possible feature and is this the correct way of doing this?

Is there a easier way for doing this?



Thanks for any comments

Howard,



On a more general point of view on whether this is the right solution:



1) Are the users capable of making a decision on field names?



2) Are the users going to make a similar decision every time a similar

field is required?



Unfortunately I have always found the approach of forcing users to make

decisions all the time is the best one, letting them make up things

themselves can be a route to disaster.  



Once you have all these (random) names how are you going to design

reports based on the entries if in effect there are multiple different

combinations?



Another way to look at this is think of all the required support

scenarios - can you cope with the demand, alternatively you may have a

job for life which is not a bad thing.



Just some thoughts and you are correct I NEVER trust me users to make

decisions if there is an option to make the decision for them.  Take the

customer name scenario, you have one field called NAME, think of all the

combinations a persons name can be entered as, that is why you should

use, TITLE, FIRSTNAME, SURNAME, etc.



If you are okay on the above points them I am sure the code exists to

update field names in a table, you need to use the TableDef object if I

am right, the Wrox Beginning Access VBA books has some text and examples

on it, the only problem you may come across is that if a table is open

via a recordset on a form you cannot change its design properties, ie

the field names so this may pose a problem.



Tell us some more about the project and I will see if I can think of an

alternative solution.



Regards



Paul





-----Original Message-----

From: Howard Stone [mailto:ququmber@h...] 

Sent: 16 January 2002 22:18

To: Access

Subject: [access] add field to form





I have a form that I would like the user to add fields when necessary

to the form and give it a name.  I now I could  put the fields on but

this would not allow the used to select the name they want.  It is this

ability for the user to name teh field that is important in this

application. This is how I am thinking of doing it:



1. Create the table and give the field a generic name such as Field1,

Field2 and so on.  Set all the necessary field properties. 

2. Put these fields on the form and set the visible property to False

and set any desired field property.  A cmdButton on the form would allow

the 

user to add the field by making it visible.

3. Write the coded that allows the use to enter the field name on the

form and this name is automatically updated in the table holding the

field.



It is on this third step that I am stuck.



Question



Is this a possible feature and is this the correct way of doing this? Is

there a easier way for doing this?



Thanks for any comments

Howard,



Everything Paul has said about user choices is very true. Free decisions

rather than a pick list can very often turn out to be a complete disaster.

(Inappropriate names, duplicate names, illegal characters ..... You name it.

Your users will do it!!!!)



However, back to your actual question, I have some preliminary questions of

my own:



Which of the following do you wish to do?



Allow the user to:



1) change the name of the Control on the Form



2) change the Data Source for the Control on the Form



3) change the Caption of the Control Label on the Form



4) change the name of the Field in the Table



5) change the Alias name of the Field in the Table



Also, is this a bound or unbound form?



Ian Ashton





-----Original Message-----

From: Paul McLaren [mailto:paulmcl@t...]

Sent: Wednesday, January 16, 2002 11:04 PM

To: Access

Subject: [access] RE: add field to form





Howard,



On a more general point of view on whether this is the right solution:



1) Are the users capable of making a decision on field names?



2) Are the users going to make a similar decision every time a similar

field is required?



Unfortunately I have always found the approach of forcing users to make

decisions all the time is the best one, letting them make up things

themselves can be a route to disaster.



Once you have all these (random) names how are you going to design

reports based on the entries if in effect there are multiple different

combinations?



Another way to look at this is think of all the required support

scenarios - can you cope with the demand, alternatively you may have a

job for life which is not a bad thing.



Just some thoughts and you are correct I NEVER trust me users to make

decisions if there is an option to make the decision for them.  Take the

customer name scenario, you have one field called NAME, think of all the

combinations a persons name can be entered as, that is why you should

use, TITLE, FIRSTNAME, SURNAME, etc.



If you are okay on the above points them I am sure the code exists to

update field names in a table, you need to use the TableDef object if I

am right, the Wrox Beginning Access VBA books has some text and examples

on it, the only problem you may come across is that if a table is open

via a recordset on a form you cannot change its design properties, ie

the field names so this may pose a problem.



Tell us some more about the project and I will see if I can think of an

alternative solution.



Regards



Paul





-----Original Message-----

From: Howard Stone [mailto:ququmber@h...]

Sent: 16 January 2002 22:18

To: Access

Subject: [access] add field to form





I have a form that I would like the user to add fields when necessary

to the form and give it a name.  I now I could  put the fields on but

this would not allow the used to select the name they want.  It is this

ability for the user to name teh field that is important in this

application. This is how I am thinking of doing it:



1. Create the table and give the field a generic name such as Field1,

Field2 and so on.  Set all the necessary field properties.

2. Put these fields on the form and set the visible property to False

and set any desired field property.  A cmdButton on the form would allow

the

user to add the field by making it visible.

3. Write the coded that allows the use to enter the field name on the

form and this name is automatically updated in the table holding the

field.



It is on this third step that I am stuck.



Question



Is this a possible feature and is this the correct way of doing this? Is

there a easier way for doing this?



Thanks for any comments

After reading the responses from the more experienced programmers I

realized that solving the problem of letting the users add fields to the

form does not solve the problem of calculations that involve this new

field.  It would still be necessary to work on the database to update the

new fields in queries to keep the database current.  I was trying to make

the db as automated as possible.



I will therefore not pursue that idea but ask for my own personal

development what would be involved in accomplishing such a task.



There would be no more than 6 fields the user would have to add on one form

only.  All the user would be required to do is to name the textbox and its

label   This was not something that would involve a large exercise on the

part of the user.



Thanks for the response.

Howard,



Firstly hopefully I was one of the "more experienced programmers" that

you were referring to!



I have done some testing and there appears to be a limitation with

changing the properties of a text box or label on an open form the same

way as changing table properties of an open table.  Basically it wont

work!



If the form is closed when the user defines the new text box and label

properties then it could be possible but is this suitable for your

scenario?



Maybe I am not on the right lines but I had what sound like a similar

scenario recently for an inbound sales operation, sales where grouped

into types, lets say for example Rental, Credit Terms, Cash Payment,

Cheque etc etc.  Each sales group had different criteria that needed

capturing but they were certain common factors that could be identified,

all had a customer, all had a product and all had a monetary value.

Beyond that there were unique criteria to be captured within each group

such as Rental had a minimum hire period, Credit Terms had a 'days to

pay' value.



Due to changes in the business new sales groups where introduced on an

adhoc basis so this brought in the need to create new forms with the

appropriate fields for the standard and for any unique values.



The solution provided one capture table for all sales groups, the table

had the standard fields (sales group, customer name, product, monetary

value and for good measure I added 10 extra fields that were a

combination of text, currency, number and date data types.  These ten

field were used as required for the additional text boxes on the form

and bound accordingly.



When the user selected a sales type and the form opened to add the new

record, certain values were written in via the OnOpen event, ie Sales

Group, Date, User and the user filled in the rest as required.



By creating a generic form for you to capture new sales groups with all

the generic coding present and all the text boxes in place you can

create new forms in a matter of minutes and save it with the appropriate

name.



For reporting purposes a top level report detailed count of customers,

count of products per sales group and monetary totals - ie all based on

the standard values - you really need to nail down what are the

important business determining factors that are required - it is all

well to provide a report that shows 100 different criteria but how many

of these are the management going to base there business decisions on

for the daily running of the operation - probably half a dozen or less

in most cases I'd bet so provide what they need and further analysis can

be provided if there are short comings identified in the main business

critieria.  Also the only people who should receive the reported data

are those in the business who are in a position to act on it - if it is

just for curiosity and they have no management input over the results

then refuse the report - brutal but effective! Curiosity report

recipients are always trouble as they often have no direct understanding

of what information is required to drive a particular part of the

business and ask for what can effectively be pointless criteria to be

included on the reports.  Another good routine I once used when I

suspected Curiosity recipients was not to send out any reports on Monday

morning and wait and see who complained - distribution was down 1/3 by

the next Monday!



Yes this does not get away from the form maintenance aspect but no

client could complain if the time taken is kept to a minimum and looking

at it from a financial point of view Access is a relatively low cost

solution so a little expense on maintenance is hardly going to break the

bank - anyway can you think of other similar software that allows users

to design forms with no development experience?



Hopefully this is along the right lines, apologies if it is not and I

have just wasted 10 minutes of your time reading my ramblings!



If I am on the right lines then maybe you are getting into the thinking

of not being just a developer but an active player in the business, let

me know and I pass over some more things that were learnt on the way to

providing a solution for an ever changing sales environment (more code

and less lectures next time!)



Regards

 

Paul









-----Original Message-----

From: Howard Stone [mailto:ququmber@h...] 

Sent: 18 January 2002 14:00

To: Access

Subject: [access] Re: add field to form





After reading the responses from the more experienced programmers I

realized that solving the problem of letting the users add fields to the

form does not solve the problem of calculations that involve this new

field.  It would still be necessary to work on the database to update

the new fields in queries to keep the database current.  I was trying to

make the db as automated as possible.



I will therefore not pursue that idea but ask for my own personal

development what would be involved in accomplishing such a task.



There would be no more than 6 fields the user would have to add on one

form only.  All the user would be required to do is to name the textbox

and its

label   This was not something that would involve a large exercise on

the

part of the user.



Thanks for the response.