Array as a function parameter? 1

[rfoye]

Is it possible to use an array variable in a function that will return an array to the calling procedure?

My code pulls a list of recipients for a certain type of email to be sent (in this case, "Rush"), and creates the arrays for the 3 email address fields (To:, CC:, BCC
What I'd like to do is move the code that builds the arrays to a separate function. Can that be done, or am I stuck with this repetitive code?

Function fFillAddressArrays()
    Dim stSQL   As String
    Dim stSubj  As String
    Dim stTo(5) As String
    Dim stCC(5) As String
    Dim stBCC(5) As String
    Dim stNot   As String
    Dim lngTo   As Long
    Dim db      As DAO.Database
    Dim rs      As DAO.Recordset
    Dim i       As Integer
    
    Set db = CurrentDb
    stNot = "Rush"
    For lngTo = 1 To 3
        stSQL = "SELECT * FROM qryNotifications WHERE ntType = '" & _
              stNot & "' AND tnTO = " & lngTo & _
             " ORDER BY tnTo, urLotusNotesName;"
        Set rs = db.OpenRecordset(stSQL)
        With rs
            .MoveFirst
            i = 0
            Select Case lngTo
                Case 1
                    Do Until .EOF
                        stTo(i) = !urlotusnotesname
                        i = i + 1
                        .MoveNext
                    Loop
                Case 2
                    Do Until .EOF
                        stCC(i) = !urlotusnotesname
                        i = i + 1
                        .MoveNext
                    Loop
                Case 3
                    Do Until .EOF
                        stBCC(i) = !urlotusnotesname
                        i = i + 1
                        .MoveNext
                    Loop
            End Select
            .Close
        End With
    Next
    
End Function

-------------------
Rob Foye
Database Management
Regions Bank

 

[Golom]

You can do things like

Public Function LoadArray(rs As DAO.Recordset) As String()
Dim x(5) As String
Dim i As Integer
Do Until rs.EOF
   x(i) = rs![urlotusnotesname]
   i = i + 1
   If i > UBound(x) Then Exit Do
   rs.MoveNext
Loop
LoadArray = x
End Function

Then your code becomes

Function fFillAddressArrays()
    Dim stSQL   As String
    Dim stSubj  As String
    Dim stTo()  As String
    Dim stCC()  As String
    Dim stBCC() As String
    Dim stNot   As String
    Dim lngTo   As Long
    Dim db      As DAO.Database
    Dim rs      As DAO.Recordset
    
    Set db = CurrentDb
    stNot = "Rush"
    For lngTo = 1 To 3
        stSQL = "SELECT * FROM qryNotifications WHERE ntType = '" & _
              stNot & "' AND tnTO = " & lngTo & _
             " ORDER BY tnTo, urLotusNotesName;"
        Set rs = db.OpenRecordset(stSQL)
        Select Case lngTo
           Case 1: stTo  = LoadArray(rs)
           Case 2: stCC  = LoadArray(rs)
           Case 3: stBCC = LoadArray(rs)
        End Select
        rs.Close
    Next
    
End Function

 

[rfoye]

Thanks Golem!

I knew it had to be something similar to that, I just didn't think of using the recordset for the called function.

BTW - I further discovered that VBA allows assigning an array to an array variable ONLY IF the variables are declared as Variant.

-------------------
Rob Foye
Database Management
Regions Bank