Choosing Report

[ShadeRF]

I have a macro that runs two queries then opens a report. I'd like to have it choose between two reports based on information in the query.

I have a column Brand in the query. If the query returns BrandX I want to open report 1 and if it returns BrandY i want to open report 2. How can I accomplish this? Thanks!

[hertfordkc]

I have a macro that runs two queries then opens a report. I'd like to have it choose between two reports based on information in the query.

I have a column Brand in the query. If the query returns BrandX I want to open report 1 and if it returns BrandY i want to open report 2. How can I accomplish this? Thanks!

-------------------------------------------------------------------------

http://office.microsoft.com/en-us/ac...005188429.aspx

[ShadeRF]

I put the condition in the WHERE box, but I believe this doesn't actually decide if the report opens or not. On my Macro I have two openreport peices, but I only want one to open based on what is in the Brand field on the Query.

[hertfordkc]

Can post just the macro text and the query SQL?

[ShadeRF]

OpenQuery
QueryName RetailUpdateQuerySS
View Datasheet
Data Mode Edit

OpenQuery
QueryName PricingQuerySS
View Datasheet
Data Mode Edit

OpenReport
Report Name PricingReportSSDGB
View Report
Window Mode Normal

OpenReport
Report Name PricingReportSS
View Report
Window Mode Normal

[hertfordkc]

Have you made some choice prior to running the macro that sets conditions on one of the queries which will ultimately determine "BrandX", "BrandY" or something else? But you don't know the result until the query is run?
And why two queries? if the decision is based on only one of the queries? Should you have combined the queries into one?
Your summary of the macro didn't give any indication of testing for conditions in either query??
I try to help with the problem presented, i.e. making a choice on which report to run, but suddenly I find myself asking "Why are you doing things this way?"
For example, you could have a form (maybe hidden) that is based on the query which provides the decision making info, which could then select which report to run.


Thread: Choosing Report Reply to Thread

[ShadeRF]

Initially there is a form and the user selects the Brand and inputs a price. Once this is done they click a Run button. The first query updates a Price field for the corresponding brand on the table, and the 2nd Query runs a series of formulas for that Brand. Based on the brand though I want a different report to show. They contain the same data from the query but the reports are formatted differently.

[hertfordkc]

Initially there is a form and the user selects the Brand and inputs a price. Once this is done they click a Run button. The first query updates a Price field for the corresponding brand on the table, and the 2nd Query runs a series of formulas for that Brand. Based on the brand though I want a different report to show. They contain the same data from the query but the reports are formatted differently.

__________________________________________________ _______________________

From our earlier posts, I conclude that the once the user selects the Brand, you effectively know which report to run. Therefore, the form could at that point run one of two macros. Both macros would run the queries, but one macro would run report A and the other Macro would run report B. There are other ways to effect this, but this is the simplest IMHO.

[ShadeRF]

Would I do that in VBA? Something like IIF Brand LIKE "BRANDX*", Macro1, Macro2
?

[hertfordkc]

Would I do that in VBA? Something like IIF Brand LIKE "BRANDX*", Macro1, Macro2
?

Based on your responses, I don't see any reason why you shouldn't have the Brand onClick event trigger the selection.

[ShadeRF]

I don't think I quite follow...

[ShadeRF]

I tried doing it this way onclick event procedure but I get a syntax error.

IIf (([BRAND]) Like "BRANDX*") Or ([BRAND] Like "BRANDY*"), DoCmd.RunMacro PricingMacro1, DoCmd.RunMacro PricingMacro2)

[hertfordkc]

I tried doing it this way onclick event procedure but I get a syntax error.

IIf (([BRAND]) Like "BRANDX*") Or ([BRAND] Like "BRANDY*"), DoCmd.RunMacro PricingMacro1, DoCmd.RunMacro PricingMacro2)

I don't think you can use DoCmd in the Expression Builder. And you'll probably need to do the a string comparison instead of using "Like".
IIF is a function and returns a result, so using it in the expression builder is problematic.
Suggest you go the OnClick property field, empty it and then click on the ellipsis (...) and select code builder. The Sub procedure will appear and you can do the necessary comparisons and DoCmd in there.