Multiple queries-Want only 1 report

G

Guest

Hi everyone! I'm using A02 on XP. Not a programmer but can work a little VB
in event procedures.

I have a report that is a bill for services rendered. We bill, rebill, and
then final bill.

My queries all have the same fields BUT I want to use just one report. I've
researched it here and found a few items but only saw one that said "do it
programmatically" and the reply was "ok" and one other that said to create a
form with the query list and create a union query for the report.

My logic path is this:...when I bill, the criteria includes [Billed] is No
and [ReBill] and [FinalBill] are Null (then [Billed] is updated to Yes); when
I rebill, the criteria includes [Billed] is Yes and [ReBill] and [FinalBill]
are Null (then [Rebilled] is updated to Yes); and lastly, [Billed] and
[Rebilled] are Yes and [FinalBill] is Null. In each case after the initial
billing I also filter out those where [Paid] is Yes.

Would a list box with the queries be best utilized with the "programmically"
or "union query" approach? Is there a better way for my example? I'm pretty
sure I understand the union query one but wanted some advice first.

Thank you all in advance for all the time you put into helping folks on the
newsgroups!
 
J

Jeff Boyce

Bonnie

Another approach might be to start with a form...

If you have a form from which the billings are "ordered", you could add
three controls corresponding to your "billed", "rebilled", "final" values.
Don't bind them to the underlying fields, just use them as containers.

Add a <Send Bill> command button that opens the report.

Now go to a single query and modify the Selection Criterion "cells" under
these three fields in your query. Point each at its respective
setting/container on the form, using something like:

Forms![YourOrderFormName]![YourBilledControlName]
and
Forms![YourOrderFormName]![YourReBilledControlName]
and
...

Use the query as the source for your report.

This will only work if the form is open. Open the form, set the Billed,
ReBilled and Final controls the way you want them. Click the button.

