• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

Problems with a descending sort applied to a report in MS ACCESS VBA

I have a print sort tab on a multitab form.  I can sort the records the way I want, and the report will produce an output that matches the sort, but if I add a "desc" after the field I want to sort on I get an error that says
"Enter parameter value  then shows the fieldname with the desc after it.

How do I get descending to work?

Thanks
0
MurphyPH
Asked:
MurphyPH
  • 8
  • 6
  • 3
  • +1
2 Solutions
 
MurphyPHAuthor Commented:
Here is the code.  I tried to add a wherecondition to the openreport but I don't really understand how to make it work.
The sort works just fine and is then stored in the OrderBy attribute of the form.

Private Sub cmdPrintReportNoPhotos_Click()
On Error GoTo Err_cmdPrintReportNoPhotos_Click
'name the report
    Dim stDocName As String
    stDocName = "ReportPage1NoPhotos"
    'Dim Where As Variant ' this is for the sorting.
    'Where = "SELECT WetForm.*, WetVeg.* FROM WetForm INNER JOIN WetVeg ON WetForm.ID1=WetVeg.ID2 Order by " & Me.OrderBy & ";"
'Identify the filter items
    Dim rptFilter As String
    rptFilter = " "
    If cboProjectSelect <> "All" Then
    rptFilter = "ProjectSite = " & Chr(34) & cboProjectSelect & Chr(34)
    End If
    If cboAppSelect <> "All" Then
        If rptFilter = " " Then
            rptFilter = "ApplicantOwner = " & Chr(34) & cboAppSelect & Chr(34)
        Else
            rptFilter = rptFilter & " and ApplicantOwner = " & Chr(34) & cboAppSelect & Chr(34)
        End If
    End If
    If cboInvestSelect <> "All" Then
    If rptFilter = " " Then
    rptFilter = "Investigator = " & Chr(34) & cboInvestSelect & Chr(34)
    Else
    rptFilter = rptFilter & " and Investigator = " & Chr(34) & cboInvestSelect & Chr(34)
    End If
    End If
    If cboStartSelect <> "All" Then
    If rptFilter = " " Then
    rptFilter = "SDate >= " & "#" & cboStartSelect & "#"
    Else
    rptFilter = rptFilter & " and SDate >= " & "#" & cboStartSelect & "#"
    End If
    End If
    If cboEndSelect <> "All" Then
    cboEndSelect = cboEndSelect + 1
    If rptFilter = " " Then
    rptFilter = "SDate <= " & "#" & cboEndSelect & "#"
    Else
    rptFilter = rptFilter & " and SDate <= " & "#" & cboEndSelect & "#"
    End If
    End If

'now print based on whether or not there is a filter
If rptFilter = " " Then ' no filter
        If txtStartRec = "All" And txtEndRec = "All" Then
            DoCmd.OpenReport stDocName, acViewNormal
        Else
            DoCmd.OpenReport stDocName, acViewPreview
            DoCmd.PrintOut acPages, txtStartRec * 2 - 1, txtEndRec * 2
        End If
    Else 'there is some filter
        If txtStartRec = "All" And txtEndRec = "All" Then
            DoCmd.OpenReport stDocName, acViewNormal, rptFilter
        Else
            DoCmd.OpenReport stDocName, acViewPreview, , rptFilter
            DoCmd.PrintOut acPages, txtStartRec * 2 - 1, txtEndRec * 2
        End If
    End If

Exit_cmdPrintReportNoPhotos_Click:
    Exit Sub

Err_cmdPrintReportNoPhotos_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintReportNoPhotos_Click
End Sub
0
 
Gustav BrockCIOCommented:
You can filter on OpenReport but not sort. Neither can you apply reliable sorting to the recordsource (the query) of the report. Sorting _has_ to done in the report adjusting the sortorder of a grouplevel:

http://www.experts-exchange.com/Database/MS_Access/Q_23201472.html#a21010327

/gustav
0
 
PatHartmanCommented:
Any sort in the RecordSource is ignored because Access thinks it is smarter than we are and completely rewrites your RecordSource query.  It only does this for reports and it does it because it knows that the record source does not need to be updateable so it forces it to be a snapshot.   It ignores any sort and eliminates any column that is not bound to a control on the report.  So, if you've ever run into a problem with code in a report because it couldn't resolve a field, this is the reason.  If you want to use a field in code, you MUST bind it to a control on the report.  You can make it very tiny and not visible, but it has to be there since that is the only way to force Access to retain it when it rebuilds the query.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
MurphyPHAuthor Commented:
Thanks for the help.  you made it clear that I can't control the report from the form except for filtering.  So I controlled the report from the report instead using an on open event that called the sorting info from the Form.

First I had to remove all of the groupings that were built into all the reports. You can see these by getting into design mode then Design>Group&Sort then remove all of the existing groups.  

Then replace the On Open event for the report with this:
Private Sub Report_Open(Cancel As Integer)
'This sets up this sorting order for printing by calling the sorting fields from the form.
rptSort = Forms![routinewetlanddelineation].[cboSort1] & ", " & Forms![routinewetlanddelineation].cboSort2 & ", " & Forms![routinewetlanddelineation].cboSort3 & ", " & Forms![routinewetlanddelineation].cboSort4 & ", " & Forms![routinewetlanddelineation].cboSort5
'This orders the report directly from inside the report.
OrderBy = rptSort
Me.OrderByOn = True
End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"You can filter on OpenReport but not sort"

