form to filter for the criteria, then open the report based on that criteria.

Posted on 2011-09-11
Medium Priority
Last Modified: 2012-05-12

I have a form and report in Access 2010.  I want to filter the report from a combo box on the form with the submit button is pressed ("onClick").  Im able to open the report with the button but im having a lil difficulty filtering the report based on the drop down selection of the form.  I've researched and see i have to place the filter on the data the report is pulling from but not sure how to exactly do that, any ideas???
Question by:IT_newbie01
  • 5
  • 4
LVL 41

Expert Comment

ID: 36520969
We need more details.
May be you can upload sample from your DB?
You can also try to filter data in report source query. When it will work, you can use same "where" in filter.

Accepted Solution

Armen Stein - Microsoft Access MVP since 2006 earned 1600 total points
ID: 36523710
One way to have a report prompt the user for criteria before it runs is to open a form from the report's Open event.  Open the form in Dialog mode so that the report waits for the form to be closed or hidden before it proceeds.  That way you can collect criteria from the user and build a Where clause for the report.  It also means that you can call the report directly - you don't need to call it from a form.  And the selection form is reusable - it can be called from multiple reports if they need the same criteria.

I've posted examples of this technique on our free J Street Downloads page at http://www.JStreetTech.com/downloads.
See "Report Selection Techniques".


Author Comment

ID: 36536330

That is exactly what im looking to accomplish.  I wish i'd seen this post alot sooner.  Im having to rearrang alot of my design because it is not web compatible .  Can you confirm that this setup is web compatible??
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

ID: 36538036
Hi IT_newbie01,

This technique will not work in Access web applications.  For one thing, it uses VBA extensively, which isn't supported for web apps.  So although a technique *like* this might work, it would need to be rewritten using web-safe forms and macros.


Author Comment

ID: 36538280

I’m sorry to hear that!! But you did provide an answer for the question posted so I will award you the point in a few minutes.  Would you happen to know where I might find documentation of the equivalent that’s web accessible?  Also do you know why VBA is no supported on the web and what techniques are supported when posting to the web?  This would help me know if certain techniques are web-friendly moving forward.


Author Closing Comment

ID: 36538287
I wish this technique was supportted for posting to the web.
ID: 36538436
Me too!  As developers (including us) get more experience working with web apps, new techniques will emerge.  I don't know of documentation of an equivalent for web apps.  I do know that many of our clients are interested in using Office 365 for hosting, and it doesn't support web reporting *at all* yet.  So we are really in a pioneering stage of development here.  Hopefully many of these issues will be resolved soon, at least by the next release of Office.

As I understand it, VBA isn't supported in web apps because having the power and flexibility of VBA turned loose on the server was deemed too dangerous and hard to control.  And that makes some sense - you don't want a rogue chunk of VBA taking down a whole server.  That isn't likely with the more carefully controlled macros.  There are many other limitations of web apps for similar reasons.  That's why although very interesting, Access web apps are not a solution to every problem.


Author Comment

ID: 36538553
"Office 365 for hosting, and it doesn't support web reporting"

That means even if I find an equivalent (in Access 2010) it won’t be supported on the web??  Can you define "web reporting"?  So there’s no way to filter a report via the web with Access 2010?
ID: 36538815
What I mean is that as far as I know (and I can't see that it has changed yet), Office 365 does not support Access reports running in the browser.  They are supported if you have your own SharePoint 2010 Enterprise environment.  I believe AccessHosting supports them too.  And they are supported running in an Access client app connected to SharePoint.

See this rather frustrated thread:

It's another reason that Access web apps just don't fit every scenario.  At least not yet - things are still changing.


Author Comment

ID: 36538948

Ok, that's what my research had turned up.  My client has SharePoint so I’m in charge of migrating report supporting into SharePoint w/ Access for the web.  Fun times!!!  I will post another question for reference/ sample on this reporting in Access support on the web.


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

600 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