CODEWITHSUNDEEP
ms-accessvba
user2211590
user2211590

Reputation: 1

Looping through an array and posting array to table

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)

  1. How can I reference my result table output fields as an array?

  2. 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

Upvotes: 0

Views: 1492

Answers (2)

Tomalak
Tomalak

Reputation: 338248

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.

Upvotes: 2

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

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.

Upvotes: 2

Related Questions