Re: Subreport ControlSource

From: Duane Hookom (duanehookom_at_NO_SPAMhotmail.com)
Date: 12/28/04 

Date: Mon, 27 Dec 2004 22:52:19 -0600

You can set your subreport's record source to a saved query. Assuming the
query name is "qselMySubRS". You would then run some code prior to opening
your main report that would use something similar to your multiple-select
code like:
    Dim strSQL as String
    strSQL = "SELECT * FROM tbl... JOIN tbl... On ....WHERE " & _
       "the result of your multi-select code here" & ";"
    CurrentDb.QueryDefs("qselMySubRS").SQL = strSQL 

-- 
Duane Hookom
MS Access MVP
"geebee" <geebee@discussions.microsoft.com> wrote in message 
news:6B2B0064-F495-499C-A7A3-CA2E68737EAB@microsoft.com...
> SOrry for seeming like a baby, but I'm not understanding your reply.  I 
> mean,
> what steps do i take. Write it out for me.  Do you need more of my code or
> further explanation?
>
>
> "Duane Hookom" wrote:
>
>> The method I use sets the SQL property of the subreport's query records
>> source. You would use code like you have to build the where clause of a 
>> sql
>> statement. Then use DAO to change the SQL property:
>> Currentdb.QueryDefs("YourSubreportQuery").SQL = strSQL
>> This should be done prior to the main report opening.
>>
>> -- 
>> Duane Hookom
>> MS Access MVP
>>
>>
>> "geebee" <geebee@discussions.microsoft.com> wrote in message
>> news:98BAD282-E1DC-45A9-911D-E33102B84A07@microsoft.com...
>> >I have a form which users can use to open a report.  They can select
>> >program
>> > values from the [programvalue] field, then click the "view" button, 
>> > which
>> > has
>> > the following OnClick:
>> >
>> > For Each varselected In Me.programvalue.ItemsSelected
>> >        strSQL = strSQL & "'" & Me.programvalue.ItemData(varselected) &
>> > "',"
>> >        strSQL2 = strSQL2 & "'" & Me.programvalue.ItemData(varselected) 
>> > &
>> > "',"
>> >    Next varselected
>> >              strSQL = "[program] IN (" & Left(strSQL, Len(strSQL) - 0) 
>> > &
>> > ")"
>> >        strSQL2 = "[program] IN (" & Left(strSQL2, Len(strSQL2) - 0) & 
>> > ")"
>> >
>> >
>> >  DoCmd.OpenReport "Obligations/Expenditures By Month", acPreview, , 
>> > strSQL
>> >
>> > The report has been opening fine showing information for program values
>> > selected.  However, I ave recently added a subreport.  I want to make 
>> > sure
>> > that the subreport shows ONLY information for the programs selected 
>> > also.
>> > Currently, it is only showing information for the first program 
>> > selected.
>> > How can I get the subreport to show ALL and ONLY the values selected in
>> > the
>> > report criteria form?
>> >
>> > Thanks in advance,
>> > geebee
>> >
>>
>>
>>
Quantcast