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
-
5 2342
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.
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,
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
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
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 Sign in to post your reply or Sign up for a free account.
Similar topics
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,...
|
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 *** ...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
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...
|
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=()=>{
|
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...
| |