Open filtered query from Cmd button on report

[David Ball]

Hi,

I have a report with a row for each “ISO” field, a text field that shows an isometric number. I have inserted a Command Button, that appears on each row, and want to be able to open a query and filter it with the filter to match the ISO number of the row that I selected the Command Button on.
The query also has a field called ISO. (Does it matter that the query and report have the same field name?).
I have been trying to set up code to open the query and filter it for the same ISO number on the report row that I selected.
How do I modify my code to get it working?

Private Sub Command16_Click()
DoCmd.OpenQuery "qryCOW_Punch_List-Dates", , acReadOnly
DoCmd.ApplyFilter , "[ISO] ='rptCOW_Punch_List-Dates.ISO.Value'"
End Sub

Thanks very much

Dave

 

[Ranman256]

You don't open a query and a filter,
The query HAS the filter in it.

 

[David Ball]

I know that, how do I do it?

 

[pbaldy]

Most of us never let users in tables or queries. I'd open a form or report:

http://www.baldyweb.com/wherecondition.htm

 

[David Ball]

Thanks pbaldy,

I am the only user and I need access to the query as I need to copy and paste it into Excel (and no, I don't want to export it into Excel, pasting is quick and works fine).

This may not be standard practice but I would really like to know how to do it.

Thanks very much

Dave

 

[The_Doc_Man]

I have been trying to set up code to open the query and filter it for the same ISO number on the report row that I selected.

With regard to "ApplyFilter" -

DoCmd.ApplyFilter method (Access)

Office VBA reference topic

learn.microsoft.com

The "Remarks" section starts out with this instruction (added color emphasis is mine):

"Use the ApplyFilter action to apply a filter, a query, or an SQL WHERE clause to a table, form, or report to restrict or sort the records in the table or the records from the underlying table or query of the form or report."

It would appear that you cannot apply a filter to a query unless there is a supporting structure that allows filtration of a bound recordset. That is something you can do to a form or report, as they provide an underlying recordset. However, you can't do that to a query. The key phrase here is "underlying" - implying that there is some type of infrastructure to allow attaching a filter. Queries don't have that particular ability.

Therefore, in DIRECT response to your question "I know that, how do I do it?" the answer is "You can't." Access doesn't support that particular action as you literally described it. However, if you wanted to open a form or report based on that selection, you could do that. I.e. instead of DoCmd.OpenQuery, you could open something else that DOES allow a filter to be applied to it. I'm looking for a workaround here, so let's ask the next logical question in solving this problem.

The next logical question is, "So let's say you've made the selection and clicked the button, and now you opened this query. Let's further say for purpose of discussion that there WAS a way to make the selection to affect a query. What is the next thing your would have done with that query? What would be the immediately following step?"

This is NOT a frivolous question. I'm trying to figure your purpose so I can suggest a workable alternative.

 

[pbaldy]

My guess is that after 8 years, the question is moot.