• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 550
  • Last Modified:

qdf.parameters

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
ShawnGray
Asked:
ShawnGray
  • 6
  • 4
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
omgangCommented:
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
 
jmoss111Commented:
One thing that will work Shawn  it to use a function that contains the client key in your recordset.

Jim
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
omgangCommented:
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
 
ShawnGrayAuthor Commented:
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
 
omgangCommented:
Are there parameters for EmailQry as well?  If so you'll need to explicitly specify those as well.
OM Gang
0
 
omgangCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
is CID a number data type or text?
0
 
ShawnGrayAuthor Commented:
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
 
omgangCommented:
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
 
Rey Obrero (Capricorn1)Commented:
'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
 
omgangCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now