Pass and loop though a query in a function 1

[HomeGrowth]

The function below works fine…

Function LoopTheList()

Dim strContactName As String, strContactList As String
Const strQueryName = "qry(2 )PA_7DayAlert_List"

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb ' Open pointer to current database
Set rs = db.OpenRecordset(strQueryName) ' Open recordset on saved query

rs.MoveFirst
Do Until rs.EOF
strContactName = Nz(rs![Responsible contact], "")
strContactList = strContactList & ";" & strContactName
rs.MoveNext
Loop
MsgBox Mid(strContactList, 2)

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

Now I would like to call this function from anywhere and provide a different Query name each time for it to loop through. The call function is

Call LoopTheList (“qry(2 )PA_7DayAlert_List “)

This returns error…However, it works if you pass a table name instead of a query. What is wrong?

Function LoopTheList(strQueryName as String)

Dim strContactName As String, strContactList As String

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb ' Open pointer to current database
Set rs = db.OpenRecordset(strQueryName) ' Open recordset on saved query

rs.MoveFirst
Do Until rs.EOF
strContactName = Nz(rs![Responsible contact], "")
strContactList = strContactList & ";" & strContactName
rs.MoveNext
Loop
MsgBox Mid(strContactList, 2)

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[RoyVidar]

You forgot telling us which error, and which line it bombs on...

One quess, the query is parameterized?

Roy-Vidar

 

[PHV]

This returns error
Any chance you could post the whole error message and the highlighted line of code when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[HomeGrowth]

Below is the error message-

Run-time error '3061':
Too few parameters. Expected 1.

and it bombs on
Set rs = db.OpenRecordset(strQueryName)

The query doesn't have parameters set, but does have some criteria. One of the criteria based on a field from a open form where the function is call.

Thanks for looking into this!

 

[RoyVidar]

The query doesn't have parameters set, but does have some criteria. One of the criteria based on a field from a open form where the function is call." - that's usually called a parameter -> parameterized query ;-)

[tt] Dim db As Database
dim qd as dao.querydef
dim prm as dao.parameter
Dim rs As dao.Recordset
Set db = CurrentDb
set qd = db.querydefs(strQueryName)
for each prm in qd.parameters
prm.value = eval(prm.name)
next prm
Set rs = qd.OpenRecordset()[/tt]

- typed, not tested version of dynamicly resolving the parameter.

Roy-Vidar

 

[HomeGrowth]

RoyVidar & PHV

RoyVidar's code works great! sure is something new I've learned today. Thank you so much!

Tt

 

[tjs32]

Had the exact problem as Homegrowth and this fixed it beautifully. Like Homegroth - sure learnt something today. Thanks RoyVidar have a star