programatically change fieldnames in a table

[John]

Hi all,

any help with the following will be much appreciated.

TableA has 5 fieldnames named "Wk1", "Wk2" etc.
The user will input a week number, e.g. last week is "37".
I would like to run a macro that changes the fieldnames in
TableA to "Wk37", "Wk38", "Wk39", "Wk40" and "Wk41".

Can i refer directly to a table object and fieldname using
a "Set" command?

TIA,
John.
Ireland.

 

[JohnFol]

I would question why you are storing data within the field names in the
first place. . . ..

 

[John Vinson]

Hi all,

any help with the following will be much appreciated.

TableA has 5 fieldnames named "Wk1", "Wk2" etc.

Then it's an incorrectly designed table. You're embedding a one to
many relationship within a single record - a venial sin called
"committing spreadsheet upon a database".

You'ld do much better to have TWO tables related one to many; store
the week number (or, better, a Date) in each record with the value for
that week. Storing data in fieldnames is, as you are experiencing, a
bad idea and a cause for unnecessary complications!

The user will input a week number, e.g. last week is "37".
I would like to run a macro that changes the fieldnames in
TableA to "Wk37", "Wk38", "Wk39", "Wk40" and "Wk41".

Can i refer directly to a table object and fieldname using
a "Set" command?

No.

 

[Tim Ferguson]

TableA has 5 fieldnames named "Wk1", "Wk2" etc.
The user will input a week number, e.g. last week is "37".
I would like to run a macro that changes the fieldnames in
TableA to "Wk37", "Wk38", "Wk39", "Wk40" and "Wk41".

This is what is known in the trade as a Really Bad Idea. You are sure to
find life much easier and safer with a table that looks like:

WeekNumber SomeValue
1 57
2 109
3 33
4 9
... ...
39 45
40 121

although if it is meant to last beyond December, you might want a
YearNumber in there too.

Best of luck


Tim F