?
Solved

Open Report with Query in VBA

Posted on 2012-03-16
4
Medium Priority
?
472 Views
Last Modified: 2012-04-10
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
Comment
Question by:gawiles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37731339
instead of posting a word doc, upload a copy of the db
0
 

Author Comment

by:gawiles
ID: 37731985
Data in db is too sensitive I will have to take time to redact.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 37737888
<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
 
LVL 74

Expert Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…
Suggested Courses

777 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