Field Names in Recordset

[ricwat]

I have a table that contains 12 fields in the format of playerXXCode (where XX is a value from 1 to 12).

I want to loop through a recordset and populate a temporary table with the player codes. (Transpose the data)

The problem is I want to do it like this:

Note:
rsOld = Original Table
rsNew = Transoposed Table

Counter = 0
do until counter >12
rsNew.addnew
rsNew![PlayerCode] = rsOld![Player & Counter & Code]
' Should be Player1Code, Player2Code, etc
rsNew.update
counter = counter+1
loop

The thing is Access doesn't recognise the Player & Counter & Code] as a field name.

I have done it before, but I can't remember how!!!

Your help in this matter is appreciated.

 

[rookery]

Hi

I have achieved something similar to this by using the following code:

Set dbs=CurrentDb
dbs.Execute ("SELECT * INTO [tblNew] FROM [tblOld]&quot

However this doesnt just capture the field names - you will transpose all of the data within the table as well. Is this what you want to do?

NSS

 

[danvlas]

rsNew("PlayerCode&quot = rsOld(Player & Counter & Code)

would do the trick...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com