• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

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?
0
bayouexpert
Asked:
bayouexpert
  • 4
  • 4
  • 3
1 Solution
 
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.

0
 
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.
0
 
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:
    (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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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.

0
 
bayouexpertAuthor Commented:
Jeff - That is not the answer I was hoping for!  I think that is unfortunately well beyond my capabilities.  
0
 
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...?

JeffCoachman
Database76.mdb
0
 
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.
0
 
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...
0
 
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.
0
 
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
0
 
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.

;-)

JeffCoachman
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now