By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,629 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Help with Sorting a report using checkboxes in my form

P: 6
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

Expand|Select|Wrap|Line Numbers
  1. Public Function getOrderBy() As String
  2.   Dim chkcoord As Variant
  3.   Dim chksales As Boolean
  4.   Dim chkjobcode As Boolean
  5.   Dim chkflc As Boolean
  6.   Dim chkcust As Boolean
  7.   chkcoord = True
  8.   chkcust = True
  9.   chksales = True
  10.   chkflc = True
  11.   chkcust = True
  12.   chkjobcode = True
  13.  
  14.   If chkcoord Then
  15.     getOrderBy = "Coordinator, "
  16.   End If
  17.   If chksales = True Then
  18.     getOrderBy = getOrderBy & "Sales #, "
  19.   End If
  20.   If chkjobcode Then
  21.    getOrderBy = getOrderBy & "Job Code, "
  22.   End If
  23.   If chkflc Then
  24.     getOrderBy = getOrderBy & "OfficeLocation, "
  25.   End If
  26.   If chkcust Then
  27.       getOrderBy = getOrderBy & "CUSTOMER NAME, "
  28.   End If
  29.   If Len(getOrderBy) = 0 Then
  30.     getOrderBy = ""
  31.     Exit Function
  32.   Else
  33.     getOrderBy = "ORDER BY " & Left(getOrderBy, Len(getOrderBy) - 2)
  34.   End If
  35.  End Function
  36.  
and the onclick for the report:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnReportWriterPreview_Click()
  2. On Error GoTo Err_btnReportWriterPreview_Click
  3. getOrderBy
  4.     Dim stDocName As String
  5.     stDocName = "RptPMSPSR"
  6.     DoCmd.OpenReport stDocName, acPreview
  7.  
  8. Exit_btnReportWriterPreview_Click:
  9.     Exit Sub
  10.  
  11. Err_btnReportWriterPreview_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_btnReportWriterPreview_Click
  14.  
  15. End Sub
  16.  
Sep 16 '07 #1
Share this Question
Share on Google+
5 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Sep 16 '07 #2

P: 6
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
Sep 16 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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
Sep 16 '07 #4

P: 6
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
Sep 20 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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
Sep 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.