Access:VB:renaming fieldname using variable

I'm not sure what you mean. You appear to have 2 recordsets, or a typo.
You are also trying to change the value of field in rst named "field" which I would say will cause you problems.  I don't know if rst![field] would work or not.  I don't think you can change the actual field name and if you could it wouldn't be named "field" I would think.

I'd think you would execute an "ALTER COLUMN" sql statement to change a fieldname, (this is for sure who to do it in other databases.

ouestque,

nobody will understand what you are trying to do, the way you are presenting your problem.

give information in details....

I think I get it

rst1!field = rstm![value]  

should be

rst1(rstm![value])

that will use the rstm!value for the fieldname in rst1
J

Sorry, I should have been more specific. I have a table with field names between 1-50 (rst1)

The following will loop 50 times (because counter = 50) Also notice that field is a variable (Notice how it changes with each loop. On the firs loop field =1 on the second loop field =2 and so on&) I am trying to get rst1!field to change with each loop. On the first loop rst1!field = rst1![1], on the second loop rst1!field = rst1![2] on the third loop rst1!field = rst1![3] and so on up to 50.

The problem is that rst1field has the wrong syntax. I want the variable named field in rst1!field to change with each loop.

In effect the following code takes the values in table 2 and enters each single value in table1 in the first record from left to right.

i.e.                                      End Result after code is run

Table2                                Table1
1                                          1 2 3 4 5
2
3
4
5

Notice above how the numbers go from up to down to left to right. I have the sample code below, but the only part to focus on is the rst1!field part is that little piece using the right syntax to make "field" change with each time the code loops? Sample code is below

Set rst1 = dbs.openrecordset(table1)
Setrstm = dbs.openrecordset(table2)

Dim field as integer, counter as integer
Counter = 50
field = 1
                  Do
                                                                     
                        rst1!field = rstm![value]<-----------------------------------------This is trouble area!!!
                        rstm.MoveNext
                        rst1.Update
                       
                        field = field + 1
                        Counter = Counter - 1
                 
  Loop While (Counter > 0)

P.S. Pretend the squares in my above comment are quotes.

When I transferred my text from word to the Experts-exchange comment window each quote turned to squares for some reason

try this

Dim dbs As DAO.Database, rstm as dao.recordset, j as integer
Set dbs = CurrentDb

Set rstm = dbs.openrecordset(table2)

rstm.movefirst
With dbs.TableDefs("Table1")
j=0
do until rstm.eof
   
    .Fields(j).Name = rstm(0)
     j=j+1
    rstm.movenext
loop

   
End With

change this

Set rstm = dbs.openrecordset(table2)

to

Set rstm = dbs.openrecordset("table2")

Also... When the above code runs it says "Item not found in data collection" and highlights "rst1!field"

The following is not working for me.
rst1! ["&field&"] or rst1! ['&field&']

ouestque,
so, you want to change the names of the fields in table1?

Thanks for the feedback!  :) but lets start over.

I have a table1 with the following field names: 1,2,3,4
I have a table2 with a single field name called Value (There are 4 records in this table)

Ideally the first snippet of code should do the same thing as the second snippet of code.
 I think the problem area is rst1![fieldname]. Fieldname is a variable , but AccessVB is recognizing it as a field


FIRST CODE      
Set rst1 = dbs.openrecordset(table1)
Setrstm = dbs.openrecordset(table2)

Dim fieldname as integer, counter as integer

Counter = 4
fieldname = 1
                  Do
                                                                     
                        rst1![fieldname] = rstm![value]<-----------------------------------------This is trouble area!!!
                        rstm.MoveNext
                        rst1.Update
                       
                        fieldname = fieldname + 1
                        Counter = Counter - 1
                 
  Loop While (Counter > 0)



SECOND CODE


Counter = 4
 Do
                                                                     
                        rst1![1] = rstm![value]
                        rstm.MoveNext
                        rst1.Update
                       
                        Counter = Counter - 1
                 
  Loop While (Counter > 0)

Counter = 4
 Do
                                                                     
                        rst1![2] = rstm![value]
                        rstm.MoveNext
                        rst1.Update
                       
                        Counter = Counter - 1
                 
  Loop While (Counter > 0)


Counter = 4
 Do
                                                                     
                        rst1![3] = rstm![value]
                        rstm.MoveNext
                        rst1.Update
                       
                        Counter = Counter - 1
                 
  Loop While (Counter > 0)

Counter = 4
 Do
                                                                     
                        rst1![4] = rstm![value]
                        rstm.MoveNext
                        rst1.Update
                       
                        Counter = Counter - 1
                 
  Loop While (Counter > 0)

ouestque,

what are trying to do?????

I want the field name to change each time the Do While loops

 If this code loops 4 times it will have the same effect as below
                        count=4
                       Fieldname=1      
                        rst1![fieldname] = rstm![value]
                        rstm.MoveNext
                        rst1.Update
                        fieldname = fieldname + 1
         count =count-1      

The above code should do what is below
"if count=4":  rst1![1] = rstm![value]          
rstm.movenext

"if count=3": rst1![2] = rstm![value]
Rstm.movenext

"if count=2": rst1![3] = rstm![value]
Rstm.movenext

"if count=1": rst1![4] = rstm![value]
Rstm.movenext

Here is what the data will look like if both tables had the following data.

      Table2
Fieldname:         Value
Row1                 Samantha
Row2                 Tom
Row3                 George
Row4                 Jim

Table1: (End result after the code is done)

Column Name:               1             2             3               4
                Row1:        Samantha     Tom            George          Jim

In other words:

the first time the code loops: rst1![fieldname] = rst1![1]
the second time the code loops: rst1![fieldname] = rst1![2]
the third time the code loops: rst1![fieldname] = rst1![3]
\the fourth time the code loops: rst1![fieldname] = rst1![4]

"fieldname" is a variable, but I don't know the syntax to declare it as so in the following code

                       count=4
                       Fieldname=1      
                        rst1![fieldname] = rstm![value]
                        rstm.MoveNext
                        rst1.Update
                        fieldname = fieldname + 1
         count =count-1      

You probably would not have to change any part of the code except below:

rst1![fieldname]

Access is not declaring "fieldname" as a variable. It is actually looking for a column called "fieldname" in the table when "fieldname" does not exist. The table has columns labled "1", "2", "3", "4"

"fieldname" represents a number(which is a column/field name) in a table

Thanks Cap!!. One problem though.... I would like all of my data to appear in the first row. So I added rst1.movefirst into your code. Nonetheless it still enters data into a seperate record

i.e. This is what the table looks like: Each * is in a seperate record

column:     1        2       3     4       5
Row1:       *
Row2:                 *
Row3:                           *
Row4:                                 *
Row5:                                         *

This is what I am trying to get it to look like:
column1       2       3        4        5
Row1: *        *        *        *         *

Dim dbs As DAO.Database, rstm as dao.recordset, j as integer,rst1 as dao.recordset
Set dbs = CurrentDb
Set rst1 = dbs.openrecordset("table1")
Set rstm = dbs.openrecordset("table2")

rstm.movefirst

 j=0
    rst1.edit
       do until rstm.eof

       rst1.fields(j)=rstm(0)
 
        j=j+1
        rstm.movenext
       rst1.movefirst
loop
  rst1.update
   
End With

Nevermind I got it.
Thanks for the help!!!!