Unanswered: Print Report Using Cascading Combo Boxes on Form
I know there is someone out there smart enough to figure this out....but I sure cannot. I've been working on this portion for 2 months now - UGH!
I have a Form (PrintReports) that uses Cascading Combo Boxes to 1) Choose the Report (GM Financial, GM List, GM Log), 2) Select the Year (ALL, 2008-2009, 2009-2010, etc), and then 3) Select the Sort Option (Name, Number, Code, Date). From there I wish to either Preview or Print the report selected in the first combo box.
I've attached the database....can someone PLEASE help me in figuring this out? I'm sure you'll need to make changes to my coding.
My codes are completely messed up (including names) as I have been trying different code options with nothing working. I am sorry for that - I should have cleaned it up before posting. I was just so frustrated, I couldn't see straight. Let me see if I can explain this a little better....
When the PRINTREPORTS form is loaded, I would like to select one of the 3 different reports. Once a report is selected, then I would like to select the "school year" for for that report. And finally, sort that report either by "Project Name", "Project Number", "GM Code", or "Budget Start Date".
I should have named the Combo Boxes "SelectReport", "SelectYear", and "Select Sort".
I would then like to Print or Print Preview the report.
Is this possible? It seems like it should be....maybe I'm just way off base.
I'd really appreciate any assistance you can give me.
If intNumOfReports <> 0 Then
Me![SelectReport].RowSourceType = "Value List"
Me![SelectReport].LimitToList = True
For intRptCounter = 0 To intNumOfReports - 1
strBuild = strBuild & CurrentDb.Containers("Reports").Documents(intRptCo unter).Name & ","
Me![SelectReport].RowSource = Left$(strBuild, Len(strBuild) - 1)
Private Sub SelectReport_Click()
If Not IsNull(Me![SelectReport]) Then
DoCmd.OpenReport Me![SelectReport], acViewPreview
Seems to work correctly, but I'm lost after that. Not sure if you were telling me to open another form or another report. At this point all I want to do is select the school year for the report to print and the sort option for how the report is supposed to print.
Well, much easier than all that would be a row source of the combo of:
SELECT Name FROM msysObjects
WHERE Type = -32764
which should return all reports. As to the other, I was simply recommending using the wherecondition argument of OpenReport to restrict to the year chosen. Dynamic sorting I do in the open event of the report, like:
I think I have everything working correctly, excluding the 3rd combo box which would select the sort option (Project Name, Project Number, GM Code, School Year). I have these options as a Value List, listed in the Row Source as "Project Name";"Project Number";"GM Code";"School Year"
Below is the code I have on my Print Preview Button - which again, seems to be working correctly. Now how do I had the sort option combo box to this code?
Private Sub PrintPreviw_Click()
If Not IsNull(SelectReport) And SelectReport <> "" Then
DoCmd.OpenReport SelectReport, acViewPreview, , "Grant.SchoolYear = '" & Me.SelectYear & "'"
MsgBox ("You Must First Select a Report To Print!")
SelectReport = ""
We are almost there....once the sort option is achieved, the DB will be completed....and I couldn't have done any of this without your assistance!
Well, I tested on the Financial report. As the error mentions, there is no existing sort set up. I guess I should have mentioned that there needs to be. Once you set it to sort on something (anything), that code will change the sort to whatever is chosen on the form.