Set up a report using more than one query

[jbeck2010]

I have set up 5 different queries to calculate one specific total each.
I have also set up 5 accompaning forms. They work great.
When I try to produce a report through the report wizard it says that
the source of the queries are not compatible or something like that.
What I would like to do is be able to print these individual totals on
one printed report.

Please help
jbeck2010

 

[Duane Hookom]

A report has one record source. Either place all your queries into a single
query, use subreports, write some code, or try DLookup() type functions.

 

[jbeck2010]

Duane,

God bless you. This problem has been nagging me for a long time. I was
developing a new database and I needed to know the answer. I am a very simple
minded person and I needed to have a simple answer. After reading your
directions on the subject, I thought and checked out each option.

#1 It was impossible to combine the queries into one due to conflicting
criteria in the same field.

#2 I don't know how to write code.

#3 The information on the Dlookup ( ) Type functions didn't seem to fit and
was very confusing to me.

#4 *Hallelujah, Praise Jesus..."SUBREPORTS" IS THE ANSWER...for me.

***For those people out there that are like me and know nothing about
writing code or using complex expressions, here's how I made the "SUBREPORT"
option workwork for me.

1. Use the report wizard in reports to setup a report. (use "ONE" of your
tables, forms or queries that you want on the report) You may have to delete
a heading in the report if you do not want it in the header. It depends on
how you ultimately want to set up the report. You will notice that the report
wizard will put a label and a control box on the report. This is very simple
to get rid of. In design view, in the properties of each, change the
"Special Effects" to Flat and the "Border Style" to Transparent. Next, on the
label and controlbox change the back fill color to White. Finally, Select the
font color you want on the label text. From here on it's just a matter of
positioning the labels and control boxs on the report. (You cannot see the
labels and control boxes on the report in report view, it looks like a
regular printed report)

2. Here's where the "SUBREPORTS" come in. In design view of the report,
click on the subreport tool and drag it to where you what it on the report
and click. It will go through another wizard to select which table, query,
etc. Select only "ONE" and create the subreport. Do as many subreports as you
want on the report. They must be done individually. Position them on the
report where you want them.

3. Follow step 1. after the report was created on all of the labels and
control boxes in the subreports. Then it's just a matter of positioning. Do
your placement in design view and switch to report view to check your work.

Duane, the above proceedure is probably not what you had in mind, but it
works for me and it will work for others like me that are mentally challenge
by code and complexed expressions. It takes just a little graphic sense to
obtain the same result.

I want to thank you so much for responding to my need. With your help, I
solved a big problem that had been bugging me for a very long...time.

Have a Blessed Day
jbeck2010

 

[Duane Hookom]

jbeck2010,
Thanks for sharing your experience it was not "simple minded".

What is even more cool is in step 2, if your main report is not maximized,
you can position it where you can see both the database window (reports) and
your main report in design view. Rather than drag the subreport control onto
the report, click and drag the actual subreport on to the main report.

Access is an amazing application and I am glad to hear you are experiencing
some success.

 

[jbeck2010]

Duane,

Thanks again for your additional information and encouragement.
I will definitely use your additional information on similar type reports.

Duane, you appear to be a very intelligent and knowledgeable Individual.
I really appreciate your help.

If you would be so kind as to help me with one more small problem.
I would like to have a form open and stay in maximized view.
I have tried a solution that was posted on the discussion group, but it
didn't work.
It was to type DoCmd.Maximize in the forms Open or Load event proceedure.
The result was that I received the following error message:

"Can't find the macro DoCmd."
The macro (or it's group) doesn't exist, or the macro is new but hasn't been
saved.

Am I doing something wrong or not doing something else that I should be doing.

Puzzled
jbeck2010

 

[Duane Hookom]

You are probably attempting to place a line of code into a property value.
To add code to an event procedure, click the drop down of the event property
and select [Event Procedure]. You can then click the [...] button to get to
the code window for that event.

To see how this might work, find the On Dbl Click event of the detail
section of a form. Drop down the list and select [Event Procedure]. Then
click the builder button [...] and change the code to look like:

Private Sub Detail_DblClick(Cancel As Integer)
MsgBox "You double-clicked the detail section", _
vbOKOnly + vbExclamation, "Dbl Click Event"
End Sub

Make sure you have a space to the left of the underscore in the MsgBox line
of code.

If you see responses in these news groups, most of the time we are
suggesting code entry.

 

[jbeck2010]

Duane,

As usual, you were right on the money. It got me to thinking about the code:
DoCmd.Maximize, I was not putting it where it was suppose to be. I was just
typing it in the "ON Open" property box. That is the reason for the error.
After reading your comments, In the "On Open" Property box I clicked on the
build button and it gave me a choice of expression builder, macro builder, or
code builder. I selected code builder and it took me to the code and placed
the curser in a specific spot. With my fingers crossed I typed in
Docmd.Maximize and closed the window. I closed the form and reopened it.
"Bam" It was Maximized. It works great!

Thanks again Duane for your guidance. "Your The Man"

I, also, have something further to report on the other problem I was having
about combining 5 different queries on 1 report. My alternative solution
worked great on the report, but when I opened the the original forms that
subform tool made the subforms from their graphics had been changed. It was a
mess. I corrected the changes to the forms and deleted the report and started
over.With your great advise in mind, I did it your cool way. I made the
original report the way I mentioned earlier and then minimized the windows to
take advantage of your drag and drop technique using the queries instead of
forms. The result was, it was much easier and faster to make the graphic
changes and placement of the labels and controls. The alignment and look of
the report is first class. No one would ever Know it was done that way. The
best part is nothing else changed and everything looks geat and works like a
champ.

Duane, I really appreciate you sharing your knowledge and you have my utmost
respect.

Have a Blessed Day,
jbeck2010

You are probably attempting to place a line of code into a property value.
To add code to an event procedure, click the drop down of the event property
and select [Event Procedure]. You can then click the [...] button to get to
the code window for that event.

To see how this might work, find the On Dbl Click event of the detail
section of a form. Drop down the list and select [Event Procedure]. Then
click the builder button [...] and change the code to look like:

Private Sub Detail_DblClick(Cancel As Integer)
MsgBox "You double-clicked the detail section", _
vbOKOnly + vbExclamation, "Dbl Click Event"
End Sub

Make sure you have a space to the left of the underscore in the MsgBox line
of code.

If you see responses in these news groups, most of the time we are
suggesting code entry.
--
Duane Hookom
MS Access MVP

Duane,

Thanks again for your additional information and encouragement.
I will definitely use your additional information on similar type reports.

Duane, you appear to be a very intelligent and knowledgeable Individual.
I really appreciate your help.

If you would be so kind as to help me with one more small problem.
I would like to have a form open and stay in maximized view.
I have tried a solution that was posted on the discussion group, but it
didn't work.
It was to type DoCmd.Maximize in the forms Open or Load event proceedure.
The result was that I received the following error message:

"Can't find the macro DoCmd."
The macro (or it's group) doesn't exist, or the macro is new but hasn't
been
saved.

Am I doing something wrong or not doing something else that I should be
doing.

Puzzled
jbeck2010