Generate Access Report From Datasheet Results

I have a query (qryInventory) that displays inventory items.  I created a read-only form (frmInventory) that displays the query results in datasheet view.  The purpose of the form is to allow the user to sort and/or filter the data by using the built-in datasheet menus.  Finally, I have a report that is also based on qryInventory.  I would like the user to click a button a generate the same report, but display the records that have been filtered in the datasheet.  I am trying to avoid having to setup 20 comboboxes that are used to filter the query directly.

Is this possible?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How does your frmInventory "filter" the data at this point? If you're using the builtin items, then you can perhaps use the Forms Filter property to filter your report. To test that, use code like this to open your report:

DoCmd.OpenReport "YourReport", acViewPreview, , Me.Filter

If you need to get the Filter from a Subform:

DoCmd.OpenReport "YourReport", acViewPreview, , Me.NameOfSubformCONTROL.Form.Filter

NameOfSubformCONTROL is the name of the Subform CONTROL that is on your parent form, and may or may not be named the same as the Form you're using as a Source Object.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bayouexpertAuthor Commented:
The form is filtered using the built-in items that are available in datasheet view (See Below).
Form View
The datasheet is a subform on the main form (the form that contains the report button).  I tried the code that you suggested, but the filters did not apply to the report.
Jeffrey CoachmanMIS LiasonCommented:
This is the issue with trying to generate a report from a filtered form.
It can be done, ...but it is tricky.
It involves saving the form's recordsource and filter as a Public variable and using that as the Report's recordsource.  
Therein lies the issue, ... the Forms "Filter" take this form:
So it is not "easy" to just "Tack" this on to the end of the forms recordsource:
1. The Form's reordsource need to be an SQL statement
2. The Recordsource need to have the trailing ";" stripped off
3.  The Forms filter need to modified to take this form:
4. This all gets compounded if you have more than 1 filter applied.

This will be much easier to do if you created a separate system to filter the form.
Then your system could spit out the (Filtered) SQL ststement and you could view/edit  it in a form, or open it in a report.

But here you would have to limit the number of fields to be filtered.

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Ahhh .... as Jeff said, you really can't do this. I thought perhaps you were using the ribbon/menu buttons, which basically set the filter for the Form, but that's not the case here.

bayouexpertAuthor Commented:
Jeff - That is not the answer I was hoping for!  I think that is unfortunately well beyond my capabilities.  
Jeffrey CoachmanMIS LiasonCommented:
Again, anything is possible.
How much trouble you are willing to go through to do it another issue...

And as always, there is always more than one one to solve any given issue

Here is a very "Brute Force" example of one way to do what you are asking...

You can play around with it and see if you can adapt ti in your DB perhaps...?

Jeffrey CoachmanMIS LiasonCommented:
...but if it were me, I would create a Filter "Form" to make all my Filter selections.
Then open either the form or the report, based on these selections.
(Again, this presumes that the users are really only filtering a few fields (not all/any fields)

I'm a bit swamped right now, but if you need advice in this technique I am sure LSM can assist further.
bayouexpertAuthor Commented:
Using the original method of filtering the datasheet with the built-in tools, the form filter is...

(((qryInventory.CustomerName In ("AIR PRODUCTS","ARENA OFFSHORE")))) And (qryInventory.JobNum Not In ("C-00142","P-15257","P-15617"))

The desired report is based on that same query (qryInventory), so is it not possible to apply that filter to the report?  I apologize if you arleady answered that question above.

Perhaps I am stubborn, but I really like the concept of allowing the user to work with the interactive datasheet as opposed to combobox filters.  I also do not like that a combo box limits the user to selecting only one filter.  I suppose I could use list boxes...
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How did you determine those form filters? If you can get to them, then you can use the the methods I suggested earlier to apply them to your Report.

How do you open your report? If you allow the user to open the Report directly through the Access Nav Pane, then you'll have troubles with this. If, however, you're doing it through a button click, then you can (again) use code as I suggested, assuming you can retrieve those filters as you indicate above.
bayouexpertAuthor Commented:
The report generates with the applied filters when the below code is applied to the button on the main form....

DoCmd.OpenReport "YourReport", acViewPreview, , Me.NameOfSubformCONTROL.Form.Filter
Jeffrey CoachmanMIS LiasonCommented:
With what you posted above the catch is that the report Hardcodes the fields,
So you will have to make sure that the Recordsource for both the report and the form always reamins the same.

This will always be an "Ad Hoc" report, so you would never really know what the report is filtering unless you are the one who generated the form filters.

So you cant really name the report "Sales"
Because it might only be Sales for Germany.
Or it might only be for a certain employee, or a certain employee, ...etc
So you need to inform anyone who looks at the hardcopy that the report is "Filtered"
There are techniques to "Display" the filter on the form, but this is another topic...

Finally, ...
Note that in Access 2007 and newer you can filter the report just like you can the form.
So you may be able to do this and skip the form entirely...
(The only kicker is that you must open the Report explicitly in "Report View" explicitly)

Now, all that being said, ... I am not tying to "Beat up" on your solution here.

Just that when you do things like this, there are a lot of things to consider.
I am all for using the default functionality in Access instead of building your own, ...but again, you need to be aware of the Pros and cons.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.