Using a Variable Column Name in a recordSet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys.

I am trying to write a VB script that will go through several fields and
columns and update the appropriate information. I have columns like building
name, dollar amount, beginning year, end year, and also columns for years
1960-2005. Essentially, I am tracking the value of an object throughout its
life time.

I am having difficulty trying to use a variable as the column name... Here
is what I have so far..

Dim currConnection As New ADODB.Connection
Dim recordSet As New ADODB.recordSet
Dim currDB As Database

Set currDB = CurrentDb
Set currConnection = CurrentProject.Connection
Set recordSet = New ADODB.recordSet

recordSet.Open "SELECT * FROM test_OUR_UNIVERSE", currConnection,
adOpenDynamic, adLockOptimistic

For i = 1960 To 2005

Do While Not recordSet.EOF
recordSet![" & i & "] = i <--- Here is what I am having difficulty
with
recordSet.MoveNext
Loop
recordSet.MoveFirst

Next
recordSet.Close

-------

I want to use:
recordSet![" & i & "]
so that it will update the appriopriate column when the loop is there...

How do I use a variable in this fashion?

I really appreciate any assistance you could provide.

Thanks.

-Michael
 
Great! Thanks!

JohnV said:
Hello Michael,

Try using:

recordset(i) -- this will reference the field specified.

JohnV


Michael said:
Hi guys.

I am trying to write a VB script that will go through several fields and
columns and update the appropriate information. I have columns like building
name, dollar amount, beginning year, end year, and also columns for years
1960-2005. Essentially, I am tracking the value of an object throughout its
life time.

I am having difficulty trying to use a variable as the column name... Here
is what I have so far..

Dim currConnection As New ADODB.Connection
Dim recordSet As New ADODB.recordSet
Dim currDB As Database

Set currDB = CurrentDb
Set currConnection = CurrentProject.Connection
Set recordSet = New ADODB.recordSet

recordSet.Open "SELECT * FROM test_OUR_UNIVERSE", currConnection,
adOpenDynamic, adLockOptimistic

For i = 1960 To 2005

Do While Not recordSet.EOF
recordSet![" & i & "] = i <--- Here is what I am having difficulty
with
recordSet.MoveNext
Loop
recordSet.MoveFirst

Next
recordSet.Close

-------

I want to use:
recordSet![" & i & "]
so that it will update the appriopriate column when the loop is there...

How do I use a variable in this fashion?

I really appreciate any assistance you could provide.

Thanks.

-Michael
Click to expand...
Click to expand...
 
Back
Top