Small Report Annoyances

[June7]

Let me explain the visibility code. The left side of the first = sign is reference to the control and its Visible property which must be set to either True or False. The right side of the first = sign is an expression that will evaluate to either True or False. That structure won't work for setting the RowSource because it does not take a Boolean True or False value. RowSource is a query.



What I suggested is VBA, not SQL.

I am not sure which event is best. Try the Load event you have and replace the code.

In place of 'do this set the graph RowSource. The SQL statement must be within quote marks. The Case Else is not actually required.

Me.Graph0.RowSource = "SELECT animalquery.State AS Expr1, Count(*) AS [Count] FROM animalquery GROUP BY animalquery.State ORDER BY animalquery.State DESC;"

[james28]

June,

Thank you for the explanation! That made everything work a treat!


-James

[james28]

June,


Sorry to follow up again. I thought everything was worked out, but I seem to have run into a problem.


Everything works just fine when I go into all of the different report views, except print preview.

When I go to print preview, it says, "Run-Time error '2455': You entered an expression that has an invalid reference to the property RowSource.


When I open the debugger, it has Me.Graph0.RowSource = "SELECT animalquery.Animal AS Expr1, Count(*) AS [Count] FROM animalquery GROUP BY animalquery.Animal ORDER BY animalquery.Animal DESC;" highlighted.


Here is the entire code that I am using:

Private Sub Report_Load()
Select Case Forms!testform.ComboAnimalType
Case "Cat"
Me.Graph0.RowSource = "SELECT animalquery.Animal AS Expr1, Count(*) AS [Count] FROM animalquery GROUP BY animalquery.Animal ORDER BY animalquery.Animal DESC;"
Case "Dog"
Me.Graph0.RowSource = "SELECT animalquery.State AS Expr1, Count(*) AS [Count] FROM animalquery GROUP BY animalquery.State ORDER BY animalquery.State DESC;"
Case Else
Me.Graph0.RowSource = "SELECT animalquery.State AS Expr1, Count(*) AS [Count] FROM animalquery GROUP BY animalquery.State ORDER BY animalquery.State DESC;"
End Select
End Sub




Thank you again!

[June7]

I've never tried setting graph RowSource with code. Maybe not possible in PrintPreview.

Maybe should just stick with having separate graph objects and setting graph visibility properties. Can have graph objects sit on top of each other.

Or why not just show them all to begin with?

Why would you want the State graph if Dog is selected?

[james28]

June,

Thank you for the reply.

I tried to simplify my sample db, which is why it says "Dog" and "Cat". Really, I will have reports being filtered by a combobox that contains street, city, state or country.


I originally planned to have the graphs stacked on top of each other, as you suggested. The problem is, I have about 15 graphs that will show regardless of whether someone picks street, city, state, or country. I then have 4 graphs that will change, based on what is selected. Since there will be 4 versions of each of the 4 graphs, that is 12 more graphs than would actually be needed, if I could get this to work in print preview. If it was just a few, I would not worry as much.



I did some google'ing and someone said that the underlying query must be changed, but I am not sure how to make that happen. Does that make any sense to you? http://social.msdn.microsoft.com/For...orum=accessdev


Thank you again!

[June7]

The underlying query referenced in that link is report RecordSource. Would not affect graph RowSource.

I don't want to build something to test this. If you want to provide your db, follow instructions at bottom of my post.

[james28]

June,

Thank you for the reply. I could not find any thing online referencing RowSource print preview, and run time error 2455, so I was hoping that would get us closer to understanding the issue.


I have attached a simplified version of the database. Database114.accdb

On the form, "Cat" shows the graph grouped by animal. Anything else shows the graph grouped by state.



As of right now, it works perfectly in all situations except when you go to print preview to print it out. I tried to put it in Open instead of Load, but that did not work at all. It did not give any errors, it just no longer controlled the graph. I took that to mean "open" was too early to edit the row source.



Thank you for any help or guidance that you can provide!

[June7]

This seems to work.

Set the graph RowSource to:

SELECT IIf(Forms!testform.ComboAnimalType="Cat",[Animal],[State]) AS Data, Count(*) AS [Count] FROM animalquery GROUP BY IIf(Forms!testform.ComboAnimalType="Cat",[Animal],[State]) ORDER BY IIf(Forms!testform.ComboAnimalType="Cat",[Animal],[State]) DESC;

Delete the VBA.

[james28]

June,

Thank you again for the reply. If I am understanding what you have written correctly, it basically says, if cat filter by animal. If anything else, filter by state.

This basically provides for two outcomes. I realize that is how I had it set in my DB, having only two unique outcomes, but my actual report will have 3 sets of outcomes, which is why I was using Cat, Dog, and All. Is there some way that I can specify 3 outcomes? Cat, Dog, and else (all)?



Thank you again!

[June7]

Can nest another IIf

IIf(Forms!testform.ComboAnimalType="Cat", [Animal], IIf(Forms!testform.ComboAnimalType="Dog",[fieldname], [another fieldname]))

or use Switch() function.

Switch(Forms!testform.ComboAnimalType="Cat", [Animal], Forms!testform.ComboAnimalType="Dog", [fieldname], Forms!testform.ComboAnimalType="All", [another fieldname]) AS Data

Instead of referencing the form control, could try passing the value of combobox to report with OpenArgs argument of OpenReport.

Then

Switch([OpenArgs]="Cat", [Animal], [OpenArgs]="Dog", [other fieldname], [OpenArgs]="All", [another fieldname]) AS Data