Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Open Report with Query in VBA

Query as a source record works fine but I now want to use multiple queries, may be use a case statement, but the VBA statement is not passing the  query.  Seems like joined tables to main table in the query is not being seen.  Opening the Report has event that opens a dialog box for filter selection and from this form criteria is sent to Query filtering report.  Query run by itself works and returns only two records.  VBA automation returns thousands or ALL records.  Query is not being used in the process obviously.  What should the Report record source be?  I usually use the main table, what should be the source when opening and calling for a query to provide source?
VBA-action-Report-open-with-Quer.docx
0
gawiles
Asked:
gawiles
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
instead of posting a word doc, upload a copy of the db
0
 
gawilesAuthor Commented:
Data in db is too sensitive I will have to take time to redact.
0
 
Jeffrey CoachmanMIS LiasonCommented:
<Query is not being used in the process obviously.>
Not necessarily,

Create the query that accepts all the parameters from the "Criteria" Form.
Then base the report on the query...
Then there is no real need to specify the query as the "Filter in your VBA.

Again, *make sure* the query works against the Criteria form.
Then just create the report form the Query.
Then set the criteria form to open first.
On the criteria form just do something like this to Open the report:
   Docmd.OpenReport "YourReport", acviewPreview
...That's all.

The report will open, it will look at the query, the query will look at the criteria form.
Again, that's all that is needed.

There are many other ways to do this, but you just seemed to have made it more complex than it needs to be...

All this being said, if you want to do things like allow all records to show if a criteria is left empty, or use multiple AND/OR Logic, then either your query needs to get more complex, or you need to do everything in code.

Another note, ...Your use of an option group may be a bit inefficient.
An Option group must be updated manually when ever a selection is added, deleted or changed.
  In addition, it takes up a lot of real estate.
If you use a combobox instead, those issues would be eliminated.

But again, once you start wanting to do things like Show "All" then you really need to think about doing most of this in code and dumping the "query" approach.

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Here is a sample of exactly how complex this can end up being...
(As I stated, there are many way you could approach this, depending on your exact needs)

So as you can see, if you really want to have "Full Control", you have to familiarize yourself with VBA, SQL and Form design...
Database104.mdb
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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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