Concatenate help

lryckman · October 9th, 2009, 04:28 PM

Hello!
I have the below code that I am getting an error
"Run-time error '-2147217904 (80040e10)':
No value given for one ore more required parameters."

I use this same code in another database, and it works fine, so it really has me stumped.
Any help would be much appreciated!
Thanks!

The part of the code that becomes highlighted when trying to debug is:

Code:

    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic

Here is the whole function that is resides in...

Code:

 
Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = "; ") _
        As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
'   this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
        Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
End Function

Old Pedant · October 9th, 2009, 07:34 PM

You will have to debug and find out just what the value of pstrSQL is.

The error has nothing to do with concatenation, per se.

You are simply passing in a bad SQL query.

Old Pedant · October 9th, 2009, 07:43 PM

But the code there is pretty crappy.

If you *are* using ADO (as opposed to DAO, and it appears you are) then it can all be done in about a quarter as much code.

Actually, an even better thing would be to not use this function at all and do it up one level.

But...

Code:

Function Concatenate(pstrSQL As String, _
        Optional pstrDelim As String = "; ") _
        As String
    Dim rs As ADODB.Recordset
    Set rs = CurrentProject.Connection.Execute( pstrSQL )
    Dim list As String
    list = rs.GetString( , , , pstrDelim )
    rs.Close
    Set rs = Nothing
    Concatenate = Left( list, Len(list) - Len(pstrDelim) )
End Function

NOTE: It will *only* work if your SQL selects *ONLY* one field!

Such as:

SELECT name FROM table WHERE ....
or
SELECT email FROM table WHERE ...

lryckman · October 12th, 2009, 08:52 AM

Quote:

Originally Posted by Old Pedant

You will have to debug and find out just what the value of pstrSQL is.

The error has nothing to do with concatenation, per se.

You are simply passing in a bad SQL query.

lol! I went back and looked after reading your reply and sure enough, I was missing quotes in my select statement of the query using the Concatenate function!

Thanks!!!