Access 2007 - Docmd.Openquery passing a parameter problem

[Lateral]

Hi guys,

I have a query that I wish to call via VBA and display the results as a PivotChart.

I also want to pass it a parameter to enable me to restrict the amount of data being displayed.

I use a command button on a form to trigger the VBA Event>

Here is my code:

Dim sWHERE As String

sWHERE = "[PartID] = " & Me.PartID

DoCmd.OpenQuery "qDisplayPartSoldHistory", acViewPivotChart

I understand that the Openquery does not have the ability to pass parameters like the Openform does. I have googled this issue but don't really understand the solutions they offer so I have come back to my favorite forum for some help.

I basically want to pass the "Me.PartID" automatically that is on the parent form to the query when it is run (qDisplayPartSoldHistory) when the command button is clicked.

Here is the source of qDisplayPartSoldHistory:

SELECT [Workorder Parts].*, Workorders.*, Customers.*, Workorders.Valid, Workorders.DateReceived, [Workorder Parts].PartID
FROM (Customers INNER JOIN Workorders ON Customers.CustomerID = Workorders.CustomerID) INNER JOIN [Workorder Parts] ON Workorders.WorkorderID = [Workorder Parts].WorkorderID
WHERE (((Workorders.Valid)=Yes) AND (([Workorder Parts].PartID)=[Parameter]))
ORDER BY Workorders.DateReceived;


Thanks for your help.


Cheers
Greg

 

[sneuberg]

You can set the filter property of the query as I show in this thread.

 

[Lateral]

Thanks Sneuberg,

I looked a the link and I have it sort of working.

Here is my code:

Dim sWHERE As String
Dim strWhere As String
Dim qdf As DAO.QueryDef

'sWHERE = "[PartID] = " & Me.PartID

strWhere = Me.PartID


MsgBox Me.PartID

SetQueryProperty "qDisplayPartSoldHistory", "Filter", strWhere
SetQueryProperty "qDisplayPartSoldHistory", "FilterOnLoad", True
DoCmd.OpenQuery "qDisplayPartSoldHistory", acViewPivotChart, acEdit

It's displaying the Pivotchart but is not filtering based upon the Me.PartID. It is using all of the records as input to the graph.

I've obviously missed something....

I added a Msgbox to check to see if the Me.PartID was being picked up and it is....

Any ideas?

Cheers
Greg

 

[sneuberg]

The filter needs to be constructed the same way as you would for a form or the WHERE argument in an open report. So strWhere = Me.PartID should be something like

strWhere = "[PartID] = " & Me.PartID

for a numeric PartID or

strWhere = "[PartID] = '" & Me.PartID & "'"

for a text PartID. This of course is assuming the name of the field is PartID

 

[Lateral]

hi Steve

The PartID is a numeric value.....

Also, do I need to change the actual query in some way to let it know to expect the filter?

 

[sneuberg]

After you run this code you should be able to open the qDisplayPartSoldHistory in design view, check the query's property and see if the filter was set corrected. You can also see if it's right by just running the query by itself.

 

[sneuberg]

Your query is a little more complex than the one in the other thread. Maybe this needs some tweaking. If you can upload your database I'll see if I can figure out what's wrong.