Solved

Open Report with Query in VBA

Posted on 2012-03-16
4
451 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
  • 2
4 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now