Dynamically Referencing Numbered Table Field Names

[Alan]

Hello,

I am working with a client that is receiving incoming data on two relational
tables who needs for me to clean it up and flatten it into a single table
with one customer per row. The resulting flat table is then uploaded into a
proprietary database.

That process involves loading the 'MANY' account data into an array and then
updating the mutiple fields of the 'ONE' customer table which are
differentiated by a digit portion in the corresponding field name. Right now,
the following code is working fine to load the array and assign values to the
fields, however I would like to reference the fields (following the '.Edit')
dynamically instead of explicitly.

The client is intimating that I could need to accomodate as many as thirty
accounts per customer rather than the current seven.

Your help is appreciated! Thanks!
Alan

Dim strSQL As String, strSQL1 As String, strSQL2 As String
Dim intNoAccounts As Integer, strCustNo As String
Dim varAcctData(8, 8) As Variant

'Grab ACCOUNT detail data from table
'(strX contains the dynamic Table Name)

strSQL1 = "SELECT [customerNumber] AS CUSTNO,
Format([advanceNumber],'000000000000') " & _
"AS ADVNUM, Left([advanceDate],10) AS ADVDATE, Left([advanceOverdueDate],10)
AS ADVODDATE, " & _
"[originalBalance] AS ORIGBAL, [currentBalance] AS CURRBAL, [lastPayDate] AS
LASTPAYDTE " & _
"FROM " & strX & " WHERE ([customerNumber] = '"

strSQL2 = "') ORDER BY [customerNumber],
Format([advanceNumber],'000000000000');"

Set rstNEWTABLE = db.OpenRecordset(strNewTableName, dbOpenTable)
With rstNEWTABLE
.MoveFirst
While Not rstNEWTABLE.EOF
strCustNo = !CUSTNO
Debug.Print "Processing CUSTNO: " & strCustNo
strSQL = strSQL1 & strCustNo & strSQL2
Set rstRaw = db.OpenRecordset(strSQL, dbOpenDynaset)
rstRaw.MoveFirst
varX = 1
While Not rstRaw.EOF
varAcctData(varX, 1) = rstRaw!ADVNUM
varAcctData(varX, 2) = rstRaw!ADVDATE
varAcctData(varX, 3) = rstRaw!ADVODDATE
varAcctData(varX, 4) = rstRaw!ORIGBAL
varAcctData(varX, 5) = rstRaw!CURRBAL
varAcctData(varX, 6) = rstRaw!LASTPAYDTE
varX = varX + 1
rstRaw.MoveNext
Wend
intNoAccounts = varX - 1
rstRaw.Close
.Edit
![NUMACCTS] = Str(intNoAccounts)
![ADVNUM-01] = varAcctData(1, 1)
![ADVDATE-01] = varAcctData(1, 2)
![ADVODDATE-01] = varAcctData(1, 3)
![ORIGBAL-01] = Val(varAcctData(1, 4))
![CURRBAL-01] = Val(varAcctData(1, 5))
![LASTPAYDTE-01] = varAcctData(1, 6)
![ADVNUM-02] = varAcctData(2, 1)
![ADVDATE-02] = varAcctData(2, 2)
![ADVODDATE-02] = varAcctData(2, 3)
![ORIGBAL-02] = Val(varAcctData(2, 4))
![CURRBAL-02] = Val(varAcctData(2, 5))
![LASTPAYDTE-02] = varAcctData(2, 6)
![ADVNUM-03] = varAcctData(3, 1)
![ADVDATE-03] = varAcctData(3, 2)
![ADVODDATE-03] = varAcctData(3, 3)
![ORIGBAL-03] = Val(varAcctData(3, 4))
![CURRBAL-03] = Val(varAcctData(3, 5))
![LASTPAYDTE-03] = varAcctData(3, 6)
![ADVNUM-04] = varAcctData(4, 1)
![ADVDATE-04] = varAcctData(4, 2)
![ADVODDATE-04] = varAcctData(4, 3)
![ORIGBAL-04] = Val(varAcctData(4, 4))
![CURRBAL-04] = Val(varAcctData(4, 5))
![LASTPAYDTE-04] = varAcctData(4, 6)
![ADVNUM-05] = varAcctData(5, 1)
![ADVDATE-05] = varAcctData(5, 2)
![ADVODDATE-05] = varAcctData(5, 3)
![ORIGBAL-05] = Val(varAcctData(5, 4))
![CURRBAL-05] = Val(varAcctData(5, 5))
![LASTPAYDTE-05] = varAcctData(5, 6)
![ADVNUM-06] = varAcctData(6, 1)
![ADVDATE-06] = varAcctData(6, 2)
![ADVODDATE-06] = varAcctData(6, 3)
![ORIGBAL-06] = Val(varAcctData(6, 4))
![CURRBAL-06] = Val(varAcctData(6, 5))
![LASTPAYDTE-06] = varAcctData(6, 6)
![ADVNUM-07] = varAcctData(7, 1)
![ADVDATE-07] = varAcctData(7, 2)
![ADVODDATE-07] = varAcctData(7, 3)
![ORIGBAL-07] = Val(varAcctData(7, 4))
![CURRBAL-07] = Val(varAcctData(7, 5))
![LASTPAYDTE-07] = varAcctData(7, 6)
.Update
.Bookmark = .LastModified
'Clear array values
For varX = 0 To 7
For varY = 0 To 7
varAcctData(varX, varY) = ""
Next varY
Next varX
'Move to next Record in NEWTABLE
.MoveNext
Wend
End With
rstNEWTABLE.Close