Too few paramters, yet parameters evaluate correctly

[fearoffours]

Hi there, I'm opening the following recordset in a forms On_Current event:

Set rst = dbs.OpenRecordset("qryPlantFromPlantsForm", dbOpenDynaset)

This is giving me a runtime error 3061, too few parameters.

The query receives a parameter from the value of another (open) form.
If I break the code before the line above, the parameter evaluates correctly in the Immediate window.

eg:

?currentdb.querydefs("qryPlantFromPlantsForm").Parameters(0).Name 
[Forms]![frmPlants].[txtFilterName]

?[Forms]![frmPlants].[txtFilterName]
Acer palmatum 'Beni-schichihenge'

So why is the recordset not opening?

 

[KenHigg]

Can you try running it without any params in the query? This should tell you if it's the query or the OpenRecordset statement...

 

[fearoffours]

Putting a finite value in place of the parameter in the query allows the recordset to open.
Therefore it's the query (which when viewed as an SQL statement looks like this)

SELECT tblPlants.[Botanical Name], tblPlants.plantID, tblPlants.[Common Name], tblPlants.txtImagePath
FROM tblPlants
WHERE (((tblPlants.[Botanical Name]) Like "*" & [Forms]![frmPlants].[txtFilterName] & "*"))
ORDER BY tblPlants.[Botanical Name];

 

[KenHigg]

I think it's a query / parameter issue where you have to right click in the query design and do parameters. I would suggest building the sql string in the module and use that as the recordsource.

 

[doco]

Just a thought and not sure if is a problem, but is perchance your txtImagePath a hyperlink?

 

[fearoffours]

no it's a simple text string.
I'll try composing that sql string in the module, if I can get the quotes right!

 

[gemma-the-husky]

is the openrecordset error a dao/ado confusion

which library are you using?

 

[KenHigg]

Maybe this:

strSQL = "SELECT tblPlants.[Botanical Name], " & _
                "tblPlants.plantID, " & _
                "tblPlants.[Common Name], " & _
                "tblPlants.txtImagePath " & _
         "FROM tblPlants " & _  
         "WHERE (((tblPlants.[Botanical Name]) Like '*'" & [Forms]![frmPlants].[txtFilterName] & "'*'))" & _
         "ORDER BY tblPlants.[Botanical Name];"

 

[fearoffours]

It's a DAO recordset, declared explicitly.

Thanks Ken, yours looked better than mine hah, but yours returned the following syntax error: 3075 missing operator in query expression
'(((tblPlants.[Botanical Name]) Like '*'Acer palmatum atropurpureum group'*'))'

So the parameter itself is evaluating properly, just looking for that syntax error now.
Surely the like is the operator there, what is it missing?

 

[KenHigg]

Well....

strSQL = "SELECT tblPlants.[Botanical Name], " & _
                "tblPlants.plantID, " & _
                "tblPlants.[Common Name], " & _
                "tblPlants.txtImagePath " & _
         "FROM tblPlants " & _  
         "WHERE (((tblPlants.[Botanical Name]) Like *" & [Forms]![frmPlants].[txtFilterName] & "*))" & _
         "ORDER BY tblPlants.[Botanical Name];"

 

[fearoffours]

Access still thinks there's a missing operator in query expression:

'(((tblPlants.[Botanical Name]) Like *Acer palmatum atropurpureum group*))'

 

[ecawilkinson]

Fearoffours,

using eval() almost always solves this. It works within the query definition, e.g put the following in the criteria for the Botanical Name field in qryPlantFromPlantsForm:

like "*" & eval("[Forms]![frmPlants].[txtFilterName]") & "*"

HTH,
Chris

 

[fearoffours]

Awesomeness, that's a handy function!
Thanks to all for their help!

 

[ecawilkinson]

hi again,

if you want to create the SQL string in a code module, the following code is copied from KenHigg, except for the fact there is a single quote jsut before the first asterisk and just after the second asterisk.

strSQL = "SELECT tblPlants.[Botanical Name], " & _
                "tblPlants.plantID, " & _
                "tblPlants.[Common Name], " & _
                "tblPlants.txtImagePath " & _
         "FROM tblPlants " & _  
         "WHERE (((tblPlants.[Botanical Name]) Like '*" & [Forms]![frmPlants].[txtFilterName] & "*'))" & _
         "ORDER BY tblPlants.[Botanical Name];"

(The eval() trick is for use in query definitions, although it would do no harm in the code above, but would be redundant.)

HTH,
Chris

 

[KenHigg]

Thanks for tweaking me on that one.

Wonder if you can put the eval back in his original param (query) and get it to work...?

 

[fearoffours]

Ken, yes the eval works in the original query. The problem is fixed!

 

[KenHigg]

Cool - We both learned something new -

 

[ecawilkinson]

Ken,

In my first post I was saying that eval() was intended to be put into the original query. It does not contribute much in a code module, in this circumstance.

Chris

 

[doco]

Cool - We both learned something new -

Make that a trio

 

[KenHigg]

Ken,

In my first post I was saying that eval() was intended to be put into the original query. It does not contribute much in a code module, in this circumstance.

Chris

Got it now -

If I would have thunk for a minute I would have probably realized that