Dynamic report in vba

[trevor40]

Hi All, I'm just about to start designing the reports for my system. What I have is a form display of Incoming Consignment notes for one of 2 companies or both companies, selectable via an Option group.

I use the same query for the report and the form, and get the results as seen in the form. This works fine. I know that they will in the future ask for all kinds of reports for this, and would like to provide them with a good selection to start with. Thier DB system will be developed into other aspects of thier bussiness as well - repair tracking, design tracking, warehouse control.

What i'd like to do now is provide an option on the form for the user to select 3 options (By Date, By Courier, by Supplier) and to change the grouping order of the report based on that.

To do this on the form I will have 3 Toggle buttons tb1, tb2 and tb3, named as above. The order they select them will dictate the grouping order. I'll look into sorting later

If anybody out there has done any sort of VBA for dynamic sorting/grouping for reports I'd love to see a sample.
If this all works I will then have to duplicate it for the Outgoing ones as well.



OR
If you can offer another solution to this I am very interested.

[Ruegen]

My tip would be not to make a dozen reports - it's easier to make one report of the same query and then simply filter it with VBA based on what options you have given on the form before it is opened.

I would use "case select" for toggle buttons that way if case 1 is selected then do this or this etc

then you could filter the report by date DESC (or ascending ) etc

more about case
http://www.techonthenet.com/excel/formulas/case.php

A nice visual thing I do is put the report on the form as a subform - that way you can see the report within a subform on the form. So the user stays on the form. Then have a print button there that prints the report.

[trevor40]

I've been using case statements for 25 years now, that won't do what i need.

I was after a way for the user to select from several options the order of those options. IE 1,2,3 or 2,3,1 or 3,2,1 or 3,1,2 or 2,1,3. I was thinking of some sort of register to keep track of how many have been selected and assign the next highest number and so on, based on that create the report grouping. If only 1 or 2 or 3 are selected ignore the other grouping.

[Ruegen]

I've been using case statements for 25 years now, that won't do what i need.

I was after a way for the user to select from several options the order of those options. IE 1,2,3 or 2,3,1 or 3,2,1 or 3,1,2 or 2,1,3. I was thinking of some sort of register to keep track of how many have been selected and assign the next highest number and so on, based on that create the report grouping. If only 1 or 2 or 3 are selected ignore the other grouping.

do you need to store that register? Or is it just for selection and opening the report accordingly purposes?

[trevor40]

A temp one should be ok, at this time I don't think i need it elseware, but perhaps storing the order in a table may be much better. clear it before starting then add the number from the button press then continue until the user hits 'print report'. I think I just solved this while typing, a combo box with buttons 1-4, on click store button click number in the table, use as required. I could even keep this as thier default report style!

What i need to do now is figure out how to change the report using vba, based on that information.
Any help there would be fantastic.

[Ruegen]

A temp one should be ok, at this time I don't think i need it elseware, but perhaps storing the order in a table may be much better. clear it before starting then add the number from the button press then continue until the user hits 'print report'. I think I just solved this while typing, a combo box with buttons 1-4, on click store button click number in the table, use as required. I could even keep this as thier default report style!

What i need to do now is figure out how to change the report using vba, based on that information.
Any help there would be fantastic.

Yeah a temp works fine - I use them all the time for various tasks across forms. I'd suggest for the combo box you treat it like a list box and get the values form me.cboBox.column(0) etc (you stuff a temp var with any value from any column as long as you have given the number of columns in the column property of the combo box and given a size, this way you can have multiple fields even though they are hidden and stuff them into temp variables if needed.)

What part of the report do you want to change? The properties or to filter the information?

you can docmd.open with a where condition or you could filter using the temp vars if you are filtering.

[trevor40]

copy from post # 3

I was after a way for the user to select from several options the order of those options. IE 1,2,3 or 2,3,1 or 3,2,1 or 3,1,2 or 2,1,3. I was thinking of some sort of register to keep track of how many have been selected and assign the next highest number and so on,

based on that create the report grouping. If only 1 or 2 or 3 are selected ignore the other grouping.