dynamic sorting

[striker73]

I have a report and I want to allow users to decide how they want it sorted. I want to add a new sorting/grouping level, is there a way I can do this dynamically? I'm hoping to be able to give the user options on how they want the report sorted (display by zip code, by territory, by industry...) and I'm hoping to be able to do this dynamically. Any ideas? Thanks!!

 

[JoeMiller]

Look at the GroupLevel and CreateGroupLevel functions, they explain how to do this.
Joe Miller
joe.miller@flotech.net

 

[striker73]

I am trying to set the group level control source with VBA and I'm not sure how I should reference a field from my query. The example I was looking at with Access help showed how to set the control source to a textbox on another form. The field I'm trying to use is 'Zip' and I've tried the following, without any luck:

Me.GroupLevel(0).ControlSource = Zip
Me.GroupLevel(0).ControlSource = [Zip]

Any ideas on the syntax for that? Thanks!

 

[JoeMiller]

Me.GroupLevel(0).ControlSource = Zip

That is the proper syntax.. do you already have a group section in the report? The GroupLevel function modifies an existing group section, it does not create a new one. You need to use CreateGroupLevel for introducing a new group level.

Joe Miller
joe.miller@flotech.net

 

[striker73]

Yes, I have 3 groups already existing. I wanted to change the first group to sort by zip code, just to see if I could get it to work. Maybe the best way would be to create all my group levels dynamically. When I use the syntax above, I get a run-time error 2465 which says MS Access couldn't find the field 'Zip' referred to in my expression.

Zip is part of my query and when I go to change the sorting/grouping within Access, it comes up automatically when I start to type it.

 

[JoeMiller]

What event are you running this code in? I run it in the OnOpen event of the form, and it works exactly the same syntax as you posted.

Joe Miller
joe.miller@flotech.net

 

[striker73]

I'm running this in the Report_Open event of a report.

 

[striker73]

I think using quotes might work. I didn't get an error message at least.

Me.GroupLevel(0).ControlSource = "Zip"

 

[JoeMiller]

You're right, I'm sorry... I wasn't thinking when I replied to your post.

Joe Miller
joe.miller@flotech.net