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
gawilesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.