How do I use varibale field names

[Greg]

I have a table which has a current month info and carries a 12 month rolling
history. At the end of each month i must copy the current month's data to
the relevant history field (ie. Jan for january, feb for february, etc). How
do i do this with a macro or with VBA. I am new to VBA. I currently edit an
update query at the end of each month, and manually type in the relevant
month in the field selection.

 

[Bob Barrows]

I have a table which has a current month info and carries a 12 month
rolling history. At the end of each month i must copy the current
month's data to the relevant history field (ie. Jan for january, feb
for february, etc). How do i do this with a macro or with VBA. I am
new to VBA. I currently edit an update query at the end of each
month, and manually type in the relevant month in the field selection.

The problem is the design of the table. The month is data and should be
stored in a column called HistoryMonth or something like that. This would
allow you to discontinue your timewasting practice of moving data around.

In essence, your "current month" table would become your history table,
simply by adding a column to store the month. If you wish to avoid deleting
data every month, you could also add a column to contain the year.

The alternative is to use VBA to prompt for the month and dynamically build
and execute a sql statement to move your data.

I'd rather not waste time going into details without hearing which path you
intend to take so I will end here.

 

[John W. Vinson]

I have a table which has a current month info and carries a 12 month rolling
history. At the end of each month i must copy the current month's data to
the relevant history field (ie. Jan for january, feb for february, etc). How
do i do this with a macro or with VBA. I am new to VBA. I currently edit an
update query at the end of each month, and manually type in the relevant
month in the field selection.

I'm with Bob; your table structure is simply WRONG.

For one thing, you should not be storing the 12 month rolling history AT ALL.
Just store the data and use a Query to select the past twelve months.