Change the format of a newly added Field in VBA

[Vindicator]

Hi there,
I have a form that adds new fields specified by the user to a table. My only problem now is that I need to be able to change the format of the new field.
Right now the user chooses the fields name and data type. However when the boolean or Yes/No datatype is chosen and created, since the format is not set all the values are in 0 or -1.

I tried the following

CurrentDb.TableDefs(tdf).Fields(strField).

but there is no "Format option"

Cheers

 

[boblarson]

You can't alter a field that way. You need to use the ALTER TABLE SQL and execute it.

 

[Vindicator]

Thanks, Ill have to look into it.

 

[missinglinq]

And also note that, IMO, it is really a very bad idea allowing users to dynamically alter tables like this.

 

[boblarson]

And also note that, IMO, it is really a very bad idea allowing users to dynamically alter tables like this.

A BIG AMEN to that statement.

 

[Vindicator]

Is there a better way to allow a user to add fields? Or in general its not good practice to alter tables.

Also I dont seem to be able to find a way to change the format using the ALTER TABLE method.

 

[MSAccessRookie]

Is there a better way to allow a user to add fields? Or in general its not good practice to alter tables.

Also I dont seem to be able to find a way to change the format using the ALTER TABLE method.

Why would a user need to add a field? Users that are adding Fields to a Table sounds like Spreadsheet style design, and perhaps revisiting the design could allow you to come up with another alternative.

 

[boblarson]

Why would a user need to add a field? Users that are adding Fields to a Table sounds like Spreadsheet style design, and perhaps revisiting the design could allow you to come up with another alternative.

I totally agree. Users should not be adding fields, modifying fields or deleting fields. If you have a properly normalized database you can add, modify and delete necessary RECORDS.

Also I dont seem to be able to find a way to change the format using the ALTER TABLE method.

I found it via Google on the first try:

ALTER TABLE table_name
ALTER COLUMN column_name datatype

 

[boblarson]

Oh, and Vindicator:

This is where I found it. But, as noted, you shouldn't need to do this.

 

[Vindicator]

Oh yes i came across that site too amongst others, however I think that is just for changing the data type, such as from a Number to Text type . I have no problem doing that. Its just when iv added a new field as datatype Yes/No the format is by default in "-1" and "0". I would like it to be "Yes" or "No" which can usually be chosen in the design view of the field under "Format". As far as Iv tried, I haven’t been able to use the ALTER TABLE SQL to do this. Although its proven useful to delete fields.

To the best of my ability I have tried to normalize the database logically. Everything is linked up and ref Integrity is enforced. However this database is going to be used for multiple studies. While using my basic design there may be extra fields needed in the future. Since Im leaving soon I wont be here to add fields or change the db to suit each study.

 

[boblarson]

Since Im leaving soon I wont be here to add fields or change the db to suit each study.[/FONT][/COLOR]

That would indicate that the database is not fully designed correctly. You should be able to set up a database so that you never (or extremely rarely) have to add fields. It is a matter of adding records to add or change items. For an example, see my sample here on being able to have different items for different people.

 

[Vindicator]

Thanks for your continued input boblarson. Iv taken a look at your sample database. I still dont think I cant get away without adding new fields. For instance I currently have a similar design to you db as you can add in new preferences, I can add new treatments for each animal. However what if you decided you wanted each individuals height? That would not go in preferences but would have to be in a separate field in tblPersons. Ideally I would create a database to suit each study, and would not have the user add new fields.

Aside from that I think it isnt possible to change the format throught code, but it will have to be done manually.

 

[boblarson]

However what if you decided you wanted each individuals height? That would not go in preferences but would have to be in a separate field in tblPersons.

Nope, it wouldn't need to be in tblPersons. It could actually be handled the same way as other items. It could be an Attribute which could then be added by adding another Attribute record and then in the junction table the value for the attribute. There is little you can't do with this method.

But, it is up to you how you want to approach it. I just gave a way where you can essentially track ANYTHING by the use of Attributes and a junction table.

 

[Vindicator]

hmm you know what I think I understand now. Anything can go into preferences, and any individual can have those preferences. I just forsee it being difficult to use a crosstab if I would want to display all persons and have all their preferences as fields.

Thank you boblarson for your help I'll see if I can replicate what you have done in your example.

Cheers,
Vind

 

[boblarson]

The thing to remember is that in most situations you might not use this to this great of an extent. But, it is a very generic way to be able to add things you might not foresee.

 

[dfenton]

You can't alter a field that way. You need to use the ALTER TABLE SQL and execute it.

If I'm not mistaken, the FORMAT property of a field in a table is an Access property, not a Jet/ACE property, and Jet/ACE DDL does not cover Access properties.

The way to change a field format is via DAO.

But it's not the kind of thing that I'd ever spend the time on doing. If I needed to alter the schema of a production app, I'd do it manually. If that was too complicated for whatever reason (can't do it in person, or via remote access), then I'd tend to send an empty shell database an import the existing data into that.

But this is the kind of thing that should happen only very, very rarely in a production app, so I think the question is misguided.