If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple queries-Want only 1 report
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! -- Bonnie W. Anderson Cincinnati, OH |
#2
|
|||
|
|||
Multiple queries-Want only 1 report
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Bonnie A" wrote in message ... 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! -- Bonnie W. Anderson Cincinnati, OH |
#3
|
|||
|
|||
Multiple queries-Want only 1 report
On Aug 10, 4:38 am, Bonnie A
wrote: 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! -- Bonnie W. Anderson Cincinnati, OH 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 |
#4
|
|||
|
|||
Multiple queries-Want only 1 report
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" wrote: On Aug 10, 4:38 am, Bonnie A wrote: 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! -- Bonnie W. Anderson Cincinnati, OH 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 |
#5
|
|||
|
|||
Multiple queries-Want only 1 report
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" wrote: 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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Bonnie A" wrote in message ... 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! -- Bonnie W. Anderson Cincinnati, OH |
#6
|
|||
|
|||
Multiple queries-Want only 1 report
On Aug 10, 6:54 am, Bonnie A
wrote: 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.) 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!!! You're welcome. |
#7
|
|||
|
|||
Multiple queries-Want only 1 report
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.) 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 |
Thread Tools | |
Display Modes | |
|
|