Sort Report using Code

[The Bey]

I've already posted a similar thread to this but it didn't help so I'm reopening it to try to get an answer.

The response I had previously was

Here's one way:

Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1 'sort/group on car type
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2 'sort/group on company
      Me.GroupLevel(0).ControlSource = "Company"
      Me.txtGroup.ControlSource = "Company"
    Case 3 'sort on date/time, no grouping
      Me.GroupLevel(0).ControlSource = "DispDateTime"
      Me.txtGroup.ControlSource = "DispDateTime"
      Me.GroupHeader0.Visible = False
  End Select
End Sub

This method I don't understand because I don't know what I should use as "GroupLevel", "txtGroup" and the "= "CarType"" etc.

As I have a number of buttons, could I avoid using the case-select and just set the grouping in the OnClick of each button?

 

[GinaWhipp]

Perhaps you should post a link to the previous thread so we have some history...

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=213006

Sorry, I wasn't on a machine with 2007 so couldn't look at the sample. I meant to follow up later when I was on my laptop and forgot. If Gina hasn't resolved it, I'll try and look tonight.

 

[GinaWhipp]

Paul,

Not resolved... I had no reference point so *please* pick up where you left off!

 

[gemma-the-husky]

out of interest, a method i have used use is to add a field (string) to the query called sortorder, and sort on that only

i then set this with a function that constructs a string reflecting the sort order determined by various combo boxes etc, on the controlling form.

maybe worth a try.

 

[The Bey]

Still no feedback on the initial question. Any suggestions?

And Gemma I don't really understand what you're suggesting; would that use similar coding to the coding i'm asking for? Because if so, could you put a shortened eg. or link to something like it?

Sorry it's just that I'm close to finishing this and I'd like to wrap it up and make it more user-friendly

 

[pbaldy]

This method I don't understand because I don't know what I should use as "GroupLevel", "txtGroup" and the "= "CarType"" etc.

As I have a number of buttons, could I avoid using the case-select and just set the grouping in the OnClick of each button?

Sorry, my daughter is getting married this week so I've been busy. I'm also back on a computer without 2007 on it. Does this picture help relate what the code is doing with the actual objects?

In my experience it's problematic to do this type of thing from outside the report (your buttons). I think the only way to do it is to programmatically open the report in design view and change things, which you can't do in an MDE/ACCDE. I always do it in the Open event of the report. Depending on version, you could have your button pass the necessary info in OpenArgs and then use that in the Open event.

 

[The Bey]

Thanks for taking time to respond, I understand that you're busy at the moment and I very much appreciate it.

Yes that looks brilliant! Whereas on yours you have the user select an option button, would I be able to use something similar to this, where, upon clicking a button, the relevant boxes are un/marked and setting each box to be a case name, then using this grouping technique?

I see that you've set the grouping not only by code but also in a grouping box (the sorting and grouping table on the left of your picture). How do I do this?

What I'm going about doing now is having a couple queries, then dependent on which button is clicked, a hidden box is assigned a value, and depending on the value the report's Recordsource is a different query (Me.RecordSource = "qryGetDates1"). Running the query alone sorts the fields in the order I'm interested in, but the report is still ordering them in its own way. Any thoughts?

 

[pbaldy]

Sorting for a report has to be set up in Sorting and Grouping; it will not necessarily follow the sorting in the query. What I did was to set up the report as you see it, then I just change which field gets put into Sorting and Grouping and the textbox in the group header.

 

[The Bey]

I just had a closer look at your .jpg and I think I understand it a bit better now.
At the start of my code, could I dimension a string as a "button click" and then assign buttons to the dim'd value? ie.

Dim InsButtonClick As ButtonClick
InsButtonClick = button1, button2, button3

or

InsButtonClick = button1 Or button2 Or button3

Can something like this work or am I getting techniques crossed?
I'm just thinking that if I can assign a single string to multiple buttons, then as long as I click any of the buttons assigned to this value, it will call the commands that are registered to the buttons. Does this make sense? Wording isn't great because I don't know terms, but I'm trying here

Basically then I can map your work and edit it for my own use

 

[pbaldy]

Sorry, just got rid of a daughter.

I'm not really clear what you're trying to accomplish with that. As I think I mentioned earlier, you could use OpenArgs to pass some value to the report to be used in the Open event.