Concatenate Rows into one row

[rkalapura]

I am using the following code (function "EmailGps()") on the record source property of an unbound field to get the email addresses in access 2010. Email address are listing by the query "QrySupvTLPDue". This query fetch me all people I need to send the email automatically. This code makes all email addresses in one line separated by semicolon. This part is working fine and has no problem for the first report.

Public Function EmailGps()
Dim Action As DAO.Database
Dim Rs As DAO.Recordset
Dim eAdr As String
Set Rs = CurrentDb.OpenRecordset("QrySupvTLPDue", dbOpenForwardOnly)
With Rs
If Not Rs.EOF And Not Rs.BOF Then
Do Until Rs.EOF
If Rs![EmailAd] <> "" Then
eAdr = eAdr & Rs!EmailAd & ";"
End If
Rs.MoveNext


Loop
End If
End With
Rs.Close
Set Rs = Nothing
eAdr = left(eAdr, Len(eAdr) - 1)
EmailGps = eAdr
End Function

Now I have a second report which I need to email the same way. So I have a second query which fetch the list of email address of people involved. I made a second function called Egroup() with same code and only changed the name of the query from "QrySupvTLPDue" to "QryEGroup". When I assign this function to an unbound field, it gives error. Both queries have only one filed for email address and sorted ascending. I tried with many other queries with one field, none of them working other than the first.

The error I am getting is "Runtime error 3061". Too few parameters. Expected 1.

I am not an expert in changing code but can understand. Can anybody tell what I am missing here for using the same code more than one time in a database?

Appreciate your help.

[ranman256]

parameters expect means you either have a parameter inside 1 of the queries that is not answered, or a field is misspelled and the query THINKS its a param.

Instead of cycling thru collecting ALL emails to send once, I scan each email once and send X number of emails. But either way works I guess until you hit the text limit of the SENDTO line.

[orange]

Also, you should wrap any sample code in "code tags".
Highlight the vba code involved then click on the # sign in the header of the response window.

I suggest you look for the free utility called SmartIndenter which will help you format your code for improved readability.

After SmartIndenter

Public Function EmailGps()
    Dim Action As DAO.Database
    Dim Rs As DAO.Recordset
    Dim eAdr As String
    Set Rs = CurrentDb.OpenRecordset("QrySupvTLPDue", dbOpenForwardOnly)
    With Rs
        If Not Rs.EOF And Not Rs.BOF Then
            Do Until Rs.EOF
                If Rs![EmailAd] <> "" Then
                    eAdr = eAdr & Rs!EmailAd & ";"
                End If
                Rs.MoveNext
            Loop
        End If
    End With
    Rs.Close
    Set Rs = Nothing
    eAdr = Left(eAdr, Len(eAdr) - 1)
    EmailGps = eAdr
End Function

[ssanfu]

"QryEGroup" opens without error?

All of the fields in queries "QrySupvTLPDue" and "QryEGroup" are the same?

In the function Egroup(), did you change the 2nd from the last line to "Egroup = eAdr"?

You don't need the lines

Dim Action As DAO.Database  (not used)

With Rs    (you are explicitly using "Rs" for the recordset variables)
End With

-----------------------------
If the queries have the same fields, you could use one function by turning it into a parameter function:

Public Function EmailGps(pQryName As String) As String

    Dim Rs As DAO.Recordset
    Dim eAdr As String
    
    Set Rs = CurrentDb.OpenRecordset(pQryName, dbOpenForwardOnly)

    If Not Rs.EOF And Not Rs.BOF Then
        Do Until Rs.EOF
            If Rs![EmailAd] <> "" Then
                eAdr = eAdr & Rs!EmailAd & ";"
            End If
            Rs.MoveNext
        Loop
    End If

    Rs.Close
    Set Rs = Nothing
    eAdr = Left(eAdr, Len(eAdr) - 1)
    EmailGps = eAdr
End Function

Usage would be:

SomeVariable = EmailGps("QrySupvTLPDue")

AnotherVariable = EmailGps("QryEGroup")

[rkalapura]

Thank you for everybody who responded and assisted for solving my problem. I have sorted this out using some Macro sequence actions. Now I found out a way to use this function any number of time in the database. Following is the Macro sequence I used:

1. Instead of using query in the code, I used a temporary table of one filed (for email address). I changed the previous queries to "append query" which will append rows to the temporary table used in code.
2. Each time after running the code and using the code result, I delete all records from the table with a delete query. So next time when the code run, it append records to the empty table. The code result is stored in unbound filed in a form. So every time I open the form, I get a fresh email address list which I need.

I have almost 12 different reports in my database which emailed to different recipient. When I run a report in different time, the number and address of recipient are different. It is very difficult to enter the email address manually each time looking in to the report. Now all of them are working fine with my Macro function.

Thank you very much everybody who sent valuable thoughts for solving my issue. God Bless all.

"QryEGroup" opens without error?

All of the fields in queries "QrySupvTLPDue" and "QryEGroup" are the same?

In the function Egroup(), did you change the 2nd from the last line to "Egroup = eAdr"?

You don't need the lines

Dim Action As DAO.Database  (not used)

With Rs    (you are explicitly using "Rs" for the recordset variables)
End With

-----------------------------
If the queries have the same fields, you could use one function by turning it into a parameter function:

Public Function EmailGps(pQryName As String) As String

    Dim Rs As DAO.Recordset
    Dim eAdr As String
    
    Set Rs = CurrentDb.OpenRecordset(pQryName, dbOpenForwardOnly)

    If Not Rs.EOF And Not Rs.BOF Then
        Do Until Rs.EOF
            If Rs![EmailAd] <> "" Then
                eAdr = eAdr & Rs!EmailAd & ";"
            End If
            Rs.MoveNext
        Loop
    End If

    Rs.Close
    Set Rs = Nothing
    eAdr = Left(eAdr, Len(eAdr) - 1)
    EmailGps = eAdr
End Function

Usage would be:

SomeVariable = EmailGps("QrySupvTLPDue")

AnotherVariable = EmailGps("QryEGroup")