I wrote a function that is supposed to concatinate all the email addresses contained in a query into one string. The code is as follows:

Function CompileAddresses(ByVal query As String) As String


    Dim rs As ADODB.Recordset
    Dim strEmail As String

    Set rs = New ADODB.Recordset
    strEmail = ""

    rs.Open query, CurrentProject.Connection

    Do While rs.EOF = False
        If rs.Fields("Email") <> "" Then
            strEmail = strEmail & rs.Fields("Email") & ";"
        End If
        rs.MoveNext
    Loop

    CompileAddresses = strEmail

End Function

query is an SQL statement that is passed to the function.

When i try to run this code it gives the following error:

Run-time error '-2147217904(80040e10)':
No value given for one or more required parameters.

This error references this line:
rs.Open query, CurrentProject.Connection

I cant for the life of me figure out what im doing wrong. if anyone has any ideas, they would be greatly appreciated.

That message generally means that one of the fields in your SQL statement is incorrect or doesn't match the name in the table -- either way the issue is with the SQL statement. If you post that maybe we could resolve this for you.

Kenny Alligood

The 2 SQL statements that can be passed to this function at the moment are:

SELECT People.Email FROM (Sites INNER JOIN (People INNER JOIN TeamMembership ON People.PID=TeamMembership.PID) ON Sites.ID=TeamMembership.TID) INNER JOIN (Ladders INNER JOIN LadderMembership ON Ladders.LID=LadderMembership.LID) ON Sites.ID=LadderMembership.TID WHERE Ladders.LID LIKE Forms!EmailTeams!cboLadder.Value;

SELECT People.Email FROM Sites INNER JOIN (People INNER JOIN TeamMembership ON People.PID=TeamMembership.PID) ON Sites.ID=TeamMembership.TID WHERE TeamMembership.TID LIKE Forms!EmailTeams!cboTeam.Value;

cboLadder.Value and cboTeam.Value return the primary key value of record that is selected from the the Ladder combo box and the Team combo box respectively. They have different SQL statement to populate themselves and both work fine.

ok, i figured it out. I need to enclose the variable after LIKE with % and quotation marks.

Does anyone know why this is the case even though i dont need to do that when i populate a combo box with using an SQL statement that has a variable?

My problem is now getting the text out of the field. Is my syntax correct? if not, what is the correct syntax?

I find the syntax requirements for paramterized queries bewildering. Is there a resource of examples anywhere that shows the syntax in various situations? thanks,
Rick Neifeld, Ph.D. Patent Attorney
President, Neifeld IP Law, PC and StockPricePredictor.com, LLC
2001 Jefferson Davis Highway, Suite 1001
Arlington, VA 22202
Tel: 703-415-0012
Fax: 703-415-0013
Email: [email protected]
www.Neifeld.com and www.PatentValuePredictor.com

yeah, something like that would be very useful.

I did figure out my problem though. I needed to include rs.MoveFirst right after the rs.Open statement. I didnt think this was needed because i thought that the recordset defaults to the first record. Can anyone provide any insite on this?

It seems i have not actually solved the problem. the MoveFirst method doesnt do anything for me. Ive narrowed the problem down to the LIKE statement. If i put in an actual value, like 1 or 2 after LIKE, everything works fine and function returns the concatinated email string. If i put in '%Forms!EmailTeams!cboLadder.Value%' after LIKE, the function returns a blank email string.

Does anyone have any idea what im doing wrong here? Thanks in advance for your help.