Open Dynamic Parameterized Query using DAO

[ItsMe]

Is there a way to open a parameterized query using DAO without passing the parameters (via DAO) to the named query object? I have some criteria in the query object that references controls on an open form. I want to open that query object with DAO using OpenRecordset but keeping getting "Parameters Expected" error.



So the form is open and the query works fine when I open it using the Nav Pane but if I try to execute the VBA that uses DAO it don't like it. I guess I should expect this but wondering if there is a way to avoid writing VBA to pass the criteria that is already in the query object as SQL.

[Bob Fitz]

Can you show us the code you are trying to use.

[ItsMe]

Just a basic OpenRecordset. I tried dbOpenDynaset too...

'Calculate the total number of units
Dim rsSum As DAO.Recordset
Dim lngSum As Long
lngSum = 0
Set rsSum = CurrentDb.OpenRecordset("qryInvView", dbOpenSnapshot)
    With rsSum
        If .EOF = False Then
            .MoveFirst
                While .EOF = False
                    lngSum = lngSum + ![CountUnits]
                    .MoveNext
                Wend
        End If
    Me.txtUnitCount.Value = lngSum
    End With
rsSum.Close
Set rsSum = Nothing

In case you are curious, here is the SQL from the query object named in the DAO. THis SQL works fine when I double click the query in the Nav Pane. Within this SQL is another query object that is a Totals query.

SELECT tblPurchOrd.PurchOrdPK, tblPurchOrd.CustFK, tblPurchOrd.PO, qryProdDescrInParam.Product, qryProdDescrInParam.CountUnits, qryProdDescrInParam.SpeciesPK, qryProdDescrInParam.GradePK, qryProdDescrInParam.DimensionPK, qryProdDescrInParam.CurePK
FROM tblPurchOrd INNER JOIN qryProdDescrInParam ON tblPurchOrd.PurchOrdPK = qryProdDescrInParam.PurchOrdFK
WHERE (((tblPurchOrd.PurchOrdPK)=IIf([Forms]![frmInventory]![cmbPO]<>"",[Forms]![frmInventory]![cmbPO],[tblPurchOrd].[PurchOrdPK])) AND ((tblPurchOrd.CustFK)=[Forms]![frmInventory]![cmbCust]) AND ((qryProdDescrInParam.SpeciesPK)=IIf([Forms]![frmInventory]![cmbSpecies]<>"",[Forms]![frmInventory]![cmbSpecies],[qryProdDescrInParam].[SpeciesPK])) AND ((qryProdDescrInParam.GradePK)=IIf([Forms]![frmInventory]![cmbGrade]<>"",[Forms]![frmInventory]![cmbGrade],[qryProdDescrInParam].[GradePK])) AND ((qryProdDescrInParam.DimensionPK)=IIf([Forms]![frmInventory]![cmbDim]<>"",[Forms]![frmInventory]![cmbDim],[qryProdDescrInParam].[DimensionPK])) AND ((qryProdDescrInParam.CurePK)=IIf([Forms]![frmInventory]![cmbCure]<>"",[Forms]![frmInventory]![cmbCure],[qryProdDescrInParam].[CurePK])));

[ItsMe]

So far, the only solution I am finding is to use QueryDefs and pass the variable through the query def. Kinda defeats the purpose of the query object.

I preach to avoid parameterized queries for most solutions. I thought I would try one for the first third of this Module I am constructing. It does not seem to be panning out. I am going to spend my time bringing the SQL from the object into the class and then I will revisit why I could not get the parameterized query to work. Might be able to look at the reasons why by Monday. I need to get this Inventory Interface completed first.

[pbaldy]

You can usually wrap each form reference in the Eval() function.

[ItsMe]

I was able to get the Query Object to work when I included the Eval() function but I still get an exception when I try to open the recordset via DAO.

The following is the only code I am executing. I moved it to a seperate click event for testing.

'Calculate the total number of units
Dim rsSum As DAO.Recordset
Dim lngSum As Long
lngSum = 0
Set rsSum = CurrentDb.OpenRecordset("qryInvView", dbOpenSnapshot)
   With rsSum
        If .EOF = False Then
            .MoveFirst
                While .EOF = False
                    lngSum = lngSum + ![CountUnits]
                    .MoveNext
                Wend
        End If
    Me.txtUnitCount.Value = lngSum
    End With
