Sort report data with code

[BukHix]

I am opening a report using criteria from a combo box. The user wants to be able to choose the sort order of the report. I added two radio buttons, one named optName and the other optNubmer. How do I work the choice into my open report code pasted below?

Private Sub cmdOpenIndex_Click()
Dim strReptCriteria
Dim bProcOk As Boolean

bProcOk = True
strReptCriteria = txtReptCriteria

    
If IsNull(Me.txtReptCriteria) Then
    MsgBox "You must select a criteria!", vbExclamation, "Error"
    txtReptCriteria.SetFocus
    bProcOk = False
End If

If bProcOk Then
    DoCmd.OpenReport "CloseOutIndex", acViewPreview, , _
        "[ReportCaption] = '" & strReptCriteria & "'"
End If

[This message has been edited by BukHix (edited 02-08-2002).]

 

[Alexandre]

You won t do that from your sub, but from the On_Open event of your form:

Ex: Define the Descending sort order for the first Group Level

If Forms!ReportLaunchingFormName!optName= true then Me.GroupLevel(0).SortOrder = true

Alex

 

[BukHix]

huh? You lost me

. Thanks for the quick reply Alex. I want to make sure I am on the same page as you so here is a visual.

My form looks like this (this is the entire form):

I want to open the report here based on the selected criteria. The dropdown offers 3 choices and I want the ability to set the sort order on the report when it is viewed. Are you saying that can not be done?

It seems there would be a way to insert the option into the DoCmd:

DoCmd.OpenReport "CloseOutIndex", acViewPreview, , _
"[ReportCaption] = '" & strReptCriteria & "'"




[This message has been edited by BukHix (edited 02-08-2002).]

 

[Alexandre]

Much clearer now

It can be done but note that number sort/name sort doesn t make sense here: your options are ascending(A..Z, 0 to 9) descending(Z..A, 9 to 0). There are other sorting options anyway. Have alook to Access help for GroupOn for more details

There are two different things in fact in that you want to do:

-Define a group to sort on
This you can only do with the report opened in design view (Hence befor opening your report in preview mode). Use Echo False / Echo True to hide the manip to your user.
Ex:

Dim varGroupLevel As Variant


varGroupLevel = CreateGroupLevel(ReportName, Forms!ReportLaunchingFormName!ComboBoxName, _
        True/False, True/False)

Have a look to CreateGroupLevel in Access help for more info.

-Define a sorting order
This you can do after having created the group level, at he same time (in design view) or later on (from the report On_Open event). I suggest that you set to -1/0 (true/false) the values that can be taken by your option group.
Then:

Me.GroupLevel(0).SortOrder = Forms!ReportLaunchingFormName!optDescending

should do the trick

Alex

[This message has been edited by Alexandre (edited 02-08-2002).]

[This message has been edited by Alexandre (edited 02-08-2002).]

 

[BukHix]

Ok I will look into that. Actualy the Sort by Name or number is a reference to the field I want to sort.

The table has three fields, JobName, JobNumber, CompletionDate. There are over 3,000 records and right now the report is sorted alphabeticaly by default. The jobnumbers are actualy text boxes with numbers like this:

K1011
G1022
T1033
K1012
K1013
etc...

 

[SteveA]

After the DoCmd.OpenReport statement, place the following line:

    [Reports].[CloseOutIndex].[OrderBy]=txtReptOrderBy

All you need to do is replace txtReptOrderBy with the name of the field that holds your sorting criteria.

HTH
SteveA

 

[BukHix]

What am I doing wrong?

If bProcOk Then
    DoCmd.OpenReport "CloseOutIndex", acViewPreview, , _
        "[ReportCaption] = '" & strReptCriteria & "'"
    [Reports].[closeoutindex].[OrderBy] = [Reports]![closeoutindex]![JobNumb]
End If

 

[Alexandre]

Access reports do their own sorting based on the sort fields you specify in the Sorting and Grouping dialog of the report. The recordsource Order By clause is ignored .

Microsoft has a knowledgebase article that explains a technique for using setting the OrderBy property of a report by opening the report in design view (Article ID: Q146310) .

Alternative is, as I pointed, to set programatically the group levels ordering using the Open event. Drawback of this method is that you either have to have
all the group levels already set in your report, or to create them programatically with your report opened in design view...

Alex

[This message has been edited by Alexandre (edited 02-11-2002).]

 

[BukHix]

Thanks Alex I missed that the first time. It works now. I cheated and just copied the existing report making it into two. Next I modified the sort order in each one. Last of all I made this change in the open report code to figure out which option is being selected:

Dim strReptCriteria
Dim bProcOk As Boolean
Dim strName As String

If Me.frmValue = 1 Then
    strName = "CloseOutIndex"
ElseIf Me.frmValue = 2 Then
    strName = "CloseOutIndex2"
End If

bProcOk = True
strReptCriteria = txtReptCriteria

    
If IsNull(Me.txtReptCriteria) Then
    MsgBox "You must select a criteria!", vbExclamation, "Error"
    txtReptCriteria.SetFocus
    bProcOk = False
End If

If bProcOk Then
    DoCmd.OpenReport strName, acViewPreview, , _
        "[ReportCaption] = '" & strReptCriteria & "'"
End If

End Sub

[This message has been edited by BukHix (edited 02-12-2002).]

 

[Alexandre]

Yeap, BukHix

That is OK as long as you don t have too many grouping and sorting possibilities and that you don t mind too much about the db size. Otherwise, sorting and grouping 'on the fly' is a an interseting approach.

Alex

[This message has been edited by Alexandre (edited 02-12-2002).]