"Steve" <(E-Mail Removed)> wrote in message
news:580244BA-0987-450A-B9A7-(E-Mail Removed)...
> Greetings:
>
> I am attempting to use Duane Hookom's concatenate function (reproduced
> below) to concatenate the results of a query named "qryMapMethod". When I
> type:
>
> Concatenate("qryMapMethod") into the immediate window I get the following
> error:
>
> "Runtime Error 3061. Too few parameters, expected 1" when it attempts to
> execute "Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)" line of code.
>
> Any help in what I am doing wrong would be greatly appreciated. Thanks.
>
> Steve
>
> 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
>
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
>
> Set db = CurrentDb
> Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)
>
> 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
> --
Pardon me, but I think there may be some confusion here. Duane's
Concatenate function works just fine if you pass it the name of a stored
query, so long as that query has no parameters. My guess is that
qryMapMethod has a parameter, such as (maybe) a reference to a control on a
form. Unlike what Access does when you open a query using DoCmd.OpenQuery,
DAO doesn't automatically resolve parameters.
If this is the reason for the error you're getting, you could modify Duane's
function to get Access to resolve the parameters for you, like this:
'------ start of modified 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
'
' Modified by Dirk Goldgar, 15 September 2009, to resolve
' parameter references in stored queries.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Set db = CurrentDb
If pstrSQL Like "SELECT *" Then
Set rs = db.OpenRecordset(pstrSQL, dbOpenDynaset)
Else
Set qdf = db.QueryDefs(pstrSQL)
With qdf
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = .OpenRecordset(dbOpenDynaset)
End With
End If
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
Set qdf = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
'------ end of modified code ------
--
Dirk Goldgar, MS Access MVP
Access tips:
www.datagnostics.com/tips.html
(please reply to the newsgroup)
Similar Threads
