By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,109 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,109 IT Pros & Developers. It's quick & easy.

QBF search criteria to report

P: n/a
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?

Aug 17 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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?

Aug 17 '06 #2

P: n/a
Thank you very much Allen,
It works great now.

Aug 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.