Scoping Report Output using Parameterized Query

[Guest]

I have the following code on my report:

Private Sub Report_Open(Cancel As Integer)

Dim PONum As String
PONum = "1276" ' Sample number for testing

Dim db As Database
Dim rec As DAO.Recordset
Dim qdfParmQry As DAO.QueryDef

Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("qryPOReportData")
qdfParmQry.Parameters("PONumber") = PONum

Set rec = qdfParmQry.OpenRecordset()

End Sub

The query requires a parameter, and properly scopes the results to that
parameter.
However, when I open my report, this code executes and does nothing, then my
report pops up the standard box asking for my query parameter, then displays
the report with the scoped query info.

What am I doing wrong here?

Thanks,

-David

 

[MGFoster]

I have the following code on my report:

Private Sub Report_Open(Cancel As Integer)

Dim PONum As String
PONum = "1276" ' Sample number for testing

Dim db As Database
Dim rec As DAO.Recordset
Dim qdfParmQry As DAO.QueryDef

Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("qryPOReportData")
qdfParmQry.Parameters("PONumber") = PONum

Set rec = qdfParmQry.OpenRecordset()

End Sub

The query requires a parameter, and properly scopes the results to that
parameter.
However, when I open my report, this code executes and does nothing, then my
report pops up the standard box asking for my query parameter, then displays
the report with the scoped query info.

What am I doing wrong here?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your code is creating one instance of the query results. Then the
report is creating another instance of the query that it uses as its
Recordsource. To get the report's Recordsource query to use the
parameter you want it to use w/o having a pop-up, you can do this
(programatically):

0. Open the report
1. The report open's the form with WindowMode:=acDialog
2. User enters the criteria on the form
3. User clicks an OK button that Hides the form
4. The report reads the criteria form the hidden form and builds a
report filter.
5. When the report closes it also closes the criteria form.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyCk1YechKqOuFEgEQLZkgCeMHq8bczWEwDFNAbKAlUFkYW7/fYAnRP4
ikHFmaZH0su2IDjJm9FaEewu
=GOtp
-----END PGP SIGNATURE-----