Umm ... that's not the case Gustav. I'm been doing that for years by setting the OrderBy  Property in the Report OnOpen event.

mx
0
 
MurphyPHAuthor Commented:
Yes, but to set it interactively from a form, you have to send some variables to the report for the report to use as the Orderby setting.
0
 
Gustav BrockCIOCommented:
Well, I meant the filter parameter on the command line.

/gustav
0
 
MurphyPHAuthor Commented:
For some reason filtering works without any problem from the Form, only the sorting, using the "desc" suffix caused the problem at first.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Seems to be a lot of confusion here.

IF ... the Grouping & Sorting dialog has been used for Grouping, then Access will completely ignore any sorting in the underlying Recordsource of the Report.
IF ... the Grouping & Sorting dialog has not been used, then Access will honor any sorting present in the underlying query (assuming a query and not just a table).

If there is no sorting specified in the query, the query (hence the Report)  will be sorted by the Primary Key of the Table the query is based on. If there is no Primary Key in the table, then the query will be sorted by the order the records were entered in the table.
IF there is more than one table in the query, then it will be harder to determine what the sorting is ... again, all of this IF ... the Grouping & Sorting dialog has not been used.

Further ... if the G&S dialog is used AND there is sorting specified in the query, Access will still do the query sorting, adding a performance hit (may or may not be noticeable). I've never heard of Access 'rewriting' the query and changing it to a Snapshot recordset.  In fact, a Snapshot recordset will generally be slower that a dynaset recordset because Access supposedly has to read the entire recordset upfront to get the 'latest' data, so I seriously doubt that is happening. I never use Snapshot recordsets for anything.

mx
0
 
MurphyPHAuthor Commented:
AND,
If there is not G&S that is preset for the report,
And
If you want to change the sort order of the Report using a Form, you can select the fields you want to use for sorting by using controls on the Form that are populated with the field lists.  You can also add the text "desc" to the field name while it is in the control to get a descending sort.
THEN
You can call the items in the controls on the Form to fill in the Orderby property of the report.

This will be a nice reference for the future.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"If there is not G&S that is preset for the report, "

Even if there is,  any 'preset' Grouping and/or Sorting will be overridden if set in code in the Report Open Event.

mx
0
 
MurphyPHAuthor Commented:
I did not find that to be true I don't think.  If I set the orderby to something different than the preset, it ignored the orderby.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And RE this what Pat said:

"  So, if you've ever run into a problem with code in a report because it couldn't resolve a field, this is the reason.  If you want to use a field in code, you MUST bind it to a control on the report."

There is one other piece to that.  If you do bind a Control to a Field in the query AND you want to refer to it in code, you MUST make the Name property of the Control different than the Control Source. Access defaults the Name of a bound Control to the same name as the bound field.

So, if your Control Source is say
CompanyName
The Name of the Text box holding this field would need to be something like
txtCompanyName ... again, ONLY if you need to refer to that Field in code.
0
 
MurphyPHAuthor Commented:
Good to know, in my case that is what I did with the cbo (combobox) prefix.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I did not find that to be true I don't think.  If I set the orderby to something different than the preset, it ignored the orderby."

I've been doing it for years ... again, setting the OrderBy property in the Report Open event.

btw ... a side note.  Whereas Sorting in a query that drives a Report is ignored per what I stated above, Grouping is not of course.  Sometimes it's convenient or necessary to do some 'pre grouping' in the query.
0
 
MurphyPHAuthor Commented:
Sorry to contradict you, but replacing the orderby does NOT work unless all the preset order&sort in the report has been removed.  I produced a video to demonstrate that fact and you can get to it at:
http://www.Ecotone.org/Download/SortDemo.mp4.txt   just remove the .txt to run the mp4
It is about 49MB in size.

Or try http://www.Ecotone.org/Download/SortDemo.html for a flash version.  About 7 MB, just click the green button in the center after it downloads.  There is a bit of a delay after you click the green button.
1
 
PatHartmanCommented:
Joe,
In order to see the impact of the changes to the query, you need to be linked to SQL Server and you need to view the query that is sent to the server.  It is possible that newer versions of Access don't force a snapshot.  I haven't checked in a while but 2003 and possibly 2007 did force a snapshot.
To get a partial sense of the changes, add a column to the query that is not bound to any control in the report.  Then add code that references the column.  It will work initially but after you save and reopen the report, it will fail.
Pat
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" you need to be linked to SQL Server and you need to view the query that is sent to the server"
:-(
I was only referring to Access dbs.

Yes ... I agree you cannot ... from code ... reference a Field that is not actually on the Report.
0
 
PatHartmanCommented:
I don't think it works differently.  It is just that you can see the damage if you are linked to SQL Server because you actually get to see the result of the changes if you look at the query as it is sent to the server.  I don't know of any way to see the changes made by the report engine if the BE is Jet/ACE.  They are not saved anywhere and they don't permanently change the RecordSource query.  The change only exists while the report runs.  The only way to verify that Access is indeed mucking with your query is to do what I suggested and use a column in code that isn't bound to a control.  Questions like this can easily be answered by the Access team if only they were willing.

I forgot to mention the name issue.  I never run into it since the first thing I ever do when I create a form/report is to rename all the Name properties to be different from the bound column name.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now