Access 2003 running multiple reports from a form

Topic

New Reply

Hi, I’m looking for some help with a database I’m doing. I want to be able to run a selection of diffrent reports from a form and, depending on what report is asked for, the criteria for the query to come from a combination of 7 other drop down menus. So for example I have a list of report types in one combo box and then 7 other combo boxes that run from querys on the table I want the reports from (Start Date, End Date, Regime, Initiating Team, BF Date, Customer and Document Type are the 7 selections that can be made) For the simplest report the selection will be just Start Date and End Date but others will include all selections. Now what I need help with is getting a coimmand button to run the reports based on the report type selection and then for the reports to use the entries in any of the active combo boxes, for example the most basic one I would normally have just entered “between [Start Date] and [End Date]” in the Criteria on the query but I need it to run from the Start Date and End Date from the combo boxes on the form. Hope this makes sense to someone.

I’m still pretty useless at VB so dummy instructions would be nice.

Cheers

Reply | Quote

Replies

However you do this, it is going to involve some pretty heavy VBA coding I am afraid.

Just to clarify:

[*]Not all criteria apply to all reports. StartDate and EndDate are the only ones that apply to all.[*]Not all applicable criteria need to be used. From among those that can be used, the user will choose the ones they want to use when they run the report.

I think it needs to be explicit to the user just what criteria are relevant to each report, so I think I would use the After Update Event of the List of reports to selectively enable/disable other combo boxes. And when you disable a combo box set its value back to null.

Then when you compile your criteria, you don’t need to base it on the selected report. Only the criteria relevant to the current report will have values in them.

So what would the After Update Event look like

Code:

Select Case me.ComboReportlist

Case "Report1"
 me.combo1.enabled=true
 me.combo2.enabled =false
 me.combo2=Null
 me.combo3.enabled =true
 me.combo4.enabled=false
 me.combo4=Null

Case "Report2"
 me.combo1.enabled=true
 me.combo2.enabled =true
 me.combo3.enabled =true
 me.combo4.enabled=false
 me.combo4=Null

Case "Report3"
me.combo1.enabled=true
 me.combo2.enabled =true
 me.combo3.enabled =false
me.combo3=Null
 me.combo4.enabled=true
 
End Select

In all this you need to decide what to enabled and disable when, and change the names.

I would use a List Box for the list of reports, rather than a combo. With a ListBox users can see the range of options without having to click the dropdown.

See how you go with getting this to work. We can move on the the next step when this bit is working OK.

Reply | Quote

John,
Thanks for the reply,your a Star!

I’ve got the After Update Event to work fine with everything so that’s all good. I’m going to eventually have multiple, like 20+, different reports running from this so I think I’ll have to go with drop downs instead of a List Box. Now what I’m looking to do is have a command button that will run the reports using the the combo box selections as the criteria?

Oh and 1 other thing and this is probably very simple but I’ve had no success at all today, I want to be able to select all types of record from a combo box as well as specific types and blanks if you know what I mean. For example some reports of the same type will need to be for Regimes, some just for specific regimes and some for regimes that have been left blank. Hopefully this makes sense to you, I’m not that great at explaining.

Cheers again

Reply | Quote

have you considered creating a form with drop down boxes then have a report based on a query that referes to the dropdown items of your form, where you could also add a button to open the report. The report title could have text boxes refering to values you selected such as between date1 and date2

I hope this helps. here is an example http://office.microsoft.com/en-us/access/ha011730581033.aspx

Reply | Quote

Good that it has worked so far.

Whether you you use a Listbox or Combo for the list of reports is up to you, and does not make any difference, exccept for appearances. I think I still prefer the Listbox, even with 20 reports.

You need two more things. One is easy, one is more complicated.

[*]A command button to open the report. You could use the wizard to build it, then edit the code so that the name of the report is not hardcoded. Instead have some code like this:

Code:

If Not IsNull(Me.ListReports) Then
 stDocName = Me.ListReports
 DoCmd.OpenReport stDocName, acViewPreview
Else
	MsgBox "Select a Report", vbInformation
End If

[*]Now you have to deal with all the various criteria, and there are two approaches to this:

[*]Use queries that refer to the various controls on your form, as suggested by r3x3[*]Build a “Where” clause in code.

I find the second approach the most flexible so I will explain that.
The line of code that opens a report will accept a Where parameter as shown below. This acts as a filter, and has the same effect as adding whatever it contains as additional criteria to the query. So we don’t have to change the query.

Code:

DoCmd.OpenReport stDocName, acViewPreview,,Strwhere

To make all this work you need to write a general procedure that examines all the criteria combos, and turns any that have a value in them, into part of this where clause.

Code:

Dim strwhere as string
Private Sub sbBuildFilter
 strWhere = "([EventDate] Between #" & Format(Me.[comboStartDate], "mm/dd/yyyy") & "# AND #" & Format(Me.[comboEnddate], "mm/dd/yyyy") & "#) and "

if not isnull(me.combo1) then
 strwhere = strwhere & "([field1] =" & me.combo1 & ") and "
end if

