Extract variable count of columns (1 Viewer)

Ben_Entrew

Registered User.
Local time
Today, 08:21
Joined
Dec 3, 2013
Messages
177
Hi all,
I wanna extract a variable count of columns and transpose it to another table.

My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities

Tried the following:
Code:
Public Sub TRANSPOSE()
    Dim rs As DAO.Recordset
    Dim rsNew As DAO.Recordset
    Dim varItm As Variant
    Dim I As Integer
    Set rs = CurrentDb.OpenRecordset("FC")
    Set rsNew = CurrentDb.OpenRecordset("Forecast_Quantities")

    Do Until rs.EOF

        For I = 1 To (12 - actuals)
         
                rsNew.AddNew
                rsNew("Time_Period") = rs("Time_period")
                rsNew("ID") = rs("ID")
                rsNew("Product_Class") = rs("MRP_Description")
                rsNew("Division") = rs("Division")
                rsNew("FC") = rs.Fields("" & I & " FC")
                rsNew.Update
      
            Next I
   
        rs.MoveNext
    Loop


Somehow it doesn't recognize the I FC column in the table FC.
Does anyone have a idea ?
Thanks in advance.

Regards,
Ben
 

Insane_ai

Not Really an A.I.
Local time
Today, 11:21
Joined
Mar 20, 2009
Messages
264
Try:

Option Base 1

Or

For I = 0 to (12-actuals)

By default, VBA starts counting at zero.
 

Users who are viewing this thread

Top Bottom