Results 1 to 9 of 9

Thread: VBA help

  1. #1
    Join Date
    Apr 2009
    Posts
    15

    Unanswered: VBA help

    I'm currently trying to sort a report on the go through use of code i found on a webpage and I'm trying to apply it to my report, however I'm encountering a few problems.

    The website I'm using is "http://www.databasedev.co.uk/sorting_reports.html" and I want my form "Form I'm trying to use" to have similar results to what the website gives using my student I.D, Last Name, First Name, and Graduation Year as the sorting criteria instead of what the website gives.

    But, I'm having problems getting the code to work correctly and I'm not sure if I'm having basic problems or more complex problems.

    Please help a newb at VBA

    I know the database is a little unorganized at the moment, but it will be fixed in the future. Personal Information has been changed
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2009
    Posts
    15
    And this is what the website gives and what I'm trying to mimic
    Attached Files Attached Files

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so make certain you set appropriate sorting in the report grouping tab
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2009
    Posts
    15
    What do you mean?

    I'm trying to use the form as a way to sort my report, not pre-sort the report. In a sense, I'm trying to be able to sort the report "on the go" using this form as a basis that can be easily used by even novice users instead of preset grouping/sorting options.

    My question revolves around the difference between these modules and how i can edit the second one that i got from the webpage in order to use for my report "student reports." This is my coding so far:

    Code:
    Private Sub Command14_Click()
    
        Dim strSQL As String, intCounter As Integer
        'Build strSQL String
        For intCounter = 1 To 4
            If Me("cboSort" & intCounter) <> "" Then
            strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
            If Me("Chk" & intCounter) = True Then
                strSQL = strSQL & " DESC"
            End If
            strSQL = strSQL & ", "
        End If
        Next
    
        If strSQL <> "" Then
            'Strip Last Comma & Space
            strSQL = Left(strSQL, (Len(strSQL) - 2))
            'Set the OrderBy property
            Reports![student reports].OrderBy = strSQL
            Reports![student reports].OrderByOn = True
        Else
            Reports![student reports].OrderByOn = False
            
        End If
    
    End Sub
    
    
    Private Sub cmdClear_Click()
    
    Dim intCounter As Integer
    
    For intCounter = 1 To 4
        Me("cboSort" & intCounter) = ""
        Me("Chk" & intCounter) = ""
    Next
    
    End Sub
    
    
    Private Sub Command27_Click()
     DoCmd.Close acForm, Me.Form.Name
    End Sub
    Private Sub Form_Close()
    DoCmd.Close acReport, "student reports"
    DoCmd.Restore
    End Sub
    
    
    Private Sub Form_Open(Cancel As Integer)
    'Open the form, in datasheet view
    'Open the report, maximized, in Print Preview
        DoCmd.OpenReport "student reports", acViewPreview
        DoCmd.Maximize
    End Sub

    And the code found in the example I'm trying to use at Sorting Microsoft Access Report Fields | Database Solutions for Microsoft Access | databasedev.co.uk

    Code:
    For intCounter = 1 To 6
            If Me("cboSort" & intCounter) <> "" Then
            strSQL = strSQL & "[" & Me("cboSort" & intCounter) & "]"
            If Me("Chk" & intCounter) = True Then
                strSQL = strSQL & " DESC"
            End If
            strSQL = strSQL & ", "
        End If
        Next
    
        If strSQL <> "" Then
            'Strip Last Comma & Space
            strSQL = left(strSQL, (Len(strSQL) - 2))
            'Set the OrderBy property
            Reports![rptStudentInformation].OrderBy = strSQL
            Reports![rptStudentInformation].OrderByOn = True
        Else
            Reports![rptStudentInformation].OrderByOn = False
            
        End If
    
    End Sub
    
    
    Private Sub cmdClear_Click()
    
    Dim intCounter As Integer
    
    For intCounter = 1 To 6
        Me("cboSort" & intCounter) = ""
        Me("Chk" & intCounter) = ""
    Next
    
    
    End Sub
    
    Private Sub cmdClose_Click()
        DoCmd.Close acForm, Me.Form.Name
    End Sub
    
    Private Sub Form_Close()
    DoCmd.Close acReport, "rptStudentInformation"
    DoCmd.Restore
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    'Open the report, maximized, in Print Preview
        DoCmd.OpenReport "rptStudentInformation", acViewPreview
        DoCmd.Maximize
    End Sub
    So basicly I'm trying to remake the code found at the webpage in order to use it on my report, "student reports," in my database.

    Please help

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is stopping you setting the sort order as part of the report design, rather than do this through code
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2009
    Posts
    15
    Instead of using a preset, set-in-stone sort i want the user to be able to sort the report on the spot easily by their preferences so that they can sort it exactly how they want, which is why I can't use a normal sort that may be too difficult for some users to change.

    I'm mainly trying to give more versatility to the report for the user's benefit.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so know I understand why you want to use code, rahter than doing it the easy way

    so have you any group footers in the report.. if so that will cause problems
    what SQL string have you got as a result of your function call

    personally I'd expect that sort of code to be in the reports on open (or soemthing similar) function.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2009
    Posts
    15
    I don't think i have any group footers, but I'm not exactly sure. And I'm not exactly sure where to find the SQL string for it.

    If you could view the database and the example that the webpage that I'm using gives you might be able to better understand what I'm trying to do as it's kinda difficult to explain with my lack of access knowledge.

    Thanks for your help so far

  9. #9
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    VBA Help

    I am not sure of the environment in which this report operates, but perhaps it might be possible to create a standing query which contains the sorting criteria and reference the query as the data source. Then you could, for instance, have a button or combo box which resorts the query on demand and refreshes the report, which only needs to display the data as presented. The point is, from the report's point of view, it never has to get engaged in reworking the data. This may be useful when dealing with data from a distant source.

Posting Permissions

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