472,791 Members | 1,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,791 software developers and data experts.

Help with Sorting a report using checkboxes in my form

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
5 2342
puppydogbuddy
1,923 Expert 1GB
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
Gumbyu
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
1,923 Expert 1GB
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
Gumbyu
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
1,923 Expert 1GB
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

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

Similar topics

5
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table,...
1
by: Jon via AccessMonster.com | last post by:
Hi Guys, My "Sorting and Grouping" in my report looks like this: Field/Expression Sort Order sID Ascending ((= Project Name Ascending User *** ...
3
by: dad | last post by:
I am building a database to track the maintenance records on a fleet of cars. I need to run a report on that will display data based on whether a box is checked or not (i.e. all cars that have the...
8
by: nn0410 | last post by:
I have a report whose record source is a query. The query includes an ORDER BY clause that sorts on a particular set of columns. I would like to be able to run the same report with the same input...
3
by: Don | last post by:
I have a "Report" that is created from a "Form". It prints a list of items, you may consider it a shopping list. In any event I use to run this in alphabetical order but have since decided to run...
9
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you can't reopen database after exiting as a result...
4
by: access baby | last post by:
i have a huge database based on date and time need to create different report we need to measure our work processes how many order received , order cancelled, completed and count of items completed...
1
by: access baby | last post by:
Hi Below mention is the reply from Salad on my query i created a crosstab query and form not based on any table of qurey but this doesnt work . I somehow have missed something actually i have too...
3
by: billa856 | last post by:
Hi, My Project is in MS Access 2002. In that i have one form in which I am using several textboxes,comboboxes and (three) checkboxes. in that form I have BillTo address and ShipTo address and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.