Solved

Open Report with Query in VBA

Posted on 2012-03-16
4
465 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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

726 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