Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    San Diego

    Unanswered: Option Group on Form sorting report data

    I am attempting to sort a report based on a combo box on a form. It is not working!! Here is the code I have so far:

    Private Sub cmdRun_Dispatch_Report_Click()
    On Error GoTo Err_cmdRun_Dispatch_Report_Click
        Dim stDocName As String
        Dim intResponse As Integer
        Dim strSQL As String
        Dim strSort As String
        DoCmd.SetWarnings False
        intResponse = MsgBox("This report may take several minutes to create.  Do you wish to continue?", vbOKCancel + vbInformation)
                    If intResponse = 2 Then Exit Sub 'User Canceled
        DoCmd.OpenQuery "qryMtbl_Hull", , acReadOnly
        DoCmd.OpenQuery "qryMTBLUNIONqryMSTRs", , acReadOnly
        DoCmd.OpenQuery "qryUPDATEMtblMSTR", , acReadOnly
        Select Case frmSortOptions
            Case 1 'UBZ sort
                strSort = "ZONE"
            Case 2 'PLN_C(default sort)
                strSort = "PLN_C"
            Case 3 'Decide sort
                strSort = "Decide"
            Case 4 'PLN_S sort
                strSort = "PLN_S"
            Case 5 'ECD sort
                strSort = "ECD"
            Case 6 'T_ACT_S sort
                strSort = "T_ACT_S"
            Case 7 'T_PLN_C sort
                strSort = "T_PLN_S"
            Case 8 'Delivered sort
                strSort = "Delivered"
            Case Else
                MsgBox "No sort order selected!", vbExclamation
        End Select
        strSQL = "SELECT qryTimsa_Final.*" & _
                " FROM qryTimsa_Final" & _
                " ORDER BY qryTimsa_Final." & strSort & ", qryTimsa_Final.txtwp, qryTimsa_Final.ZONE DESC;"
        Debug.Print strSQL
        DoCmd.OpenQuery "qrymtblReportData_Final", , acReadOnly 'this is the qryTimsaFinal rows
        'DoCmd.OpenQuery "qryMtblGBHolds", , acReadOnly  this is really slow at the moment
        DoCmd.OpenQuery "qryMtblHolds", , acReadOnly
        DoCmd.OpenQuery "qryMtblAll_Holds", , acReadOnly
        stDocName = "rptDispatch_Pkgs"
        DoCmd.OpenReport stDocName, acPreview
        If Err <> 0 Then
                Select Case Err
                    Case 2501 'Canceled action
                        Resume Next
                    Case Else
                        MsgBox Err.Number & ":  " & Err.Description, vbCritical, Me.Name & " frmSortOptions_AfterUpdate()"
                End Select
            End If
        DoCmd.SetWarnings True
    End Sub
    Nothing is being sorted when the report runs. It is haphazardly spewing data! Within the report properties, I have the order by empty and turned off.

    I am probably barss-ackwards on some of this, you may be as harsh as you want, all criticism is accepted!!!!

    Thanks a million!

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Two things that I see..

    Do you have grouping levels defined in your report? Grouping levels will override any ORDER BY clause specified in the query.

    Secondly, you can reference a column by it's integer instead of it's name, thereby eliminating A LOT of bloat. Just make sure the fields in teh query are in the same order as the choices in teh combo box, then you can use something like this:

    " ORDER BY ' & yourCombo.ListIndex + 1
    Last edited by Teddy; 04-16-04 at 12:57.

  3. #3
    Join Date
    Sep 2001
    Chicago, Illinois, USA
    Is frmSortOptions the name of the combobx.

    If so, just move that whole select into the OnOpen of the report and use it to set the OrderBy and OrderByOn properties of the report object.

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Mar 2004
    San Diego
    Doh!!! I posted my topic wrong. It is an option group. That is what the switch statement is referring to. Sorry about that, it is still early, the coffe hasn't kicked in yet!

    I don't have any grouping within the report, but I do have a subreport that is being grouped. Will that affect this? I don't think so, but I am unsure here.
    Good to know about the integer reference. Thanks for that tidbit.

    Do I need to use DAO to do this? I am a beginner and am a little unsure of what is needed to get this accomplished.


  5. #5
    Join Date
    Mar 2004
    San Diego

    I suppose that referencing by integer will not work if I am not including all fields from the query within the sort option group. Is there a way to work around this?

Posting Permissions

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