Form for report criteria and grouping levels 1

[barbola]

I have very limited VB skills. I have several versions of queries and reports accessing the same data, except different criteria and grouping levels are needed for the reports, which is why I have so many.

I know there must be a way to use one query and one report and just ask the user to select the report grouping levels and categories of data using a form.

I know how to use a form for report criteria, but then I need separate queries for each type of criteria - I think.

For example, the fields are date, group, type, weight. Some reports have all data by date, so I have a query for that. Some reports show data for one group for all dates, and some show data for one group for a date range entered. Some show all data with weight classes, some just for a group.

I have to have separate queries and reports for each scenario - see my dilemma? I just don't know how to "code" this.

Thanks!

 

[randysmid]

Hi Barb,
Personally, I have a strategy that has worked very well. This strategy is based on analyzing each report, and determining the criteria needed. By creating a matrix of this criteria, you can create a form that provides alot of functionality. In this form, the first field to be accessed by the user is the report selection combobox. I base this on a query, with a table underlying it that contains the official report names, e.g., "Sales Report by Region", "Master Sales Report", etc. When the form is first opened, none of the criteria fields on the form are visible. After the report is selected (using the AfterUpdate event for the report selection combobox), I then "turn-on" the necessary criteria fields. The code might look like this:
'code to set criteria for "Sales Report by Region"
If cboReport = "Sales Report by Region" Then
lblDateFrom.Visible = True
txtDateFrom.Visible = True
lblDateTo.Visible = True
txtDateTo.Visible = True
End If

After the user enters all the criteria, then you will provide a command button where you will check to make sure all necessary entries have valid data. It is then that you can use two different techniques to run the report. I wrote FAQ's on these. The first uses a query, where you "pass" the criteria to it. This is FAQ: faq703-2696

You can also use "filter", and you will find that amongst my FAQs if you are interested. Anyway, this should be enough to get you started. HTH,
Randy Smith
California Teachers Association