Solved

qdf.parameters

Posted on 2008-10-23
13
493 Views
Last Modified: 2013-11-28
I have a subform whos query shows all emails for a client.

The following fails at the qdf.Parameters line:

Function ConcatenateField()
Dim DB As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim mySql As String

mySql = "select Email From EmailQry WHERE (CID = [Forms]![Directory Information]![ClientKey])"

Set DB = CurrentDb
Set qdf = DB.CreateQueryDef("", mySql)
qdf.Parameters([CID]) = [Forms]![Directory Information]![ClientKey]

Set rs = qdf.OpenRecordset

    rs.MoveFirst
    Do Until rs.EOF
        If Len(rs("Email")) > 0 Then strTemp = strTemp & "; " & rs("Email")
      rs.MoveNext
    Loop
    ConcatenateField = Mid(strTemp, 2)

End Function

I get a run time error 3265 "item not found in list"
I've tried a few variations on this line but I'm not familiar enough with it to know how to fix it.

This works:
Dim rs As DAO.Recordset, strTemp As String

Set rs = CurrentDb.OpenRecordset("select Email From EmailQry")
rs.MoveFirst

Do Until rs.EOF
    If Len(rs("Email")) > 0 Then strTemp = strTemp & "; " & rs("Email")
  rs.MoveNext
Loop
ConcatenateField = Mid(strTemp, 2)

But it returns every record.  Trying to get it to only pull records based on the [ClientKey]

0
Comment
Question by:ShawnGray
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22791025
pass to the function the clientKey

Function ConcatenateField(ck as long)

Dim rs As DAO.Recordset, strTemp As String

Set rs = CurrentDb.OpenRecordset("select Email From EmailQry where clientkey=" & ck)
rs.MoveFirst

Do Until rs.EOF
    If Len(rs("Email")) > 0 Then strTemp = strTemp & "; " & rs("Email")
  rs.MoveNext
Loop
ConcatenateField = Mid(strTemp, 2)

end function

from the form call the function

call ConcatenateField(me.clientKey)

0
 
LVL 28

Expert Comment

by:omgang
ID: 22791056
cap's solution is best but if you want to use qdf.parameters specify in this manner

qdf.Parameters("[CID]") = [Forms]![Directory Information]![ClientKey]

OM Gang
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22791073
One thing that will work Shawn  it to use a function that contains the client key in your recordset.

Jim
0
 
LVL 28

Expert Comment

by:omgang
ID: 22791074
Sorry, should be

qdf.Parameters("[Forms]![Directory Information]![ClientKey]") = [Forms]![Directory Information]![ClientKey]

you need to tell the QueryDef object explicitly what the parameter is

OM Gang
0
 

Author Comment

by:ShawnGray
ID: 22791177
Thank you both

Cap,
I get "too few paremeters" on
Set rs = CurrentDb.OpenRecordset("select Email From EmailQry where clientkey=" & ck)

omgang,
I get "too few paremeters" on
Set rs = qdf.OpenRecordset
0
 
LVL 28

Expert Comment

by:omgang
ID: 22791217
Are there parameters for EmailQry as well?  If so you'll need to explicitly specify those as well.
OM Gang
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 22791249
What I mean is, if EmailQry has something like
WHERE SomeField = [Forms]![TheForm]![TheField]
then the QueryDef object is going to need to have that parameter explicitly specified as well

In general, if there are three parameters in the query and/or sub-queries you'll need to
Set qdf = CurrentDb.QueryDefs("SomeQuery")
qdf.Parameters("[Forms]![SomeForm]![TheFirstFormField]") = [Forms]![SomeForm]![TheFirstFormField]
qdf.Parameters("[Forms]![SomeForm]![TheSecondFormField]") = [Forms]![SomeForm]![TheSecondFormField]
qdf.Parameters("[Forms]![SomeForm]![TheThirdFormField]") = [Forms]![SomeForm]![TheThirdFormField]

OM Gang
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22791293
pass to the function the clientKey

Function ConcatenateField(ck as long)

Dim rs As DAO.Recordset, strTemp As String

Set rs = CurrentDb.OpenRecordset("select Email From EmailQry where CID=" & ck)
rs.MoveFirst

Do Until rs.EOF
    If Len(rs("Email")) > 0 Then strTemp = strTemp & "; " & rs("Email")
  rs.MoveNext
Loop
ConcatenateField = Mid(strTemp, 2)

end function

from the form call the function

call ConcatenateField(me.clientKey)


0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22791297
is CID a number data type or text?
0
 

Author Closing Comment

by:ShawnGray
ID: 31509444
Beautiful.  Thank you for getting me over that learning hump.  I kept trying one or the other.  Didn't figure I needed to define them all.
Thank you again.  Very much.
0
 
LVL 28

Expert Comment

by:omgang
ID: 22791307
cap', correct me if I am wrong, but if EmailQry has parameters then you'll need to use a QueryDef object and pass the parameters explicitly.  Hence the 'Too few parameters, Expected X' error message.
OM Gang
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22791389
'Too few parameters error is coming from wrong field name in the select statement

"select Email From EmailQry where clientkey=" & ck

it should be

"select Email From EmailQry where CID=" & ck

0
 
LVL 28

Expert Comment

by:omgang
ID: 22791434
Yes - if a field name is mis-typed or otherwise incorrect it will generate the same parameters message.  If I'm not mistaken, in this particular case, ShawnGray was dealing with multiple parameters, some in the sub-query EmailQry.  The QueryDef object must be used in this case and all parameters passed eplicitly.

OM Gang
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now