Looping through an array and posting array to table

Question

I am an old Foxpro programmer and I use to use arrays to post variable fields.

What I am trying to do is I have 15 date fields in the new table I designed.
In my query I have individual records with one date for activity.

I want to compile the 15 different dates for a each Client_id into one record with 15 dates but I can't seem to reference the table data as an array.
I have tried a couple different methods of defining the array but nothing seems to work.

Here is my code that I have. In my table I have 15 date fields named Mail_date1, Mail_date2, Mail_date3, etc.
I tried first defining it just as an array but did not like it; my code always fails when I try to reference the date field in the result table rs2!mdate2 = memdate(intcounter)

How can I reference my result table output fields as an array?
Do I have to put a whole bunch of if statements to load my results?
Seems like a waste.... should be able to load them as an array.

I am a new Access 2007 VBA programmer.

Dim db As DAO.Database
Set db = CurrentDb
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim FinTotal, intcounter As Integer
Dim FinMPU, FinVersion As String
Dim mail_date(1 To 15) As Date
Dim memdate(1 To 15) As Date
Dim mdate2 As String

Set rs1 = db.OpenRecordset( _
            "SELECT NewFile.MPU_ID,   " & _
            "       NewFile.MAIL_DATE," & _
            "       NewFile.TOTAL,    " & _ 
            "       Freight.Version   " &_
            "FROM Freight " & _
            "  LEFT JOIN NewFile ON Freight.[MPU ID] = NewFile.MPU_ID " & _
            "ORDER BY NewFile.MPU_ID, NewFile.MAIL_DATE")

Set rs2 = db.OpenRecordset("Final")

DoCmd.RunSQL "DELETE Final.* FROM Final;"

intcounter = 1
memdate(intcounter) = rs1!mail_date
FinMPU = rs1!mpu_ID
FinTotal = rs1!total
FinVersion = rs1!Version
rs1.MoveNext

On Error GoTo Error_MayCauseAnError

Do While Not rs1.EOF
    Do While Not rs1.EOF _
             And memdate(intcounter) <> rs1!mail_date _
             And FinMPU = rs1!mpu_ID
        intcounter = intcounter + 1
        memdate(intcounter) = rs1!mail_date
        FinTotal = FinTotal + rs1!total
        FinVersion = rs1!Version
        FinMPU = rs1!mpu_ID
        rs1.MoveNext
    Loop

    If FinMPU <> rs1!mpu_ID Then
        rs2.AddNew
        mdate2 = "mail_date" & CStr(intcounter)
        rs2!mdate2 = memdate(intcounter)
        rs2!total = FinTotal
        rs2!mpu_ID = FinMPU
        rs2!Version = FinVersion
        rs2.Update

        FinTotal = rs1!total
        FinVersion = rs1!Version
        FinMPU = rs1!mpu_ID
        intcounter = 1
        memdate(intcounter) = rs1!mail_date
    End If
    rs1.MoveNext
Loop

@Tomalak Umm, no. For native Access applications using the ACE database engine, the "Microsoft Office ... Access Database Engine Object Library" (which maps to `DAO` in VBA) is still the default and preferred access method. — Gord Thompson, Mar 31 '13 at 12:00
@Gord IIRC they have switched to ADO as the default library as of Access 2007 (?). — Tomalak, Mar 31 '13 at 12:06
@Tomalak `DAO` is still the default and native way to handle data in Access 2007/2010/2013 as it has been updated to support the enhancement of the new database format. You are completely free to us `ADO` of course; there are some things you can do in one that cannot be done in the other and vice-versa. See also http://stackoverflow.com/questions/1039224/is-it-better-to-use-ado-or-dao-in-access-2007 — Renaud Bompuis, Mar 31 '13 at 12:15
@Tomalak Back around Access_2000 (or maybe Access_97) Microsoft replaced DAO with ADO as the default reference for new VBA projects (to push people toward using SQL Server backends). This was not popular with the Access developer community, and in Access_2003 Microsoft put the default DAO reference back in and gave it precedence over ADO. Then, in Access_2007 (IIRC) Microsoft dropped the default ADO reference and (the "new, improved") "DAO" was once again the sole default reference for data access. — Gord Thompson, Mar 31 '13 at 12:34
@GordThompson I see. This is the bit I was missing. I was pushed into SQL Server. ;-) — Tomalak, Mar 31 '13 at 12:50

score 2 · Answer 1 · answered Mar 31 '13 at 12:00

first, if you expect and answer, you should really spend more time on properly formatting your explanation and your code...

Now, for some remarks and possible answer to the question:

You should DELETE FROM Final before you open that table in a recordset.

You should be explicit about the type of recordset you are opening:

' Open as Read-only '
Set rs1 = db.OpenRecordSet("...", dbOpenSnapshot)
' Open as Read/Write '
Set rs1 = db.OpenRecordSet("...", dbOpenDynaset)

You should Dim memdate(1 To 15) As Variant instead of Date as the Date datatype cannot be Null, and since you are pulling data from a LEFT JOIN, it's possible that the returned values could be Null if there are no corresponding data to Freight in the table Newfile.
That On Error GoTo Error_MayCauseAnError should probably not be there.
Use On Error Goto only to catch errors you can't deal with at all.
Using that here will only hide errors in your code. With some proper checks statements you should not even need the On Error Goto...
It looks like your first internal loop is trying to skip some records.
However, when that loop breaks, it could be because it reached EOF, and you never test for that in the code that follows the loop.
You never test if your intcounter goes beyond the 15 allocated dates.
Are you absolutely sure that you can never have more than 15 records?
You do not say which error message you get exactly. That could be useful to help determine the kind of issue at hand.

You should also include that `Dim FinTotal, intcounter As Integer` would declare `FinTotal` a variant. Assuming that it would be `Integer` as well is a common mistake. — Tomalak, Mar 31 '13 at 12:03

score 2 · Answer 2 · answered Mar 31 '13 at 12:02

2

Instead of

mdate2 = "mail_date" & CStr(intcounter)
rs2!mdate2 = memdate(intcounter)

Use

rs2.Fields("mail_date" & intcounter).Value = memdate(intcounter)

the ! syntax of DAO really only is a shorthand for the longer rs.Fields("name") form.

answered Mar 31 '13 at 12:02

Tomalak

332,285
67
532
628

Of course the explicit use of `.Value` still is completely optional. – Tomalak Mar 31 '13 at 12:24

Looping through an array and posting array to table

2 Answers2

Linked

Related