Report based on fruit variety

[ssworthi]

I haven't created a report that is based on specific criteria such as fruit variety which I have been asked to do.



The user would like a report of season to date fruit hauled by variety. A table is populated of fruit hauled daily and I need to specify for example create a report for season to date say "gala". Is that accomplished through a form which then ties in somehow to the report. I'm drawing a blank.

Thanks for any help you can offer. I have the report of season to date of all fruit and I have a combo box of different varieties; I just need to tie them together. Thanks ever so much.

[June7]

What do you mean by 'report of season to date fruit hauled by variety' - a report that lists records grouped by variety? Use report Sorting & Grouping design features.

[ssworthi]

I am aware of the grouping, etc., what I would like to accomplish is to have a drop down or something that the user can select the fruit variety so if he only wants to see for instance "Gala" Apples and not all apples, just the one variety reflected on the report.

[June7]

Have combobox on form and then open report filtered by selected item.

DoCmd.OpenReport "report name", , acViewPreview, "Fruit='" & Me.textboxname & "'"

[orange]

This type of request --- a general report format that can be customized -- is often handled with an initial form with a dialog to get report parameters from the user, then a submit Report button to produce the custom report.
See Dynamic Reports for info and example.

Good luck.

[ssworthi]

Have combobox on form and then open report filtered by selected item.

DoCmd.OpenReport "report name", , acViewPreview, "Fruit='" & Me.textboxname & "'"

I am sorry; I really need help a little further with this. I have created a form "frm_variety", it has a combo box with the different varieties possible, Im not sure where I use the cmd verbiage, in the "open report" in the attributes of the report? thank you so much.

[June7]

Could put it in the AfterUpdate event of combobox. Find the AfterUpdate event property, select [Event Procedure], click the ellipsis (...) to open VBA editor, type code in the procedure. Or build [Embedded Macro]. I don't use macros.

[orange]

Did you look at the material from Martin Green's Dynamic Reports -the link I gave in post #5?

[ssworthi]

I have been trying but haven't gotten success yet. I created a form, did all the parameters for such a form, not 100% understanding the code. The combo box I created is "cmb_var" so in the after update in properties of the combo box I did an expression:
Do.Cmd.OpenReport "Season to Date Haul Report", , acViewPreview, "Variety="' & Me. [Cmb_Var] & "'" and am receiving a "The expression you entered contains invalid syntax"
This is my first exercise trying to do this sort of thing so please excuse my naivety, I really want to learn this as I see this applying so many other places. Thank you for your patience.

[orange]

Try (untested)
DoCmd.OpenReport "Season to Date Haul Report", , acViewPreview, "Variety='" & Me.[Cmb_Var] & "'"

[ssworthi]

Thank you for all your help; you have no idea.
Mind if I walk through this with you?
I have a report "Haul to date by variety". I have a new form called frm_var. The frm_var has a combo box listing all of the varieties. This combo box will drive the criteria of the report of which variety I want to see in the report. In the parameters AFTER UPDATE of the combo box called "cmb_var" the command "DoCmd.OpenReport "Season to Date Haul Report", , acViewPreview, "Variety='" & Me.[Cmb_Var] & "'" Is there supposed to also be a command button to run the report after making the selection in the combo box; this is my confusion I think.

[orange]

No, I don't think so. If you have the DoCmd.OpenReport in the after Update of the combo, I would expect the report to open...unless there is an error .

[ssworthi]

Wow it worked!! Thank you, thank you! It's going to printer, possible to change it to print preview; what would the code be?

[June7]

Ooops. acViewPreview in wrong argument. Note the change in position in relation to commas. Get familiar with the various arguments of the OpenReport method then the error would become apparent to you.

DoCmd.OpenReport "report name", acViewPreview, , "Fruit='" & Me.textboxname & "'"

[ssworthi]

thank you so much! It works perfect now!