report launch pad

[tsluu]

has anyone had experience on a report launch pad where user select a report
from the list and then fill in whatever parameters required and click Go
button to view.

i guess parameters input fields must b dynamic since different reports
required different number of parameters.

maybe suggest different approaches or +ive or -ive points on such method.

 

[Duane Hookom]

I would first create a table of reports that can be displayed in a list box:
ztblReports
================
rptName actual object name
rptTitle short title to display in list box
rptStatus usually "Active", "In Design","Deleted",...
rptAuthor
rptCreateDate
rptDescription Longer description

Then create your form with the list box "lboReport" that displays the title
but is bound to rptName. Add controls for the user to enter or make criteria
selection like:
txtStartDate - date
txtEndDate - date
cboEmpID - numeric
cboDeptCode - text

Then add a command button to run the selected report. The code might look
like:
Dim strWhere as String
Dim strRpt as String
strWhere = "1=1 "
If IsNull(Me.lboReport) Then
Msgbox "You must select a report", vbOkOnly + vbInformation, "PEBKAC"
Else
strRpt = Me.lboReport
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [DateField] >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [DateField] <=#" & _
Me.txtEndDate & "# "
End If
If Not IsNull(Me.cboEmpID) Then
strWhere = strWhere & " AND [EmpID] = " & _
Me.cboEmpID & " "
End If
If Not IsNull(Me.cboDeptCode) Then
strWhere = strWhere & " AND [Dept] = """ & _
Me.cboDeptCode & """ "
End If
DoCmd.OpenReport strRpt, acViewPreview, , strWhere
End If
This code assumes the criteria fields are in the report's record source.

 

[tsluu]

Hi Duane,

Thanks for the reply and code samples. What I did was more or less as what
you suggested. However, I've organised all possible report parameters into
tabs as categories. For example the date tab is date related which require
user the select the appropriate date or date range selection or even month
selection. dependin on the reports, I've dimmed out which tabs that are not
necessary accordingly.

I would first create a table of reports that can be displayed in a list box:
ztblReports
================
rptName actual object name
rptTitle short title to display in list box
rptStatus usually "Active", "In Design","Deleted",...
rptAuthor
rptCreateDate
rptDescription Longer description

Then create your form with the list box "lboReport" that displays the title
but is bound to rptName. Add controls for the user to enter or make criteria
selection like:
txtStartDate - date
txtEndDate - date
cboEmpID - numeric
cboDeptCode - text

Then add a command button to run the selected report. The code might look
like:
Dim strWhere as String
Dim strRpt as String
strWhere = "1=1 "
If IsNull(Me.lboReport) Then
Msgbox "You must select a report", vbOkOnly + vbInformation, "PEBKAC"
Else
strRpt = Me.lboReport
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & " AND [DateField] >=#" & _
Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & " AND [DateField] <=#" & _
Me.txtEndDate & "# "
End If
If Not IsNull(Me.cboEmpID) Then
strWhere = strWhere & " AND [EmpID] = " & _
Me.cboEmpID & " "
End If
If Not IsNull(Me.cboDeptCode) Then
strWhere = strWhere & " AND [Dept] = """ & _
Me.cboDeptCode & """ "
End If
DoCmd.OpenReport strRpt, acViewPreview, , strWhere
End If
This code assumes the criteria fields are in the report's record source.
--
Duane Hookom
Microsoft Access MVP

has anyone had experience on a report launch pad where user select a report
from the list and then fill in whatever parameters required and click Go
button to view.

i guess parameters input fields must b dynamic since different reports
required different number of parameters.

maybe suggest different approaches or +ive or -ive points on such method.