When I open a report using vba and apply a filter I lose the ability to sort.

[jpreator]

I have a report that has three components: department, shift, and a calculated field. I am grouping by department and shift and sorting the calculated field in descending order. When I just open the report it works great. When I use a form with checkboxes that allows the user to choose which departments to include in the report it stops sorting by the calculated field. It will not even let me manually sort by that field. What have I messed up?

[zmbd]

sounds like could be something in the calling code; however, there can be other causes one such being if the value of the text box is calculated in the form/report instead of in the underlying recordset. Another is if there are conflicting names between the form and the report controls... scope should limit this; however, stranger things have happened.

Please post the code that loads your report from the form (cut and paste), please use the [CODE/] format by selecting the posted script and clicking on the [CODE/] format tool.

-z

[jpreator]

Private Sub cmdOpenReport_Click()
    Dim strFilter As String
    Dim lngLen As Long
 
    'Identify which departments are selected for the filter string
    If Me.chkCFAA = -1 Then
        strFilter = strFilter & "(DeptName = """ & "CFAA" & """) Or "
    End If
 
    If Me.chkCFMP = -1 Then
        strFilter = strFilter & "(DeptName = """ & "CFMP" & """) Or "
    End If
 
    If Me.chkPlating = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Plating" & """) Or "
    End If
 
    If Me.chkSPEER = -1 Then
        strFilter = strFilter & "(DeptName = """ & "SPEER" & """) Or "
    End If
 
    If Me.chkPA = -1 Then
        strFilter = strFilter & "(DeptName = """ & "PA" & """) Or "
    End If
 
    If Me.chkPMP = -1 Then
        strFilter = strFilter & "(DeptName = """ & "PMP" & """) Or "
    End If
 
    If Me.chkChemistry = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Chemistry" & """) Or "
    End If
 
    If Me.chkRFAA = -1 Then
        strFilter = strFilter & "(DeptName = """ & "RFAA" & """) Or "
    End If
 
    If Me.chkRFMP = -1 Then
        strFilter = strFilter & "(DeptName = """ & "RFMP" & """) Or "
    End If
 
    If Me.chkRFP = -1 Then
        strFilter = strFilter & "(DeptName = """ & "RFP" & """) Or "
    End If
 
    If Me.chkAccounting = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Accounting" & """) Or "
    End If
 
    If Me.chkCI = -1 Then
        strFilter = strFilter & "(DeptName = """ & "CI" & """) Or "
    End If
 
    If Me.chkEngineering = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Engineering" & """) Or "
    End If
 
    If Me.chkEnvironmental = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Environmental" & """) Or "
    End If
 
    If Me.chkHR = -1 Then
        strFilter = strFilter & "(DeptName = """ & "HR" & """) Or "
    End If
 
    If Me.chkIT = -1 Then
        strFilter = strFilter & "(DeptName = """ & "IT" & """) Or "
    End If
 
    If Me.chkMachineShop = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Machine Shop" & """) Or "
    End If
 
    If Me.chkMaintenance = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Maintenance" & """) Or "
    End If
 
    If Me.chkPurchasing = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Purchasing" & """) Or "
    End If
 
    If Me.chkQA = -1 Then
        strFilter = strFilter & "(DeptName = """ & "QA" & """) Or "
    End If
 
    If Me.chkRandD = -1 Then
        strFilter = strFilter & "(DeptName = """ & "R&D" & """) Or "
    End If
 
    If Me.chkSafety = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Safety" & """) Or "
    End If
 
    If Me.chkSales = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Sales" & """) Or "
    End If
 
    If Me.chkShipping = -1 Then
        strFilter = strFilter & "(DeptName = """ & "Shipping" & """) Or "
    End If
 
    'Trim any trailing " AND " from the filter string
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strFilter) - 4
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "Please select a department"
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strFilter = Left$(strFilter, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
 
        'Finally, apply the string as the form's Filter.
        'Me.Filter = strFilter
        'Me.FilterOn = True
    End If
 
    DoCmd.OpenReport "Completed Kaizen List", acViewPreview, , strFilter
 
End Sub
 

[zmbd]

 'See if the string has more than 5 characters (a trailng " AND ") to remove.
104.     lngLen = Len(strFilter) - 4

Sticks out...

        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
110.         'Debug.Print strWhere
 

Uncomment line 110 and take a look again at the resolved string here. This might not be the issue; however, it's not going to hurt anything to verify that things are actually being formed correctly before we start in on other steps.

You might consider posting this string for us to look at too. :) ([CODE/] please :)

[jpreator]

strWhere comes up empty

[jforbes]

How are you expecting your report order to be Defined?

I don't typically use the OrderBy Property on a Report, but set the OrderBy in the Query that the report is based on. You may want to check the OrderBy and OrderByOnLoad properties of your Report to see if they are causing you this trouble.

[jpreator]

Tried using the OrderBy properties and it didn't make a difference.

[zmbd]

strWhere comes up empty

Sorry, didn't catch that you have a different string in the filter...
either insert the debug.print strFilter below line110 and comment out the original line or replace line110. I would like to see what I being passed to the report.

Also, if you are using a query as the recordsource for the report the SQL for that query might be something to look at too.

Finally, is the calculated value being done in the recordsource or the report/form?

[jpreator]

I had two departments selected with check boxes and this is the filter string "strFilter" = (DeptName = "RFP") Or (DeptName = "Engineering")

And I think I may have mispoke. It's not a calculated field as such, it's a count of records, but it is happening in the query which is a combination of two queries. The first is a list of all employees being pulled from a master HR database. The second is a count of the number of times the employee has entered a record in the local database.

Here is the sql code for the query:

SELECT qEEList.[Value Stream]
   , qEEList.DeptName, qEEList.Shift
   , qEEList.EmpID, qEEList.Name
   , Nz([CountOfEmpID],"0") 
      AS CountofCompleted
FROM qEEList 
   LEFT JOIN qParticipationCount 
      ON qEEList.EmpID 
         = qParticipationCount.EmpID
ORDER BY Nz([CountOfEmpID],"0") DESC;

[zmbd]

I've tried to replicate this in my test database and have not been able to do so with my data.

You do have to have the report closed before calling the report from the code... that's snagged me once or twice during development. Have it open in design view and then run the VBA to open the report... and the report ignores the WhereCondition when it opens in print preview or is sent to the printer (opps, sorry tree :( ).

Are there any VBA/Macro codes running in the On_Current, On_Load, On_Open, On_Activate, On_Filter,.... Or for that matter any of the events in the report?

Open your form in design view.
Show the property sheet for the report
Double check that "Report" is shown in the PS dropdown
Events tab

The attached is from data based on a MS tutorial.
I've set the where condition as:
[City_Name]='Jakarta' or [City_Name]='Paris'

In the report itself, the grouping is by [City_Name] sorted on [RetailSales] as descending.
[RetailSales] is a calculated field in the underlying query.

[City_Name] moved to the [City_Name] group header
Sub totals are calculated textbox in the [City_Name] group footer

The grand totals are in the report footer

The form use the DoCmd.OpenReport passing the wherecondition in as the above string.

Once the report was opened, used the DoCmd.OutPutTo command to send to PDF.

The formatting is generally default; thus, kindof ugly :)