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

and the onclick for the report:

Sep 16 '07 #1

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.

Sep 16 '07 #2

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

Sep 16 '07 #3

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

Sep 16 '07 #4

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

Sep 20 '07 #5

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

Sep 20 '07 #6