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" <em**********@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
>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?