if not isnull(me.combo2) then
 strwhere = strwhere & "([field2] =" & CHR(34) & me.combo2 & CHR(34) & ") and "
end if
' now remove the trailing and
Strwhere = left(strwhere,Len(strwhere)-4)

End sub

In the above I am assuming that the Date combos will always have something in them
In the above the syntax for combo1 is what is used if field1 is a number. Combo2 syntax is the one to use for a text field.
You need and “and between each part of the where clause, so you add one at the end of each line, then remove the last one at the end.
Note that Strwhere is decalred outside this procedure, so its value can be used elsewhere. This declaration needs to be at the very top of the module, after

Code:

Option Compare Database
Option Explicit

Then modify the the report opening code by calling this procedure first.

Code:

If Not IsNull(Me.ListReports) Then
sbBuildFilter
 stDocName = Me.ListReports
 DoCmd.OpenReport stDocName, acViewPreview,,Strwhere
Else
	MsgBox "Select a Report", vbInformation
End If

Reply | Quote

Here’s a solution that is simple, elegant and easy to maintain. (I’ve attached a sample database)

All you need is a table to hold the names and needs of each report.

Then a form that lists these tables and provides controls for users to enter criteria.

The form uses the table to see what criteria is required for a report and enables the appropriate controls.

For the developer – its a breeze.

Just add the new report to the table and check the fields for required criteria.

If you need a new criteria, then add a new field to the table, a new control to the form and two lines of code to the On Current event of the form.

That’s it.

There is even more detail, like checking that the user has entered all the required criteria in the sample database I’ve added here.

errata – in the code behind the Print button, the line that reads
If IsNull(ctl) Then
should read –
If ctl.Enabled And IsNull(ctl) Then

Reply | Quote

Thanks guys for answering so fast.

Not had much of a chance to work further on this but the command button is wroking fine and opening the reports I want, now just need to link the criteria but don’t know if I’ll get the chance to try this out today but I’ll let you know how I get on so expect more questions soon.

Again thanks for the excellent help I’ve been given so far, helped me no end.

Reply | Quote

Ok so just 1 problem now, I can’t get the criteria on the form to run as the criteria on the report. For example I have

strwhere = “([DateIssued] between #” & Format(Me.Combo0, “mm/dd/yyyy”) & ” AND #” & Format(Me.Combo2, “mm/dd/yyyy”) & “#)”

Where DateIssued is the field name in the query and combo0 and combo2(Yeah, put some real time into naming thins, oh well) are the start and end date.

Cheers

Reply | Quote

Two things

[*]Do you have strwhere at the end of the open report line?
Code:

DoCmd.OpenReport stDocName, acViewPreview,,Strwhere

[*]Put in

Code:

 Msgbox strwhere

immediately before the open report line to tell what is in that variable.

Reply | Quote

Actually just ignore that, I had the ,,strWhere after vbinformation instead of acviewpreveiw, working fine now. Only 1 problem, 1 of the fields I’m using as criteria is a check box and I can’t get that to work. I’ve tried it using the syntax for numbers (as the value it returns is 0 or -1) and the syntax for text (as the chioce I get from the query to the combo box is yes or no) but I can’t get it to work as yet.

Thanks again for all the help guys, I’d have been lost without it.

Reply | Quote

Yes/No fields are a bit of a problem. The user may want to filter for the Yes values, the No values, or not filter on this field at all. So you not to provide three options.
For this reason, I don’t use a check box on the filter form. Instead I use another combo box, with text values Yes or No. It can also be blank.

Then I have this code :

Code:

If Me.comboVisitCompleted = "yes" Then
 	strwhere = strwhere & "( [PracticeVisitComplete] = true) and "
	ElseIf Me.comboVisitCompleted = "No" Then
 	strwhere = strwhere & "([PracticeVisitComplete] = false) and "
	End If

In a related thread, you raise the question of how the reader of a report knows what filters were used to generate it. If the filter is just a date, the solution given there is enough, but once there are a range of variables involved you can use this solution.

You can pass a string to a report using OpenArgs. Pass a second copy of strwhere.

Code:

DoCmd.OpenReport stDocName, acViewPreview,,Strwhere, , strwhere

Then at the top of the report have a Label control (Mine is called LabelFilter) and in the OnOpen event of the REport:

Code:

If Not IsNull(Me.OpenArgs) Then
 Me.LabelFilter.Caption = Me.OpenArgs
Else
 Me.LabelFilter.Caption = "All Doctors"
End If

Sometimes I take this a bit further. When I build the filter, I build two versions of it at once:

[*]The version you have already, formatted the way Access wants it[*]A plain language version, that is more readable. (strTextFilter)

Code:

If Me.comboVisitCompleted = "yes" Then
 	strwhere = strwhere & "( [PracticeVisitComplete] = true) and "
 strTextFilter = strTextFilter & "(Practice Visit Completed) and "
	ElseIf Me.comboVisitCompleted = "No" Then
 	strwhere = strwhere & "([PracticeVisitComplete] = false) and "
	strTextFilter = strTextFilter & "(Practice Visit Not completed ) and "
	End If

It is the second of these that I pass to the report.

Reply | Quote