SubQuery parameter for report

[mark]

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

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

The subquery, qryGatherCredit CardInfoFromCal lRecords, 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.OpenRepor t strReportName, acViewDesign

Dim objQuery As QueryDef
Set objQuery =
CurrentDb().Que ryDefs(Reports( strReportName). RecordSource)

'Set up the query parameter, [CreditCardNo]
objQuery.Parame ters!strCreditC ardNo = Trim(strCreditC ardNumber)

Reports(strRepo rtName).RecordS ource = objQuery.????
Thanks, gang!

[Tom van Stiphout]

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!m yForm!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,
qryGatherCredi tCardInfoFromCa llRecords.*
FROM qryGatherCredit CardInfoFromCal lRecords LEFT JOIN ([Number Cross
Reference] LEFT JOIN [Company Information] ON [Number Cross
Reference].[Property Code]=[Company Information].[Property Code]) ON
qryGatherCredi tCardInfoFromCa llRecords.[Number ID]=[Number Cross
Reference].[Number ID];

The subquery, qryGatherCredit CardInfoFromCal lRecords, 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.OpenRepo rt strReportName, acViewDesign

Dim objQuery As QueryDef
Set objQuery =
CurrentDb().Qu eryDefs(Reports (strReportName) .RecordSource)

'Set up the query parameter, [CreditCardNo]
objQuery.Param eters!strCredit CardNo = Trim(strCreditC ardNumber)

Reports(strRep ortName).Record Source = objQuery.????
Thanks, gang!