Creating and looping through a record set.

[Bill]

I haven't done any coding for two years and didn't do much of it then but
here is my problem. I have looked though my old access books but am not
having any luck what so ever.

I have a table called "tblInvoice" with three fields "strInvNum",
"strGLAcct", and "dblAmount". The fields strGlAcct and dblAmount may contain
arrays. An example of the data looks like this.

strInvNum strGLAcct dblAmount
556 536~436 50.00~100.15
557 466 30.00
558 536~556~563 1.50~536.00~56.15

I need to then put the records into a table called tblInvoiceDetail as
follows:
strInvNum LineNum strGlAcct dblAmount
556 1 536 50.00
556 2 436 100.15
557 1 446 30.00
558 1 536 1.50
558 2 556 536.00
558 3 563 56.15

Any suggestions on how to do this? I did it years ago but no longer have the
database to copy the code from.

 

[pietlinden]

I haven't done any coding for two years and didn't do much of it then but
here is my problem. I have looked though my old access books but am not
having any luck what so ever.

I have a table called "tblInvoice" with three fields "strInvNum",
"strGLAcct", and "dblAmount". The fields strGlAcct and dblAmount may contain
arrays. An example of the data looks like this.

strInvNum   strGLAcct           dblAmount
556            536~436            50.00~100.15
557            466                   30.00
558            536~556~563    1.50~536.00~56.15

I need to then put the records into a table called tblInvoiceDetail as
follows:
strInvNum   LineNum   strGlAcct   dblAmount
556             1             536             50.00
556             2             436           100.15      
557             1             446             30.00
558             1             536               1.50
558             2             556            536.00    
558             3             563              56.15

Any suggestions on how to do this? I did it years ago but no longer have the
database to copy the code from.

Use SPLIT() to break the repeating values out. You'll need to do it
for both GIAccount and Amount. Since they should have the same number
of values, you could use a single counter variable to loop through
them.

dim varGIAccount as Variant, varAmount as Variant
'process outer loop here...
dim rsDest as dao.recordset
rsDest.OpenRecordset("DestTable",dbAppendOnly)
rsSrc.OpenRecordset("SrcTable",dbopenForwardOnly)
do until rsSrc.EOF
varGIAccount = Split(rsSrc.Fields("strGLAcct"),"~")
varAmount = Split(rsSrc.Fields("dblAmount"),"~")
for i = LBound(varGIAccount) to UBound(varGIAccount)
With rsDest
.addnew
.fields("InvNum") = rsSrc.Fields("strInvNum")
.fields("LineNum") = rsSrc.Fields("LineNum")
.Fields("GIAccount") = varGIAccount(i)
.Fields("Amount") = varAmount(i)
.Update
next i

rsSrc.close
rsDest.close

....totally untested, but that should get you started. Basically, you
split the two repeating fields into arrays, using Split() and then you
loop through the arrays, adding the values to the final table.