Rolling headers (13 months) how to update records
I have been struggling with ADODB to solve this:
1. I create a table with solid row data and headers.
2. I update the last 13 column headers with rolling months as below.
Dim tbl, newCol, adox, n
Set myConn = CurrentProject.Connection
tbl = "MyTable"
Set adox = CreateObject("ADOX.Catalog")
adox.ActiveConnection = myConn
For n = 0 To 12
newCol = Year(DateAdd("m", n, (Now()))) & "-" & Right(0 & Month(DateAdd("m", n, Now())), 2)
adox.Tables(tbl).Columns("M+" & n).Name = newCol
Set adox = Nothing
Set myConn = Nothing
3. Until now I'm fine, but then:
- I'm picking values from another table along with month number and need to place this data by matching month into "MyTable".
The problem is how to save the source data (myValue) to "MyTable" to correct columns? myData connects nicely to "MyTable" but I cannot use it like this (get error msg):
Do Until myData.EOF
from 0 to 12
myData!rollCol = myValue/13 <== This fails!!
(here I set next month to rollCol)
rollCol points to actual month to actual+12 months
e.g. 2008-05, 2008-06, 2008-07 .....2009-05
I have not set any specific Dimension for rollCol, should I?
Thanks in advance