?
Solved

Generate Access Report From Datasheet Results

Posted on 2011-10-03
11
Medium Priority
?
363 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:bayouexpert
  • 4
  • 4
  • 3
11 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 36904054
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.

0
 

Author Comment

by:bayouexpert
ID: 36904115
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36905446
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:
    (YourForm.YourFilteredField=SomeValue)
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:
    YourFilteredField=SomeValue
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.


JeffCoachman
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85
ID: 36905472
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.

0
 

Author Comment

by:bayouexpert
ID: 36905668
Jeff - That is not the answer I was hoping for!  I think that is unfortunately well beyond my capabilities.  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36905766
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...?

JeffCoachman
Database76.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36905806
...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.
0
 

Author Comment

by:bayouexpert
ID: 36910958
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...
0
 
LVL 85
ID: 36911699
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.
0
 

Author Closing Comment

by:bayouexpert
ID: 36911832
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36912264
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.

;-)

JeffCoachman
 
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question