Opening a Report on Recordsets Selected using a Form

  • Thread starter Thread starter JonC
  • Start date Start date
J

JonC

I am developong a database using acces 2003 and have got to a stage
where I have a form with two levels of nested subforms. Recordsets are
selected using a string of linked comboboxes and this is all working
fine. The problem is that I need to be able to print these recordsets
as a report and I can't figure out how to open a report on the
recordset selected using the form. If anyone can help it would save me
a lot of hair that I'm currently pulling out.
 
Hi Jon,

for the main report, you can use the WHERE clause of the OpenReport action

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

ie:

DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"

***

Report Filters

It would be best to build a filter string for the report (as opposed to
imbedding a parameter in a query)--in this way, you can use comboboxes
and listboxes to make it easier for the user to choose criteria and you
can ignore criteria when it has not been specified...

here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...


'~~~~~~~~~~~~~~~~~~

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if


if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter


make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.controlname_for_date2 & "#"
end if
'~~~~~~~~~~~~~~~~~~
***

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Wow thanks a lot Crystal. I'll give it a go. I think a bit of copying
and pasting is in order. Have a great day yourself.
Jon.
Hi Jon,

for the main report, you can use the WHERE clause of the OpenReport action

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

ie:

DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"

***

Report Filters

It would be best to build a filter string for the report (as opposed to
imbedding a parameter in a query)--in this way, you can use comboboxes
and listboxes to make it easier for the user to choose criteria and you
can ignore criteria when it has not been specified...

here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...


'~~~~~~~~~~~~~~~~~~

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if


if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter


make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.controlname_for_date2 & "#"
end if
'~~~~~~~~~~~~~~~~~~
***

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I am developong a database using acces 2003 and have got to a stage
where I have a form with two levels of nested subforms. Recordsets are
selected using a string of linked comboboxes and this is all working
fine. The problem is that I need to be able to print these recordsets
as a report and I can't figure out how to open a report on the
recordset selected using the form. If anyone can help it would save me
a lot of hair that I'm currently pulling out.
Click to expand...
Click to expand...
 
You're welcome, Jon ;)

if you are using OutputTo or attaching the report to an email (or need a
subreport to be filtered), you have to

1. go to the design view of the report
2. set the filter
3. save it
4. THEN, do, for instance, OutputTo ...


'~~~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

'use the hidden parameter to open if you don't want to see it
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save and close the changed report
DoCmd.Close acReport, pReportName,acSaveYes

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub
'~~~~~~~~~~~~~~~

where
pReportName = name of report
pFilter = filter string
******************************

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Wow thanks a lot Crystal. I'll give it a go. I think a bit of copying
and pasting is in order. Have a great day yourself.
Jon.
Hi Jon,

for the main report, you can use the WHERE clause of the OpenReport action

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

ie:

DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"

***

Report Filters

It would be best to build a filter string for the report (as opposed to
imbedding a parameter in a query)--in this way, you can use comboboxes
and listboxes to make it easier for the user to choose criteria and you
can ignore criteria when it has not been specified...

here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport

assuming you are in the code behind the ReportMenu form...


'~~~~~~~~~~~~~~~~~~

'tell Access you are going to create a variable to hold text
dim mFilter as string

'initialize the variable
mFilter = ""

'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.controlname_for_text & "'"
end if

'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.controlname_for_date & "#"
end if

'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.controlname_for_number
end if


if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'~~~~~~~~~~~~~~~~~~

I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks

DateFieldname to show that dates need to be delimited with #

NumericFieldname to show that numbers are not delimited

each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter


make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)

For a Date Range, you would do:

'~~~~~~~~~~~~~~~~~~

If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.controlname_for_date1 & "#"
end if

If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.controlname_for_date2 & "#"
end if
'~~~~~~~~~~~~~~~~~~
***

IF you want to also PRINT the criteria on the report

put a LABEL on your PageFooter
name --> Label_Criteria

then, in the OnFormat event of the ReportHeader

'~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.filter,""))) > 0 then
me.Label_Criteria.Caption = me.filter
me.Label_Criteria.Visible = true
else
me.Label_Criteria.Visible = false
end if
'~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


I am developong a database using acces 2003 and have got to a stage
where I have a form with two levels of nested subforms. Recordsets are
selected using a string of linked comboboxes and this is all working
fine. The problem is that I need to be able to print these recordsets
as a report and I can't figure out how to open a report on the
recordset selected using the form. If anyone can help it would save me
a lot of hair that I'm currently pulling out.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top