Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    Join Date
    Jan 2012
    Posts
    23

    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.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  3. #3
    Join Date
    Jan 2012
    Posts
    23
    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

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  5. #5
    Join Date
    Jan 2012
    Posts
    23
    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.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  7. #7
    Join Date
    Jan 2012
    Posts
    23
    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.

  8. #8
    Join Date
    Jan 2012
    Posts
    23
    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!

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you see this?

    Quote Originally Posted by pbaldy View Post
    Dynamic sorting I do in the open event of the report, like:

    Me.GroupLevel(0).ControlSource = "CarType"
    You would use the combo instead of the hard-coded field name.
    Paul

  10. #10
    Join Date
    Jan 2012
    Posts
    23
    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.

    Quote Originally Posted by pbaldy View Post
    Did you see this?


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

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You have all the pieces:

    Quote Originally Posted by renickj View Post
    honestly have no clue where to put it
    Quote Originally Posted by pbaldy
    in the open event of the report
    Me.GroupLevel(0).ControlSource = Forms!FormName.SortComboName

    Which of course assumes that the combo contains field names.
    Paul

  12. #12
    Join Date
    Jan 2012
    Posts
    23
    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.....

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db?
    Paul

  14. #14
    Join Date
    Jan 2012
    Posts
    23
    Here is the database. I hope you can figure out what I am doing wrong.
    Attached Files Attached Files

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •