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.

What are you stuck on? What's not working? For starters, in the after update event of the report combo (ambiguously named Combo351) you set the row source for the year combo, which is fine, but:

"WHERE School_Year = '" & Me.cbo351 & "' ORDER BY School_Year"

A) Combo351 doesn't contain a year
B) It isn't named cbo351

Frankly, report and year don't seem like candidates for cascading combo boxes. They would be independent of each other.

Paul-
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.

Jeannie

I need to leave for a few minutes but none of this is too difficult. If the combo contained actual report names (it doesn't) you'd just do:

DoCmd.OpenReport Me.ComboName,...

To restrict the report to a given year you can do this:

Open a second form to the record

See if that gets you going and I'll be back in a bit.

Paul-
OK...this is what I have so far (based on Form=PrintReports, 1st Combo=SelectReport, 2nd Combo=SelectYear, 3rd Combo=SelectSort....

Private Sub Form_Open(Cancel As Integer)

Dim intNumOfReports As Integer
Dim intRptCounter As Integer
Dim strBuild As String

intNumOfReports = CurrentDb.Containers("Reports").Documents.Count

Me![SelectReport].LimitToList = True

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 & ","
Next
Me![SelectReport].RowSource = Left$(strBuild, Len(strBuild) - 1)
End If

End Sub

and....

Private Sub SelectReport_Click()
If Not IsNull(Me![SelectReport]) Then
DoCmd.OpenReport Me![SelectReport], acViewPreview

End If
End Sub


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:

Me.GroupLevel(0).ControlSource = "CarType"

Paul-
Your assistance has been a life-saver! You are awesome!

I was unable to work on this DB over the weekend. The district was doing maintenance on the server so I had no access to my files.

I am back in the office today, so I will be working on this again today. I will be starting where we left off on Friday.

I have the rowsource of the combo box to select all reports using....
SELECT Name FROM msysObjects WHERE Type = -32764. It works...YAY!

Again, I am lost as to how to use the next two combo boxes (Select Year and Select Sort) to Print the Selected Report in the first combo box.

Paul-
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 & "'"
Else
MsgBox ("You Must First Select a Report To Print!")
SelectReport.SetFocus
End If
SelectReport = ""
End Sub

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!

Did you see this?

You would use the combo instead of the hard-coded field name.

Yes, I did see that - but honestly have no clue where to put it or how to code it exactly. I need just a little bit more assistance from you....PLEASE.

You have all the pieces:

Me.GroupLevel(0).ControlSource = Forms!FormName.SortComboName

Which of course assumes that the combo contains field names.

OMG....I just cannot get this to work....what am I doing wrong??

Private Sub Report_Open(Cancel As Integer)
Me.GroupLevel(0).ControlSource = Forms!PrintReports.SelectSort
End Sub

Error states there is no sorting or grouping field or expression defined for the group level number used.

This is my Row Source for the SelectSort Combo Box as a Value List:
"ProjectName";"ProjectNumber";"GMCode";"SchoolYear "

What am I missing? I am really sorry I am not getting this.....

Can you post the db?

Here is the database. I hope you can figure out what I am doing wrong.

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.