• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Access:VB:renaming fieldname using variable

I have the following loop. What is the proper syntax to make it work?

The variable "field"  is the trouble area.  i.e. each time it loops through the field name should change
i.e. loop1:rst1![1]          loop2:  rst1[2]                 loop3: rst1[3]

Do
                  Do
                        field = 1
                                               
                        rst1!field = rstm![value]<-----------------------------------------This is trouble area!!!
                        rstm.MoveNext
                        rst1.Update
                       
                        field = field + 1
                        Counter = Counter - 1
                    Loop While (Counter > 0)
               
                    countdistinct = countdistinct - 1
                Loop While (countdistinct > 0)
0
ouestque
Asked:
ouestque
1 Solution
 
SStoryCommented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
ouestque,

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

give information in details....
0
 
jefftwilleyCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ouestqueAuthor Commented:
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)
0
 
ouestqueAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
change this

Set rstm = dbs.openrecordset(table2)

to

Set rstm = dbs.openrecordset("table2")
0
 
ouestqueAuthor Commented:
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&']
0
 
Rey Obrero (Capricorn1)Commented:
ouestque,
so, you want to change the names of the fields in table1?
0
 
ouestqueAuthor Commented:
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)

0
 
Rey Obrero (Capricorn1)Commented:
ouestque,

what are trying to do?????
0
 
ouestqueAuthor Commented:
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



0
 
ouestqueAuthor Commented:
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      
0
 
ouestqueAuthor Commented:
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
0
 
Rey Obrero (Capricorn1)Commented:
try this

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
      loop
  rst1.update
   
End With
0
 
ouestqueAuthor Commented:
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
0
 
ouestqueAuthor Commented:
Nevermind I got it.
Thanks for the help!!!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now