.CreateField Mehtod used with a Variable

[hockeyman9474]

I have an application that allows a user to select a month from a combo box and on the change event will delete a temp table and recreate it with information according to what the user selected. So i.e. the user selects June, temp_Table is dropped and recreated with columns 1-6(1 for Jan, 2 for Feb etc.)

Also in the on change event I have code that includes a counter that counts the columns/fields to know how many months exist. Next a loop runs adding the missing months as fields to the table using the current value of the counter. So if June is selected, the counter should be at 6 and the loop knows to run until it reaches 13 (the first column is not a month). With each iteration in the loop I am using the code below. My question is can I, use a variable to name a field. For example, cnt is my variable. I have scoured the internet all day looking for answer but alas have not been successful in finding one.


Set db = CurrentDb
Set tdf = db.TableDefs("temp_Table")


Do Until cnt = 13




tdf.Fields.Append tdf.CreateField(cnt, dbInteger, 5)
cnt = cnt + 1

Loop

[Bulzie]

Not clear on your issue but usually you can put a variable in parentheses to get its name?

For Each fld In rs1.Fields
z = rs1(fld.Name).Name
y = rs1(fld.Name)

[aytee111]

For starters, starting a field name with a number is a very bad idea. You could add a prefix such as "A_" & cnt....

Did you try running this routine? That is the best way to find out if it works or not.

[orange]

hockeyman,

Do you have a clear description of what you are trying to do in business terms --plain English?
Seems you have a method (how to do something) that isn't quite working. It would be better for readers
if we had a clear requirement. There may be many ways to do what you need.

[ssanfu]

I have to agree with orange. Right now what you have told us seems bad on many levels.

- Constantly deleting and creating tables will lead to dB bloat at the least and dB corruption at the worst.
- What you describe is a spreadsheet design, not a database design.
- The month names (Jan, Feb,...) you want to use as field names are actually data. Again, this is a spreadsheet design.


No one told me about "committing spreadsheet" , so it took me many years to understand the concept and correct my thinking about table designs.