How to store recordset names in array?

[Steve Sothern]

what is wrong with this code?

Dim initials As String, e_SIG As String, queryFUP As String
Dim rstFUP(4) As Recordset

Set rstMSG = CurrentDb.OpenRecordset("defaultemailmsg")

initials = "AM"
e_SIG = vbCrLf & _
        vbCrLf & _
        vbCrLf & _
        vbCrLf & _
        "Kind regards," & vbCrLf & _
        vbCrLf & _
        "Awhi McLachlan" & vbCrLf & _
        "AIMS Supporter Services/N.E.S.T." & vbCrLf & _
        "Ph (09) 4376694"

For A = 1 To 4
queryFUP = "followup_email" & A
Set rstFUP(A) = CurrentDb.OpenRecordset(queryFUP)
With rstFUP(A)
    e_SUB = "Follow up on invitation to join Northland Electricity Emergency Helicopters Supporter Family"
    .MoveFirst
    Do Until .EOF
        e_ADDY = !email
        f_TYPE = (10 * !email_followup_type) + ![1stemailfeasturesize]
        Select Case ![1stemailfeaturesize]

etc etc

the code errors at the set rstFUP(A) = ... line
it tells me "runtime error 3061, too few parameters. Expected 1"

why? can I not use an array in this way?

[pod]

Hi

difficult to evaluate based on the displayed code only ...

my suggestion is display in a message box (msgbox) your sql statement (queryFUP) before excuting it ... just to make certain it is a recognizable sql statement

[TheSmileyCoder]

The code shows that queryfup is:

queryFUP = "followup_email" & A

However that will only work assuming that there is a query or a table called followup_email1 (or 2,3,4 as in example)

[Steve Sothern]

thanks for you reply. There are queries named "followup_email 1", "...2",etc and they all execute properly when launched on their own.

If I replace the for next loop with and the rstFUP array with four individual variables and four separate subroutines for each query, the code works fine. Leads me to believe their is some issue with using an array to identify a recordset.

[TheSmileyCoder]

I honestly don't know whether or not its possible to make an array of recordsets.

Other options could include:

1. Reuse the same variable:
   
   

   For A=1 to 4
     queryFUP = "followup_email" & A
     Set rstFUP = CurrentDb.OpenRecordset(queryFUP)
     'etc...

   You would basicly just be updating its value each time.
   
2. Use a collection instead of an array.
   
3. Move the handling of the recordset to a seperate procedure that then gets called 4 times.

[Steve Sothern]

Thanks Smiley, I like your option 3, great thinking. I will try to avoid arrayed recordsets in the future.

[ADezii]

@TheSmileyCoder - I wasn't actually sure myself, until now, but you can create an Array of Recordsets as in:

Dim MyDB As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset

Set MyDB = CurrentDb

Set rst1 = MyDB.OpenRecordset("SELECT * FROM [Order Details] WHERE [Quantity] = 1;", dbOpenSnapshot)
Set rst2 = MyDB.OpenRecordset("SELECT * FROM [Order Details] WHERE [Quantity] = 10;", dbOpenSnapshot)
Set rst3 = MyDB.OpenRecordset("SELECT * FROM [Order Details] WHERE [Quantity] = 100;", dbOpenSnapshot)

Dim aRecordsets(1 To 3) As DAO.Recordset

Set aRecordsets(1) = rst1
Set aRecordsets(2) = rst2
Set aRecordsets(3) = rst3

Debug.Print "Order ID", "Unit Price", "Quantity"
Debug.Print "-------------------------------------------------"

With aRecordsets(3)
  Do While Not .EOF
    Debug.Print ![OrderID], Format$(![UnitPrice], "Currency"), ![Quantity]
      .MoveNext
  Loop
End With
Debug.Print "-------------------------------------------------"

aRecordsets(3).Close
Set aRecordsets(3) = Nothing

OUTPUT (based on [Order details] Table of Northwind Sample Database):

Order ID      Unit Price    Quantity
-------------------------------------------------
 10286        $14.40         100 
 10452        $15.50         100 
 10549        $9.50          100 
 10588        $14.00         100 
 10607        $39.00         100 
 10678        $38.00         100 
 10854        $31.00         100 
 10895        $34.00         100 
 11030        $19.00         100 
 11030        $55.00         100 
-------------------------------------------------