rsSum.Close
Set rsSum = Nothing

[pbaldy]

Can you post the db here? It worked for me; the query was against a linked SQL Server table, but I can't imagine that would matter.

[ItsMe]

Let me see if I can put something together that I can upload.

[orange]

ItsMe,

See if this link helps.

Good luck.

[ItsMe]

I tried my best to clean up a version so the form would be intuitive. The back story is I am revising a form to offer more features. The form will allow the user to apply various criteria to eventually create reports for Front Operations regarding warehouse location of filtered inventory.

The section I am currently focusing on is the ListBox RowSource. The Customer ID will be mandatory and the other criteria will be optional. Behind the form is legacy code as well as some ideas for the future that is commented out. I cleaned up (removed) a lot of controls and code to focus on the listbox Rowsource but, there is still some code in there that is commented out.

Orange, I will take a look at that link now.

[ItsMe]

Orange, that worked. Did you know it was going to work?

This is the QueryDef solution I was referring to in post #5
http://support.microsoft.com/kb/142938

It seems the function you linked to does all the heavy lifting for you. All I had to do was pass the query object name along with the snapshot argument and whamo. It seemed to work quickly too. Performance is a major consideration as I refactor my code and try new things.

I know I have a soltion that I can roll out and there is a big light at the end of the tunnel. I am not going to mark this solved yet though. I am still interested in understanding if, indeed, the only solution is QueryDefs.

[pbaldy]

Cool function. Regarding Eval(), the argument needs to be a string. This doesn't throw the error:

SELECT tblPurchOrd.PurchOrdPK, tblPurchOrd.CustFK, tblPurchOrd.PO, qryProdDescrInParam.Product, qryProdDescrInParam.CountUnits, qryProdDescrInParam.SpeciesPK, qryProdDescrInParam.GradePK, qryProdDescrInParam.DimensionPK, qryProdDescrInParam.CurePK
FROM tblPurchOrd INNER JOIN qryProdDescrInParam ON tblPurchOrd.PurchOrdPK = qryProdDescrInParam.PurchOrdFK
WHERE (((tblPurchOrd.PurchOrdPK)=IIf(Eval('[Forms]![frmInventory]![cmbPO]')<>"",Eval('[Forms]![frmInventory]![cmbPO]'),tblPurchOrd.PurchOrdPK)) And ((tblPurchOrd.CustFK)=Eval('[Forms]![frmInventory]![cmbCust]')) And ((qryProdDescrInParam.SpeciesPK)=IIf(Eval('[Forms]![frmInventory]![cmbSpecies]')<>"",Eval('[Forms]![frmInventory]![cmbSpecies]'),qryProdDescrInParam.SpeciesPK)) And ((qryProdDescrInParam.GradePK)=IIf(Eval('[Forms]![frmInventory]![cmbGrade]')<>"",Eval('[Forms]![frmInventory]![cmbGrade]'),qryProdDescrInParam.GradePK)) And ((qryProdDescrInParam.DimensionPK)=IIf(Eval('[Forms]![frmInventory]![cmbDim]')<>"",Eval('[Forms]![frmInventory]![cmbDim]'),qryProdDescrInParam.DimensionPK)) And ((qryProdDescrInParam.CurePK)=IIf(Eval('[Forms]![frmInventory]![cmbCure]')<>"",Eval('[Forms]![frmInventory]![cmbCure]'),qryProdDescrInParam.CurePK)));

[ItsMe]

I should have looked into it some more and passed the correct argument to the Eval() function. I glossed over it when I added the Eval function to the SQL and it retrieved the correct records. Thanks Paul. This may be the best approach of all the options.

[pbaldy]

No problem.

[orange]

Itsme,

I didn't know for sure, but I saw that function recently in a response to someone who had trouble getting the form value.
When I saw Paul's comment about Eval(),I recalled it, searched it down again, and suggested it.
Glad it worked for you.