Solved

qdf.parameters

Posted on 2008-10-23
13
524 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Database 5 45
Microsoft Access 2016 Bug? 9 44
Runtime Error -2147467259 (80004005) 7 46
What can I do to shrink my Access Frontend? 12 45
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

756 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