Macro in Access

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

Guest

Hi,

I was trying to take a column in table 1, where table 1 =

Scenario, Time, Rate

and change the "Rate" column. I know the logic I want to perform on the
array but I just need the proper syntax. How do I assign the "Rate" column
from table 1 to the "ArrayRates", and How do I take the "ArrayIndex" column
and put it in a new table or just update the old table 1?

Thanks for your help.

Here is my code (so far):

Option Compare Database
Option Explicit

Dim ArrayRates(375000) As Double
Dim ArrayIndex(375000) As Double

For i = 1 To 360000
ArrayRates(i) = The "Rate" column from Table 1
Next i

ArrayIndex(1) = 1270

For i = 1 To 360000
ArrayIndex(i + 1) = ArrayIndex(i) * (ArrayRates(i) / 100 + 1)
Next i

For i = 1 To 360000
tbleNew.Table.Column2.Row(i) = ArrayIndex(i) 'Want to put the "ArrayIndex"
into a new column
Next i
 
To iterate records in a table, you need to use a Recordset object. Something
like:
Dim rst As New Dao.Recordset
Set rst = CurrentDb.OpenRecordset("MyTableOrQueryName", dbOpenForwardOnly)
Do While Not rst.Eof
Debug.Print rst.Field("MyFieldName")
Loop
rst.Close
Set rst = Nothing

I think you'll find that using recordsets, you don't need to fill arrays.
You could open a recordset for each table, loop records in the source table,
and append records to the destination table with whatever modification you
need.

Barry
 
Back
Top