Query parameters generating err 3061 with OpenRecordset

[Chris Morris]

I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.

This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:

Dim rst As DAO.Recordset
Dim stTo As String 'one of the function's arguments received from the calling function.
Dim stToString As String 'the built up concatenated emails

Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)
stTo = ""
With rst
.MoveFirst
Do While Not .EOF
stToString = stToString & rst!Email & ";"
'Next
.MoveNext
Loop
End With

 

[CJ_London]

You are trying to open a query or table with a zero length string because you have declared it here

Dim stTo As String 'one of the function's arguments received from the calling function.

If it is supposed to be an argument to the function then it should not be declared here

 

[Chris Morris]

Sorry - my mistake, I was including that (supposedly) for clarity, instead the code sits without that:

Public Function Send_Email(stTo As String, stSubject As String, stBody As String, _
Optional stCC As String = "", Optional stBCC As String = "")

Dim rst As DAO.Recordset
Dim stToString As String 'the built up concatenated emails

'Build To, CC and BCC fields.
Set rst = CurrentDb.OpenRecordset(stTo)

With rst
.MoveFirst
Do While Not .EOF
stToString = stToString & rst!Email & ";"
'Next
.MoveNext
Loop
End With
End If

 

[CJ_London]

The problem is almost certainly that there is an error in your query - for example, is your form open to provide the criteria?

 

[Chris Morris]

It is indeed open. Regardless of the code I've ended up with, is there any alternative to this? My main requirement is to be able to loop through email addresses from either a static or dynamically filtered query to be able to build up a complete "To" list. Am I approaching this from the wrong angle I wonder?

 

[spikepl]

Look at Similar threads at the bottom, or google errror 3061. Your predicament is fairly common: the expression service, which interpretes Forms!blabla does not run with OPenRecordset. Either you have to contruct the query SQL on the fly, and concatenate the value for your parameter into it, or you have to use a QueryDef objct and parameters

 

[CJ_London]

Without knowing a lot more I cannot say whether this sis the best way or there is a better way. I repeat, the error is caused because there is something wrong with your query - check spelling.

An alternative is to build your SQL within your function, passing through the fields you want

e.g.

Function GetTos(FldName as string, TblName as String) as String
Dim Rst as Recordset
 
Set Rst=currentdb.openrecordset ("SELECT " & FldName & " FROM " & tblName)
GetTos=""
While not rst.EOF
    GetTos=GetTos & rst.fields(0) & ";"
Wend
End Function