TableDefs syntaxt question

[mcucino]

Hi All, I'm trying to produce the field name of the 2nd field in a particular table. I am trying to figure out how to use indexing and tabledefs but I'm getting "Invalid operation. (Error 3219)". Help! Thanks

  
dim DestTable as string
dim TheFieldName as string

DestTable = "MyTable"
TheFieldName = CurrentDb.TableDefs(DestTable).Fields(1)

[orange]

Why are you doing this with code?

[mcucino]

I actually just figured it out! all I needed was a .name at the end.

I am trying to manage a sequence of years as columns in a data set and I needed to see what year came up first. The user can edit the table and add new years or remove years, so whatever year shows up first is the one I need.

Now that I say that out loud, I realize it probably would be better to store a "start year" somewhere any time they change the table, but either way works, I suppose

[CJ_London]

really sounds like your tables are not constructed properly and having users edit table design is a bad idea. A very excel way of thinking. By having a 'year' column you are building data into the table design.

[mcucino]

The changes to the table columns are handled by VBA with a form based on user input with lots of data validation and error handling. We spent a lot of time and effort determining the best database architecture for our needs and it's working very well so far. If we used your typical normalized table structure with a year it would be a nightmare of super slow crosstab queries on crosstab queries for 60 years worth of data. Although I am always open to suggestions and input.

[CJ_London]

Without knowing the purpose of your dB and design constraints, not possible to suggest anything beyond normalisation considerations. I don’t propose to spend time trying to pursuade you to change something that works for you,