Solved

qdf.parameters

Posted on 2008-10-23
13
501 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

920 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