button to open a report with only filtered data

[d3ell]

Hi all.



New to this Access World and I have made a database using several youtube vids and microsft tutorials.

I cant seem to fathom out how to do the following..

I have some data in a form (only 8 records and a few columns) that I filter using a right click and filter action. This is a date value and in this process I'm filtering by "this week".

This filter reduces my 8 records to 2 records which is correct!

However when I use the button I have created to send the data to a form, it still sends all the data (all 8 records). I have tried using the Build event scenario and change the "where condition" to Forms![RemedialLog]![Date Complete].FilterLookup

however this doesn't seem to work and it still sends all 8 records to the form.

To be more precise what im ultimately trying to achieve is a chart in my report that filters the data.

My report has a chart in it, that works fine. however even if I filter the data in the report. The records change correctly so that only 2 records show, however the chart still seems to be pulling all 8 records worth of data and not updating?

So my thoughts were to change the amount of records that were sent to the report via the filter in the form.

You experts may say its as simple as adding an update event to the chart within the report to make the chart update when the filter is applied?

Any help for a noob?!??

Would be massively appreciated!

Loving access at the mo!

Thanks

D3ell

[ItsMe]

If you use the Wizard to create a control button to open reports, it will create a macro. I would rather use VBA. You can save your form's filter property to a string variable and then use the same string variable to open the report in a Docmd.


So something like this would be the code. Try it by creating a new control button and canceling the Wizard. Use the elipses(...) next to the "Click Event" field in the property sheet's "Event Tab". Then click "Code Builder"

Dim strWhere As String
strWhere = ""
if me.filteron = true then
strWhere = Me.Filter
End If
Debug.Print strWhere
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere, acHidden
DoCmd.SelectObject acReport, "ReportName", False
DoCmd.OutputTo acOutputReport, "rReportName, acFormatPDF
DoCmd.Close acReport, "ReportName"

[d3ell]

Hi Thanks for replying!

Ok, managed that and yes it filters through the correct data. The report only shows the 2 records that I want it to show. However the chart that is on the report is somehow still picking up the data from all the records. (all 8) How would I get the chart to lookup the filtered data within the report? so that it only uses the 2 records that it's pulled up using your previously explained button?

Thanks

[ItsMe]

Not sure. I don't have much use for charts. I am going to guess that this chart is using some sort of OLE. You will need to look at the Recordsource or Image Property or ???? for the chart "Control".

Are you printing your report or are you looking at your report in "Report View"? I ask because I am only aware of One RecordSource for Reports in Print Preview. I don't have a lot of experience with reports in the newer Report View. I still use forms rather than Report View.

Just mentioning it because, based on your answers, I will need to investigate it.

[d3ell]

yeah sorry to be so much of a noob, all I can try and explain is how I made it.

In the reports section, in the design view, I created a chart using the add chart button at the top. This opens a wizard whether wizard is checked or not. I then just followed the instructions. I cant find "record source" anywhere in the properties. It does have a "row source" and "rowsource type". I think this is where the problem lies as the chart is just reading from a table. irrelevant of how the results are filtered through.

Do you have any idea how to fix this? Is that explanation OK?

Is there another way to create a chart with a "recordSource"?

Thanks!

[ItsMe]

You got it. The RowSource will dictate how the Chart Object appears in the report. You will have to include the same string variable in the RowSource property of your Chart Control.

Can you post the text of your Rowsource property here?

What is the name of the control for your Chart?

Where, exactly, is the Chart Control? Is it in the report header, page footer, detail section? Can you find the name of the section in the property sheet?

Try and answer what you can.

[d3ell]

Ok - Row source property is - SELECT [Reason for Defect],Count(*) AS [Count] FROM [Builders Query] GROUP BY [Reason for Defect];

name of Control - Graph 22 ??

Chart is in the page footer

Name of section - pagefootersection

This correct?!

Thanks for your persistence!

[ItsMe]

This is going to get a little bit complex. But only because there are several steps to the process.

We need to send the Filter criteria from the form to the report. So let's add an open arg to the docmd that opens the report to the filtered criteria.

Before we had this
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere, acHidden

Now we need this in the form's VBA module
DoCmd.OpenReport "ReportName", acViewPreview, , strWhere, acHidden, strWhere

In the reports VBA module, we will need to add some code. Start by declaring a string variable in the Module's header. Use the View Code Option in the Report's design view to open the VBA editor aka the IDE. Under the line "Option Compare Database" paste the following.

Dim strWhere as String

Now we have a variable declared that we can use anywhere within the module. Let's initialize it using the Report's Load Event handler. At the top left of the VBA editor, there is a pulldown. It should say "General". Go ahead and select "Report". It will default to create an "On Open" handler. Use the pulldown on the right to create a "Load" handler.

Insert the following between the first and last lines of your On Load Handler. We want the On Load event to initialize our string variable we declared earlier.

strWhere = Me.OpenArgs

Now we need another event to add strWhere to our Chart Control's RowSource. At the top of the VBA editor use the pulldown on the left to select your "pagefootersection". It should default to "On Format" or "Format". If not, use the selection on the right to create an event to format your pagefootersection.

The following code will be used to change the Rowsource property of your Chart Control. We will take the original SQL that is your Control's RowSource and concatenate the original form's filter property to it. I believe this will work but it nay not. I may have the wrong event handler for the control's Rowsource or the SQL string syntax may need some help. Just let me know what happens and click Debug if Access produces and error. Let me know what you see.

Dim strSQL As String
strSQL = "SELECT [Reason for Defect],Count(*) AS [Count] FROM [Builders Query] GROUP BY [Reason for Defect] HAVING " & strWhere
Debug.Print strSQL
Me![Graph 22].RowSource = strSQL

[d3ell]

Hi, thanks for the detailed reply! I have just left work so when I return on Monday I will try the above!

Thanks for putting the time in to help me!! Much appreciated!

[ItsMe]

No problem. Let us know how it goes.