JOPO (just one person's opinion)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
K

krissco

Hi everyone! I'm using A02 on XP. Not a programmer but can work a little VB
in event procedures.

I have a report that is a bill for services rendered. We bill, rebill, and
then final bill.

My queries all have the same fields BUT I want to use just one report. I've
researched it here and found a few items but only saw one that said "do it
programmatically" and the reply was "ok" and one other that said to create a
form with the query list and create a union query for the report.

My logic path is this:...when I bill, the criteria includes [Billed] is No
and [ReBill] and [FinalBill] are Null (then [Billed] is updated to Yes); when
I rebill, the criteria includes [Billed] is Yes and [ReBill] and [FinalBill]
are Null (then [Rebilled] is updated to Yes); and lastly, [Billed] and
[Rebilled] are Yes and [FinalBill] is Null. In each case after the initial
billing I also filter out those where [Paid] is Yes.

Would a list box with the queries be best utilized with the "programmically"
or "union query" approach? Is there a better way for my example? I'm pretty
sure I understand the union query one but wanted some advice first.

Thank you all in advance for all the time you put into helping folks on the
newsgroups!
Click to expand...

I would do this using the WhereClause of the DoCmd.OpenReport method.
Open the report from a form where you select which type of bill you
are sending:

Form contains either an option group or a combo box or a list box. . .
The choice is yours! In addition to the report choice, include a "View
the Report" button. In the event procedure for "OnClick" of the
button, enter code similar to the following:

private sub ViewReport_Click()

'This example is for an option group

select case MyOptionGroupControl

case 1 'Bill
DoCmd.OpenReport "TheReportName", acViewPreview, , "Billed =
No and ReBill is null and FinalBill is Null"
case 2 'ReBill
DoCmd.OpenReport "TheReportName", acViewPreview, , "Billed =
Yes and ReBill is null and FinalBill is Null and not paid = Yes"
case 3 'FinalBill
DoCmd.OpenReport "TheReportName", acViewPreview, , "Billed =
Yes and ReBill = Yes and FinalBill is Null and not paid = yes"
case else
msgbox "uh-oh"
end select

end sub

That takes care of launching the report - only one more thing to take
care of - marking "Billed" and "ReBill" as YES prior to billing or
rebilling.

The simplest way to do this would be to create and save Update
queries:

qryUpdateBilled:
update SomeTable
Set billed = Yes
where billed = no
and rebill is null
and finalbill is null

.. . .

and then execute the query after opening your report:

docmd.open report . . . .
docmd.openquery "qryUpdateBilled"



Depending on how you view the report and when/if you print it, that
won't be a good method of updating the information. Alternatively, in
the "Close" event of the billed report, you could prompt the user and
open the update query:

private sub report_close()

'This will store the user's response
dim lngRet as long

if me.filter like "*Billed = No and ReBill is null and FinalBill is
Null*" then
lngRet = msgbox ("Do you want to update all items on this report as
billed?", vbYesNo)
if lngRet = vbYes then docmd.openquery "qryUpdateBilled"

else . . .

end sub


Good luck,

-Kris
 
G

Guest

Hi Kris,

Thank you SO much for the quick reply.

Quick question: in your example, my report's record source would be a query
that pulls data that fits ALL criteria sets? In other words, don't put any
billing criteria in the query itself? (My data comes from multiple tables.)

I already have separate buttons for the create fee and update records steps
on this one but I'm sure I can use your 'close event' suggestion elsewhere.

Thank you VERY much for your time!!!
--
Bonnie W. Anderson
Cincinnati, OH


krissco said:
Hi everyone! I'm using A02 on XP. Not a programmer but can work a little VB
in event procedures.

I have a report that is a bill for services rendered. We bill, rebill, and
then final bill.

My queries all have the same fields BUT I want to use just one report. I've
researched it here and found a few items but only saw one that said "do it
programmatically" and the reply was "ok" and one other that said to create a
form with the query list and create a union query for the report.

My logic path is this:...when I bill, the criteria includes [Billed] is No
and [ReBill] and [FinalBill] are Null (then [Billed] is updated to Yes); when
I rebill, the criteria includes [Billed] is Yes and [ReBill] and [FinalBill]
are Null (then [Rebilled] is updated to Yes); and lastly, [Billed] and
[Rebilled] are Yes and [FinalBill] is Null. In each case after the initial
billing I also filter out those where [Paid] is Yes.

Would a list box with the queries be best utilized with the "programmically"
or "union query" approach? Is there a better way for my example? I'm pretty
sure I understand the union query one but wanted some advice first.

Thank you all in advance for all the time you put into helping folks on the
newsgroups!
Click to expand...

I would do this using the WhereClause of the DoCmd.OpenReport method.
Open the report from a form where you select which type of bill you
are sending:

Form contains either an option group or a combo box or a list box. . .
The choice is yours! In addition to the report choice, include a "View
the Report" button. In the event procedure for "OnClick" of the
button, enter code similar to the following:

private sub ViewReport_Click()

'This example is for an option group

select case MyOptionGroupControl

case 1 'Bill
DoCmd.OpenReport "TheReportName", acViewPreview, , "Billed =
No and ReBill is null and FinalBill is Null"
case 2 'ReBill
DoCmd.OpenReport "TheReportName", acViewPreview, , "Billed =
Yes and ReBill is null and FinalBill is Null and not paid = Yes"
case 3 'FinalBill
DoCmd.OpenReport "TheReportName", acViewPreview, , "Billed =
Yes and ReBill = Yes and FinalBill is Null and not paid = yes"
case else
msgbox "uh-oh"
end select

end sub

That takes care of launching the report - only one more thing to take
care of - marking "Billed" and "ReBill" as YES prior to billing or
rebilling.

The simplest way to do this would be to create and save Update
queries:

qryUpdateBilled:
update SomeTable
Set billed = Yes
where billed = no
and rebill is null
and finalbill is null

.. . .

and then execute the query after opening your report:

docmd.open report . . . .
docmd.openquery "qryUpdateBilled"



Depending on how you view the report and when/if you print it, that
won't be a good method of updating the information. Alternatively, in
the "Close" event of the billed report, you could prompt the user and
open the update query:

private sub report_close()

'This will store the user's response
dim lngRet as long

if me.filter like "*Billed = No and ReBill is null and FinalBill is
Null*" then
lngRet = msgbox ("Do you want to update all items on this report as
billed?", vbYesNo)
if lngRet = vbYes then docmd.openquery "qryUpdateBilled"

else . . .

end sub


Good luck,

-Kris
Click to expand...
 
G

Guest

Hey Jeff!

Thanks bunches for the quick reply. I like the unbound controls usage.
Deinitely have a use for your advice.

I appreciate your time!
--
Bonnie W. Anderson
Cincinnati, OH


Jeff Boyce said:
Bonnie

Another approach might be to start with a form...

If you have a form from which the billings are "ordered", you could add
three controls corresponding to your "billed", "rebilled", "final" values.
Don't bind them to the underlying fields, just use them as containers.

Add a <Send Bill> command button that opens the report.

Now go to a single query and modify the Selection Criterion "cells" under
these three fields in your query. Point each at its respective
setting/container on the form, using something like:

Forms![YourOrderFormName]![YourBilledControlName]
and
Forms![YourOrderFormName]![YourReBilledControlName]
and
...

Use the query as the source for your report.

This will only work if the form is open. Open the form, set the Billed,
ReBilled and Final controls the way you want them. Click the button.

JOPO (just one person's opinion)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


Bonnie A said:
Hi everyone! I'm using A02 on XP. Not a programmer but can work a little VB
in event procedures.

I have a report that is a bill for services rendered. We bill, rebill, and
then final bill.

My queries all have the same fields BUT I want to use just one report. I've
researched it here and found a few items but only saw one that said "do it
programmatically" and the reply was "ok" and one other that said to create a
form with the query list and create a union query for the report.

My logic path is this:...when I bill, the criteria includes [Billed] is No
and [ReBill] and [FinalBill] are Null (then [Billed] is updated to Yes); when
I rebill, the criteria includes [Billed] is Yes and [ReBill] and [FinalBill]
are Null (then [Rebilled] is updated to Yes); and lastly, [Billed] and
[Rebilled] are Yes and [FinalBill] is Null. In each case after the initial
billing I also filter out those where [Paid] is Yes.

Would a list box with the queries be best utilized with the "programmically"
or "union query" approach? Is there a better way for my example? I'm pretty
sure I understand the union query one but wanted some advice first.

Thank you all in advance for all the time you put into helping folks on the
newsgroups!
Click to expand...
Click to expand...
 
K

krissco

Hi Kris,

Thank you SO much for the quick reply.

Quick question: in your example, my report's record source would be a query
that pulls data that fits ALL criteria sets? In other words, don't put any
billing criteria in the query itself? (My data comes from multiple tables.)
Click to expand...

Correct. In some cases, this approach destroys the speed of your query
(if retreiving ALL the data is slow, but due to indexes, pulling SOME
data
I already have separate buttons for the create fee and update records steps
on this one but I'm sure I can use your 'close event' suggestion elsewhere.

Thank you VERY much for your time!!!
Click to expand...

You're welcome.
 
K

krissco

Oops. I think I got 1/2 a post there (my infant hit the "Send"
button :)
Quick question: in your example, my report's record source would be a query
that pulls data that fits ALL criteria sets? In other words, don't put any
billing criteria in the query itself? (My data comes from multiple tables.)
Click to expand...

Yes. Correct. One warning: this can slow down your query (if it is
quick when pulling SOME data due to indexes, but slow pulling ALL data
due to massive data sets) so it is not always a practical way to
design a report.

-Kris
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pass Dialog Box entry to new Query Dialog Boxes 5
numbering a report 2
Create report using two reports 1
checkbox in table to show records in report 2
If then Statement 6
aggregate sql 1
Multiple Queries-Want only 1 Report 3
Access Dcount (multiple criteria) 3

Top