Report Question: User Chosen Criteria and Sort 2

[Yarcadian]

One of my current projects is a compliance db. The user's want the ability to decide which criteria they want to use for any given report and the ability to sort the report by any field.

For Example: Say there is a Vendor Compliance Report, the user's want to decide if they want All Vendors, a Specific Vendor, Vendors for a Specific Buyer...they also want the ability to decide how the report should be sorted. Rather than write multiple reports with the same data format, I would like to write one report and give them the option to decide which criteria to use and how to sort it.

Any help would be greatly appreciated....TIA

 

[BobStubbs]

Here is some example code from one of my databases which may help you. It is based on a table containing fields 'EmployeeID' and 'EmployeeName', but you can use it for any table and any number of sort fields.

First, define two global variables in a module:

Public stChooserID As String
Public stSortBy As String

Next, create a 'selection form' with three controls:

A Command Button - btnRunReport
Two combo boxes - cmbSortOrder, cmbChooseEmployee

I used the wizard to create cmbChooseEmployee, so it displays a list of all the EmployeeID and EmployeeName values from my table.

cmbSortOrder just contains two values which I typed in:

By ID
By Name

I used the wizard to create the command button as an 'Open Report' button which opens my report. I then changed the click event code for the button:

On Error GoTo Err_btnRunReport1_Click

    Dim stDocName As String

    cmbSortOrder.SetFocus
    Select Case cmbSortOrder.Text
    Case Is = "By ID"
        stSortBy = "[EmployeeID]"

    Case Is = "By Name"
        stSortBy = "[EmployeeName]"

    Case Else
        MsgBox "Please choose a Sort Order"
        Exit Sub
    End Select
    
    If IsNull(cmbChooseEmployee) Then
        stChooserID = ""
    Else
        stChooserID = "[EmployeeID]='" & _
        cmbChooseApplication & "'"
    End If
    
    stDocName = "MyReportName"
    
    DoCmd.OpenReport stDocName, acPreview

Exit_btnRunReport1_Click:
    Exit Sub

Err_btnRunReport1_Click:
    MsgBox Err.Description
    Resume Exit_btnRunReport1_Click
    
End Sub

Now write this code into the On Open event of the report:

Private Sub Report_Open(Cancel As Integer)
    Me.OrderBy = stSortBy
    Me.OrderByOn = True

    If stChooserID > "" Then
        Me.Filter = stChooserID
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
End Sub

When you test this, you should see this behaviour when you click the 'run report' button:

-- Choose 'By ID' as the sort order, leave the 'Employee Name' combo box blank .... see a list of all Employees, sorted by ID
-- Choose 'By Name' as the sort order, leave the 'Employee Name' combo box blank .... see a list of all Employees, sorted by name
-- Choose an Employee name from the combo box ... see just one record listed, for this Employee

I hope that this will give you some clues ... using this technique you should be able to build a report whicih will list details for one Vendor or all vendors, sorted by any column which you choose.


Bob Stubbs

 

[Yarcadian]

Thanks Bob, this really helps me out. I have been trying to make it too complex and was getting nowhere fast.

David

 

[grnzbra]

It seems that if you had 20 or 30 possible things to chose to select on, that would get out of hand.

You might want to try code to loop through all the controls on the criteria form and each time it encounters one with an entry it adds its value to a string plus the word "and". After it's all done, use the Left and Len functions to get rid of the last "and"

Now use the string to create a query def which will drive the report.

You might be able to do something similar for sorting by using the OrderBy property of the report.

 

[Yarcadian]

grnzbra: I have not had a great deal of success when working with loops, but I like the idea of generating a query def based on one. I am going to have to try again..Thanks

David

 

[fredk]

Bob - Thanks for this post - it helped to point me in the right direction - I dropped the select case and just used the value of the combo box (my field names are descriptive so it won't be confusing for the user.

Thanks!

Fred