Re: QBF search criteria to report

There are a couple of different ways that QBF forms typically work:
a) The query contains criteria such as:
[Forms].[Form1].[Text0]
b) You leave the criteria out of the query, and build a Filter string to
apply to your form (and the WhereCondition of OpenReport.)

If you use (b), you can build another string at the same time, which is a
description of the filter in plain English. You can then pass this
description to the report, and display it there.

If you would like an example of how to make a form using (b), see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

In Access 2002 and later, you can pass the description in the OpenArgs of
the report. In older versions, we use a public string variable to hold the
description, and then use the report's Open event to read and clear the
string and display it on the report.

The code below illustrates how to do this in Access 2000 or later. Use the
custom function OpenTheReport() instead of DoCmd.OpenReport. Advantages:
- Defaults to Preview.
- The broken Filter argument of OpenReport is gone.
- The code that calls it does not need to handle error 2501 if the report is
cancelled.
- Still lets you know if the report was opened, i.e. check the return value.

The code goes in a standard module:

Option Explicit
Public gstrTitleInHeader As String

Public Function OpenTheReport(strDoc As String, _
Optional lngMode As AcView = acViewPreview, _
Optional strWhere As String, _
Optional strDescrip As String) As Boolean
On Error GoTo Err_OpenTheReport
'Purpose: Wrapper for OpenReport that closes report if already open.
'Return: True if the report was opened.
'Usage: Called from Form_frmReport.PrintIt()
'Arguments: strDoc = name of report.
' lngMode = acViewNormal or acViewPreview.
' strWhere = the WhereCondition to apply.
' strDescrip = description of the WhereCondition to use in
the header of the report. See TitleInHeader().

If CurrentProject.AllReports(strDoc.).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

gstrTitleInHeader = strDescrip

DoCmd.OpenReport strDoc, lngMode, , strWhere
OpenTheReport = True

Exit_OpenTheReport:
Exit Function

Err_OpenTheReport:
MsgBox "Error " & Err.Number & ": " & Err.Description, "OpenTheReport()"
Resume Exit_OpenTheReport
End Function

Public Function TitleInHeader(rpt As Report) As Boolean
On Error GoTo Err_TitleInHeader
'Purpose: Read the caption from Forms.frmReport.lblTitle into
' this report's txtWhereDescrip.
'Usage: In ReportHeader's OnFormat property:
' =TitleInHeader([Report]
'Note: Report header must have text box named txtWhereDescrip.
' Can be set before calling the OpenReport action.
Dim strName As String

strName = rpt.Name

If Len(gstrTitleInHeader) > 0& Then
rpt.txtWhereDescrip = gstrTitleInHeader
End If
'Reset the string.
gstrTitleInHeader = vbNullString

TitleInHeader = True

Exit_TitleInHeader:
Exit Function

Err_TitleInHeader:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_TitleInHeader
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rinmanb70" <emailchrisco@xxxxxxxxx> wrote in message
news:1155796218.980441.78770@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a QBF form/query and a report from the QBF that shows the
results of the QBF. I would like to show the criteria on the report
that was used in the QBF to get the info on report. I can't find how
to do this except to show the "hard for users to understand" SQL WHERE
statement. Anyone have a better way?


.

Relevant Pages

  • Re: Running 2 reports in a form together
    ... Form where the report is run from is called "clientanddate" ... Dim strReport As String 'Name of report to open. ... here is an example that tests criteria and builds a filter string to use as a parameter in OpenReport ...
    (microsoft.public.access.forms)
  • Re: Running 2 reports in a form together
    ... Form where the report is run from is called "clientanddate" ... Dim strReport As String 'Name of report to open. ... What I need it to do is also filter in the date report using the clients name ... here is an example that tests criteria and builds a filter string to use ...
    (microsoft.public.access.forms)
  • Re: Running 2 reports in a form together
    ... here is an example that tests criteria and builds a filter string to use as a parameter in OpenReport ... Since a filter is applied on the recordset, they do not have to be on the report object ...
    (microsoft.public.access.forms)
  • RE: Need help with looping through records to email report
    ... Assuming that the report returns all of the records pretaining to this ... and email it to each employee. ... Dim rst As DAO.Recordset ... Dim strAcountStatus As String ...
    (microsoft.public.access.forms)
  • Re: SnapTheReport -- made it generic | example useage
    ... Sub SnapTheReport(pReportName As String, pFilter As String) ... ' pFilter = string to filter report or "" to get all ... Dim mFilename As String ... SetReportFilter pReportName, pFilter ...
    (microsoft.public.access.formscoding)