Help with Sorting a report using checkboxes in my form

[Gumbyu]

Hello all, I have been working on this issue for about a week and still cannot get anything to group or sort.
At this point, I just want to be able to group a report by using a 'checkbox' on a form. This form is for reporting and I need to be able to group using different columns in my report.

The first question I have is, should the following grouping code be placed in the reportopen sub (of the report) or in the onclick sub (of the form). Let me say also that the onclick sub opens the report. I am fairly new at VBA and have had a lot of trouble with this. At this point, the group by code is only in the onclick for a button to generate the report.

I receive no errors at all, it just doesnt work.

PLEASE look at the code and let me know what I am doing wrong or if there is ANY more information that you may need. Am I overthinking this? I have read through the posts about grouping using queries and joins but - I am not entirely sure how or if that would (and have tried) solve my grouping issue. Those queries are long since deleted.

Also, this is code that someone off of tek-tips helped me with. So, thanks to them for getting me this far.

Thank you,
Bob

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 = True 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
 

and the onclick for the report:

Private Sub btnReportWriterPreview_Click()
On Error GoTo Err_btnReportWriterPreview_Click
getOrderBy
    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
 

[puppydogbuddy]

if field or control names contain spaces, you have to enclose the names with brackets, i.e. Job Code s/b [Job Code]...so try enclosing the names that contain spaces above with brackets, and see if that resolves your problem.

[Gumbyu]

PuppyDogBuddy,
Thank you for your reply.
I placed the brackets as suggested and the result was the same as the original problem with the code.

Do you have any other suggestions?

Bob

[puppydogbuddy]

PuppyDogBuddy,
Thank you for your reply.
I placed the brackets as suggested and the result was the same as the original problem with the code.

Do you have any other suggestions?

Bob

Bob,
If brackets did not make any difference, then that is your problem...you are trying to order by one literal string instead the individual fields wthin a string. You are going to separate the fieldname within the string like this;

"Order By " & [Job Code] & "," & [anotherField] & ...........

Also,for debugging purposes, I would get it working with one field by itself, then add the other fields later. Let me know if you need more help.

pDog

[Gumbyu]

Bob,
If brackets did not make any difference, then that is your problem...you are trying to order by one literal string instead the individual fields wthin a string. You are going to separate the fieldname within the string like this;

"Order By " & [Job Code] & "," & [anotherField] & ...........

Also,for debugging purposes, I would get it working with one field by itself, then add the other fields later. Let me know if you need more help.

pDog

PDog - I appologize for not getting back to this post to let you know if the solution above worked. Unfortunately, it did not. I just dont understand why the order by fields above are not passing to the report telling it what to order by. I would REALLY like to understand.

However, for now - what I did was create 5 seperate queries, then on the on click i just call the queries for each seperate check box that is checked (true). To avoid seperate reports, I also disabled the other check boxes when one is selected. Dirty way of doing it, but it worked.

The thing that I really want to understand (as I said before) is why my code didnt work to begin with. Also, if (and I know the customer will) request to 'group by' I really dont want to have to create a seperate query for, what, 96 possible variables that could be selected?

Any reference to a book or coding would be of great help.

Thank you for your response.
Bob

[puppydogbuddy]

PDog - I appologize for not getting back to this post to let you know if the solution above worked. Unfortunately, it did not. I just dont understand why the order by fields above are not passing to the report telling it what to order by. I would REALLY like to understand.

However, for now - what I did was create 5 seperate queries, then on the on click i just call the queries for each seperate check box that is checked (true). To avoid seperate reports, I also disabled the other check boxes when one is selected. Dirty way of doing it, but it worked.

The thing that I really want to understand (as I said before) is why my code didnt work to begin with. Also, if (and I know the customer will) request to 'group by' I really dont want to have to create a seperate query for, what, 96 possible variables that could be selected?

Any reference to a book or coding would be of great help.

Thank you for your response.
Bob

It would be helpful if you posted your revised code as per my suggestion and let me look at it. Also, see this link for another way of approaching this. This is fairly easy to implement:

http://www.fabalou.com/Access/Reports/sortorder.asp