Passing a check box true value to sort in a form

[soklear]

Hi. I really need help.

The issue is that I have a form that is utilized as a 'search for' various values and returns the values into a report.
The report is functioning fine, however, I am having a really hard time building a 'sort by' function that will pass to the report to sort by the values selected.

For instance, I have 4 check boxes:
1) Coordinator, 2) Sales Number 3) City 4) Office location

The issue that I am having is that the sort function I placed into either the 'onclick' function to pull the report or on the 'onopen' function of the report, I cant seem to get the sort to work, at all.

Here is the code:
Private Sub sort1_sub()

If Me.chkcoord = True Then
gstSqlSort0 = "me.Coordinator"
Else
gstSqlSort0 = Me.chkcoord
End If
If Not IsNull(chkcoord) Then
If chksales = True Then
gstSqlSort1 = "Sales #"
Else
gstSqlSort1 = chksales
End If
End If
If Not IsNull(chkjobcode) Then
If chkjobcode = True Then
gstSqlSort2 = "Job Code"
Else
gstSqlSort2 = JobCode
End If
End If
If Not IsNull(chkflc) Then
If chkflc = True Then
gstSqlSort3 = "Job Code"
Else
gstSqlSort3 = JobCode
End If
End If
If Not IsNull(chkcust) Then
If JobCode = True Then
gstSqlSort4 = "CUSTOMER NAME"
Else
gstSqlSort4 = chkcust
End If
End If

gstSqlOrderBy = " Sort By " & gstSqlSort0

If gstSqlSort1 <> "" Then
'gstSqlOrderBy = gstSqlOrderBy & "," & gstSqlSort1
'End If
If gstSqlSort2 <> "" Then
gstSqlOrderBy = gstSqlOrderBy & "," & gstSqlSort2
End If
If gstSqlSort3 <> "" Then
gstSqlOrderBy = gstSqlOrderBy & "," & gstSqlSort3
End If
If gstSqlSort4 <> "" Then
gstSqlOrderBy = gstSqlOrderBy & "," & gstSqlSort4
End If

End Sub


Which, is called when I click on the report button (the code

Private Sub btnReportWriterPreview_Click()
On Error GoTo Err_btnReportWriterPreview_Click
gstsqlqry = "select QryMainSPSR.* from QryMainSPSR"
sort1_sub
Dim stDocName As String
stDocName = "RptPMSPSR"
DoCmd.OpenReport stDocName, acPreview

Exit_btnReportWriterPreview_Click:
Exit Sub

Err_btnReportWriterPreview_Click:
MsgBox Err.Description
Resume Exit_btnReportWriterPreview_Click

End Sub

 

[Trevil]

I see a few strange things:
1. Is the following really the code you are using?
If gstSqlSort1 <> "" Then
'gstSqlOrderBy = gstSqlOrderBy & "," & gstSqlSort1
'End If
because the 2nd & 3rd lines are comments.
2. In your 'Sort1_sub', you go to all of the trouble to build the sort string in variable gstSqlOrderBy, but I can't see where you ever use it. I would have expected to see the code in btnReportWriterPreview_Click look more like:
sort1_sub
gstsqlqry = "select QryMainSPSR.* from QryMainSPSR " & gstSqlOrderBy

3. If you are trying to sort by a field, or ignore the sort for a field, I don't understand what you are doing with:
If Me.chkcoord = True Then
gstSqlSort0 = "me.Coordinator"
Else
gstSqlSort0 = Me.chkcoord
End If
I would think you want the name of the field in the recordset -- not "me.xxx".


Learn from the mistakes of others. You won't live long enough to make all of them yourself.

 

[MajP]

You are pretty far off the mark. As far as I can see this does nothing. You build a sort order string in your sub, but you never use it anywhere. I would make this a function that returns a string, but looking at this I am very confused. I am assuming that if a box is checked, it will include the field in the sort order. Your names do not even seem to be correct and repeat. Why are you checking for Null?

1)What is supposed to happen here?

If Me.chkcoord = True Then
gstSqlSort0 = "me.Coordinator"
Else
gstSqlSort0 = Me.chkcoord
End If

If Not IsNull(chkcoord) Then
If chksales = True Then
gstSqlSort1 = "Sales #"
Else
gstSqlSort1 = chksales
End If

first you see if the control chkCoord is true if it is you set the string equal to a string "Me.Coordinator" if not to false or null. Then you check it again to see if it is null and if it is you set the string "Sales #" or to null. What are you trying to do?

I am going to take a big swag.

Here is my function that returns an order by string. I use 2 state check box controls because I have no idea why you need a false and a null selection.

Public Function getOrderBy() As String
  Dim chkCoord As Variant
  Dim chkSales As Boolean
  Dim chkjobcode As Boolean
  Dim chkflc As Boolean
  Dim chkcust As Boolean
  'chkCoord = True
  chkcust = True
  chkSales = True
  chkflc = True
  chkcust = True
  chkjobcode = True
  
  If chkCoord Then
    getOrderBy = "Coordinator, "
  End If
  If chkSales Then
    getOrderBy = getOrderBy & "Sales #, "
  End If
  If chkjobcode Then
    getOrderBy = getOrderBy & "Job Code, "
  End If
  If chkflc Then
    getOrderBy = getOrderBy & "OfficeLocation, "
  End If
  If chkcust Then
      getOrderBy = getOrderBy & "CUSTOMER NAME, "
  End If
  If Len(getOrderBy) = 0 Then
    getOrderBy = ""
    Exit Function
  Else
    getOrderBy = "ORDER BY " & Left(getOrderBy, Len(getOrderBy) - 2)
  End If
 End Function

demo where all check boxes are true:
?getOrderBy
ORDER BY Coordinator, Sales #, Job Code, OfficeLocation, CUSTOMER NAME

using this function in your sub:
gstsqlqry = "select QryMainSPSR.* from QryMainSPSR" & getOrderBy