?
Solved

qdf.parameters

Posted on 2008-10-23
13
Medium Priority
?
539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
LVL 28

Accepted Solution

by:
omgang earned 2000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

718 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