Subreport ControlSource

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top