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

SubQuery parameter for report

P: n/a
I've got a report whose recordsource is a query:

SELECT [Company Information].Company,
qryGatherCreditCardInfoFromCallRecords.*
FROM qryGatherCreditCardInfoFromCallRecords LEFT JOIN ([Number Cross
Reference] LEFT JOIN [Company Information] ON [Number Cross
Reference].[Property Code]=[Company Information].[Property Code]) ON
qryGatherCreditCardInfoFromCallRecords.[Number ID]=[Number Cross
Reference].[Number ID];

The subquery, qryGatherCreditCardInfoFromCallRecords, takes 1
parameter, [strCreditCardNo].
When opening this report via VBA, how can I pass a value to this
parameter, then open the report based on these results?
Something like this:

'open the report so can fiddle with recordsource
DoCmd.Echo False 'hides the report when it opens
DoCmd.OpenReport strReportName, acViewDesign

Dim objQuery As QueryDef
Set objQuery =
CurrentDb().QueryDefs(Reports(strReportName).Recor dSource)

'Set up the query parameter, [CreditCardNo]
objQuery.Parameters!strCreditCardNo = Trim(strCreditCardNumber)

Reports(strReportName).RecordSource = objQuery.????
Thanks, gang!

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 24 Dec 2004 14:06:36 -0800, "mark"
<ma****************@bloglines.com> wrote:

Make the parameter look at a field on a guaranteed-open form for its
value:
select * from mytable where myfield=forms!myForm!myControl

The form can be hidden. One option is to open that hidden form at
startup time, and leave it open for the duration of the session.

-Tom.

I've got a report whose recordsource is a query:

SELECT [Company Information].Company,
qryGatherCreditCardInfoFromCallRecords.*
FROM qryGatherCreditCardInfoFromCallRecords LEFT JOIN ([Number Cross
Reference] LEFT JOIN [Company Information] ON [Number Cross
Reference].[Property Code]=[Company Information].[Property Code]) ON
qryGatherCreditCardInfoFromCallRecords.[Number ID]=[Number Cross
Reference].[Number ID];

The subquery, qryGatherCreditCardInfoFromCallRecords, takes 1
parameter, [strCreditCardNo].
When opening this report via VBA, how can I pass a value to this
parameter, then open the report based on these results?
Something like this:

'open the report so can fiddle with recordsource
DoCmd.Echo False 'hides the report when it opens
DoCmd.OpenReport strReportName, acViewDesign

Dim objQuery As QueryDef
Set objQuery =
CurrentDb().QueryDefs(Reports(strReportName).Reco rdSource)

'Set up the query parameter, [CreditCardNo]
objQuery.Parameters!strCreditCardNo = Trim(strCreditCardNumber)

Reports(strReportName).RecordSource = objQuery.????
Thanks, gang!


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.