Setting report RowSource from Form

J

Jason

I have a form used to open a report and I need to set the RowSource of a
chart on the report based on a combo box on the form. The problem I'm having
is that if I try to set it before I open the form then I get an error that
the form is not open. If I try to do it after opening, then I get an error
that you can't set the RowSource after printing has begun.

I'm thinking I need to pass my SQL string to the report somehow and then use
it maybe in the OnOpen event? Here's the code I'm trying to use...

Private Sub cmdOpenrptMonthlyComparison_Click()
On Error GoTo Err_cmdOpenrptMonthlyComparison_Click

'Set SQL for report to show only selected department
Dim strSQL As String
Dim strDept As String

strDept = Forms!frmDept!cmbDept
strSQL = "SELECT qryPlantCrosstab.[Comment Subcategory],
Sum(qryPlantCrosstab.Jul) AS Jul, Sum(qryPlantCrosstab.Aug) AS Aug,
Sum(qryPlantCrosstab.Sep) AS Sep, Sum(qryPlantCrosstab.Oct) AS Oct,
Sum(qryPlantCrosstab.Nov) AS Nov, Sum(qryPlantCrosstab.Dec) AS [Dec],
Sum(qryPlantCrosstab.Jan) AS Jan, Sum(qryPlantCrosstab.Feb) AS Feb,
Sum(qryPlantCrosstab.Mar) AS Mar, Sum(qryPlantCrosstab.Apr) AS Apr,
Sum(qryPlantCrosstab.May) AS May, Sum(qryPlantCrosstab.Jun) AS Jun " & _
"FROM qryPlantCrosstab " & _
"WHERE (((qryPlantCrosstab.Classification) = " & Chr(34) &
strDept & Chr(34) & "))" & _
"GROUP BY qryPlantCrosstab.[Comment Subcategory] " & _
"ORDER BY Sum(qryPlantCrosstab.Jan) DESC;"

'Set the Source for the chart to the SQL created and open the report



Dim stDocName As String
Reports!rptMonthlyComparison.OLEUnbound0.RowSource = strSQL
stDocName = "rptMonthlyComparison"
DoCmd.OpenReport stDocName, acPreview


Exit_cmdOpenrptMonthlyComparison_Click:
Exit Sub

Err_cmdOpenrptMonthlyComparison_Click:
MsgBox Err.Description
Resume Exit_cmdOpenrptMonthlyComparison_Click

End Sub
 
J

Jason

Okay - got a piece of it. If I open it first in design mode, I can make the
change and then open it in Preview.

Now my problem is that when I close the report, it asks me if I want to save
my changes. How do I suppress this warning? docmd.SetWarnings (false) doesn't
seem to work.
 
K

Ken Snell \(MVP\)

If you're using ACCESS 2002 or ACCESS 2003, you can pass the SQL string in
the OpenArgs argument of the DoCmd.OpenReport method, then use the report's
Open event to set the Recordsource to that SQL string.

DoCmd.OpenReport stDocName, acPreview, , , , strSQL


Then this would be the Open event procedure for the report:

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then Me.RecordSource = Me.OpenArgs
End Sub

--

Ken Snell
<MS ACCESS MVP>



Jason said:
Okay - got a piece of it. If I open it first in design mode, I can make
the
change and then open it in Preview.

Now my problem is that when I close the report, it asks me if I want to
save
my changes. How do I suppress this warning? docmd.SetWarnings (false)
doesn't
seem to work.
--
Thanks,

Jason


Jason said:
I have a form used to open a report and I need to set the RowSource of a
chart on the report based on a combo box on the form. The problem I'm
having
is that if I try to set it before I open the form then I get an error
that
the form is not open. If I try to do it after opening, then I get an
error
that you can't set the RowSource after printing has begun.

I'm thinking I need to pass my SQL string to the report somehow and then
use
it maybe in the OnOpen event? Here's the code I'm trying to use...

Private Sub cmdOpenrptMonthlyComparison_Click()
On Error GoTo Err_cmdOpenrptMonthlyComparison_Click

'Set SQL for report to show only selected department
Dim strSQL As String
Dim strDept As String

strDept = Forms!frmDept!cmbDept
strSQL = "SELECT qryPlantCrosstab.[Comment Subcategory],
Sum(qryPlantCrosstab.Jul) AS Jul, Sum(qryPlantCrosstab.Aug) AS Aug,
Sum(qryPlantCrosstab.Sep) AS Sep, Sum(qryPlantCrosstab.Oct) AS Oct,
Sum(qryPlantCrosstab.Nov) AS Nov, Sum(qryPlantCrosstab.Dec) AS [Dec],
Sum(qryPlantCrosstab.Jan) AS Jan, Sum(qryPlantCrosstab.Feb) AS Feb,
Sum(qryPlantCrosstab.Mar) AS Mar, Sum(qryPlantCrosstab.Apr) AS Apr,
Sum(qryPlantCrosstab.May) AS May, Sum(qryPlantCrosstab.Jun) AS Jun " & _
"FROM qryPlantCrosstab " & _
"WHERE (((qryPlantCrosstab.Classification) = " & Chr(34) &
strDept & Chr(34) & "))" & _
"GROUP BY qryPlantCrosstab.[Comment Subcategory] " & _
"ORDER BY Sum(qryPlantCrosstab.Jan) DESC;"

'Set the Source for the chart to the SQL created and open the report



Dim stDocName As String
Reports!rptMonthlyComparison.OLEUnbound0.RowSource = strSQL
stDocName = "rptMonthlyComparison"
DoCmd.OpenReport stDocName, acPreview


Exit_cmdOpenrptMonthlyComparison_Click:
Exit Sub

Err_cmdOpenrptMonthlyComparison_Click:
MsgBox Err.Description
Resume Exit_cmdOpenrptMonthlyComparison_Click

End Sub
Click to expand...
Click to expand...
 
J

Jason

Thanks for the response Ken. So I can't pass arguments using Access 2000?
--
Thanks,

Jason


Ken Snell (MVP) said:
If you're using ACCESS 2002 or ACCESS 2003, you can pass the SQL string in
the OpenArgs argument of the DoCmd.OpenReport method, then use the report's
Open event to set the Recordsource to that SQL string.

DoCmd.OpenReport stDocName, acPreview, , , , strSQL


Then this would be the Open event procedure for the report:

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then Me.RecordSource = Me.OpenArgs
End Sub

--

Ken Snell
<MS ACCESS MVP>



Jason said:
Okay - got a piece of it. If I open it first in design mode, I can make
the
change and then open it in Preview.

Now my problem is that when I close the report, it asks me if I want to
save
my changes. How do I suppress this warning? docmd.SetWarnings (false)
doesn't
seem to work.
--
Thanks,

Jason


Jason said:
I have a form used to open a report and I need to set the RowSource of a
chart on the report based on a combo box on the form. The problem I'm
having
is that if I try to set it before I open the form then I get an error
that
the form is not open. If I try to do it after opening, then I get an
error
that you can't set the RowSource after printing has begun.

I'm thinking I need to pass my SQL string to the report somehow and then
use
it maybe in the OnOpen event? Here's the code I'm trying to use...

Private Sub cmdOpenrptMonthlyComparison_Click()
On Error GoTo Err_cmdOpenrptMonthlyComparison_Click

'Set SQL for report to show only selected department
Dim strSQL As String
Dim strDept As String

strDept = Forms!frmDept!cmbDept
strSQL = "SELECT qryPlantCrosstab.[Comment Subcategory],
Sum(qryPlantCrosstab.Jul) AS Jul, Sum(qryPlantCrosstab.Aug) AS Aug,
Sum(qryPlantCrosstab.Sep) AS Sep, Sum(qryPlantCrosstab.Oct) AS Oct,
Sum(qryPlantCrosstab.Nov) AS Nov, Sum(qryPlantCrosstab.Dec) AS [Dec],
Sum(qryPlantCrosstab.Jan) AS Jan, Sum(qryPlantCrosstab.Feb) AS Feb,
Sum(qryPlantCrosstab.Mar) AS Mar, Sum(qryPlantCrosstab.Apr) AS Apr,
Sum(qryPlantCrosstab.May) AS May, Sum(qryPlantCrosstab.Jun) AS Jun " & _
"FROM qryPlantCrosstab " & _
"WHERE (((qryPlantCrosstab.Classification) = " & Chr(34) &
strDept & Chr(34) & "))" & _
"GROUP BY qryPlantCrosstab.[Comment Subcategory] " & _
"ORDER BY Sum(qryPlantCrosstab.Jan) DESC;"

'Set the Source for the chart to the SQL created and open the report



Dim stDocName As String
Reports!rptMonthlyComparison.OLEUnbound0.RowSource = strSQL
stDocName = "rptMonthlyComparison"
DoCmd.OpenReport stDocName, acPreview


Exit_cmdOpenrptMonthlyComparison_Click:
Exit Sub

Err_cmdOpenrptMonthlyComparison_Click:
MsgBox Err.Description
Resume Exit_cmdOpenrptMonthlyComparison_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
C

Charles Wang[MSFT]

Hi Jason,

For your question,
"Now my problem is that when I close the report, it asks me if I want to
save
my changes. How do I suppress this warning? ",

Please try using DoCmd.Save to save your report before you execute
DoCmd.OpenReport to open the report.

Hope this helps.

Charles Wang
Microsoft Online Community Support
=====================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications

If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.


Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 
K

Ken Snell \(MVP\)

No. What you do in ACCESS 2000 is to put a hidden textbox on the form, write
the SQL string to that textbox, then use the report's Open event to test if
the form is open, and if it is, read the SQL string from that textbox and
set your report's RecordSource to that string value.

--

Ken Snell
<MS ACCESS MVP>

Jason said:
Thanks for the response Ken. So I can't pass arguments using Access 2000?
--
Thanks,

Jason


Ken Snell (MVP) said:
If you're using ACCESS 2002 or ACCESS 2003, you can pass the SQL string
in
the OpenArgs argument of the DoCmd.OpenReport method, then use the
report's
Open event to set the Recordsource to that SQL string.

DoCmd.OpenReport stDocName, acPreview, , , , strSQL


Then this would be the Open event procedure for the report:

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then Me.RecordSource = Me.OpenArgs
End Sub

--

Ken Snell
<MS ACCESS MVP>



Jason said:
Okay - got a piece of it. If I open it first in design mode, I can make
the
change and then open it in Preview.

Now my problem is that when I close the report, it asks me if I want to
save
my changes. How do I suppress this warning? docmd.SetWarnings (false)
doesn't
seem to work.
--
Thanks,

Jason


:

I have a form used to open a report and I need to set the RowSource of
a
chart on the report based on a combo box on the form. The problem I'm
having
is that if I try to set it before I open the form then I get an error
that
the form is not open. If I try to do it after opening, then I get an
error
that you can't set the RowSource after printing has begun.

I'm thinking I need to pass my SQL string to the report somehow and
then
use
it maybe in the OnOpen event? Here's the code I'm trying to use...

Private Sub cmdOpenrptMonthlyComparison_Click()
On Error GoTo Err_cmdOpenrptMonthlyComparison_Click

'Set SQL for report to show only selected department
Dim strSQL As String
Dim strDept As String

strDept = Forms!frmDept!cmbDept
strSQL = "SELECT qryPlantCrosstab.[Comment Subcategory],
Sum(qryPlantCrosstab.Jul) AS Jul, Sum(qryPlantCrosstab.Aug) AS Aug,
Sum(qryPlantCrosstab.Sep) AS Sep, Sum(qryPlantCrosstab.Oct) AS Oct,
Sum(qryPlantCrosstab.Nov) AS Nov, Sum(qryPlantCrosstab.Dec) AS [Dec],
Sum(qryPlantCrosstab.Jan) AS Jan, Sum(qryPlantCrosstab.Feb) AS Feb,
Sum(qryPlantCrosstab.Mar) AS Mar, Sum(qryPlantCrosstab.Apr) AS Apr,
Sum(qryPlantCrosstab.May) AS May, Sum(qryPlantCrosstab.Jun) AS Jun " &
_
"FROM qryPlantCrosstab " & _
"WHERE (((qryPlantCrosstab.Classification) = " & Chr(34)
&
strDept & Chr(34) & "))" & _
"GROUP BY qryPlantCrosstab.[Comment Subcategory] " & _
"ORDER BY Sum(qryPlantCrosstab.Jan) DESC;"

'Set the Source for the chart to the SQL created and open the report



Dim stDocName As String
Reports!rptMonthlyComparison.OLEUnbound0.RowSource = strSQL
stDocName = "rptMonthlyComparison"
DoCmd.OpenReport stDocName, acPreview


Exit_cmdOpenrptMonthlyComparison_Click:
Exit Sub

Err_cmdOpenrptMonthlyComparison_Click:
MsgBox Err.Description
Resume Exit_cmdOpenrptMonthlyComparison_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
C

Charles Wang[MSFT]

Hi,

Just check with you to see if the suggestions were helpful. Please let us
know if you would like further assistance.

Have a great day!


Charles Wang
Microsoft Online Community Support
 
C

Candyman

Jason,
I was just looking for some other stuff, and what they say is right, but
would'nt it be easier to track by writing a query and name it for the report
then pass the parameters into the query? ie. qry_rptMyReport

Then you can see the underlying SQL without going through code.

I open my reports using a listbox. On the form I drop down parameters and
have also named the Parmeters and Paramter labels very carefully. before the
report runs I gather the parameters to post to the report..

Private Function rptParameters()
Dim tName, strPRAM, SQLpram As String
Dim tValue As Variant

Dim c As Control
' On Error GoTo errLabelCheck
For Each c In Me.Controls
If UCase(Mid(c.Name, 4, 4)) = "PRAM" Then
tValue = ""
tName = "lbl" & Mid(c.Name, 8)
If UCase(Mid(c.Name, 8)) = "ACTIVE" Then
If c.value = -1 Then tValue = "Yes"
If c.value = 0 Then tValue = "No"
Else
tValue = c.value
End If
If c.value <> "" Then
If Len(strPRAM) > 0 Then
strPRAM = strPRAM & "; " & Me.Controls(tName).Caption &
": '" & tValue & "'"
Else
strPRAM = "( Parameters: " & Me.Controls(tName).Caption
& ": '" & tValue & "'"
End If
End If
End If
Next c
If Len(strPRAM) > 0 Then strPRAM = strPRAM & " )"
rptParameters = strPRAM
Exit Function
errLabelCheck:
MsgBox ("Parameter Labels must equate parameters. See established
pattern.")
rptParameters = ""
Exit Function
End Function

When the report is selected then Ipass those parametrs to the report.
rptPRAM = rptParameters
DoCmd.OpenReport Me.lstReports, IIf(Me.chkPrint.value, acViewNormal,
acViewPreview), , SQLpram, acWindowNormal, rptPRAM

when the report opens, i also use a blank text box called lblParameters to
list the report parameters using this code in the OnOpen action for the
report:
Private Sub Report_Open(Cancel As Integer)
Me.lblParameters.Caption = Nz(Me.OpenArgs, "")
End Sub

works cool!

I'm new at this.

Just tid bits

Jason said:
I have a form used to open a report and I need to set the RowSource of a
chart on the report based on a combo box on the form. The problem I'm having
is that if I try to set it before I open the form then I get an error that
the form is not open. If I try to do it after opening, then I get an error
that you can't set the RowSource after printing has begun.

I'm thinking I need to pass my SQL string to the report somehow and then use
it maybe in the OnOpen event? Here's the code I'm trying to use...

Private Sub cmdOpenrptMonthlyComparison_Click()
On Error GoTo Err_cmdOpenrptMonthlyComparison_Click

'Set SQL for report to show only selected department
Dim strSQL As String
Dim strDept As String

strDept = Forms!frmDept!cmbDept
strSQL = "SELECT qryPlantCrosstab.[Comment Subcategory],
Sum(qryPlantCrosstab.Jul) AS Jul, Sum(qryPlantCrosstab.Aug) AS Aug,
Sum(qryPlantCrosstab.Sep) AS Sep, Sum(qryPlantCrosstab.Oct) AS Oct,
Sum(qryPlantCrosstab.Nov) AS Nov, Sum(qryPlantCrosstab.Dec) AS [Dec],
Sum(qryPlantCrosstab.Jan) AS Jan, Sum(qryPlantCrosstab.Feb) AS Feb,
Sum(qryPlantCrosstab.Mar) AS Mar, Sum(qryPlantCrosstab.Apr) AS Apr,
Sum(qryPlantCrosstab.May) AS May, Sum(qryPlantCrosstab.Jun) AS Jun " & _
"FROM qryPlantCrosstab " & _
"WHERE (((qryPlantCrosstab.Classification) = " & Chr(34) &
strDept & Chr(34) & "))" & _
"GROUP BY qryPlantCrosstab.[Comment Subcategory] " & _
"ORDER BY Sum(qryPlantCrosstab.Jan) DESC;"

'Set the Source for the chart to the SQL created and open the report



Dim stDocName As String
Reports!rptMonthlyComparison.OLEUnbound0.RowSource = strSQL
stDocName = "rptMonthlyComparison"
DoCmd.OpenReport stDocName, acPreview


Exit_cmdOpenrptMonthlyComparison_Click:
Exit Sub

Err_cmdOpenrptMonthlyComparison_Click:
MsgBox Err.Description
Resume Exit_cmdOpenrptMonthlyComparison_Click

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

coding a report from a form. 2
Between Dates on Combo Form 10
Report not opening correctly 4
Report Menu - Multi select Listbox Filter? 0
VBA in form to substitute for parameters 0
SubForm - Setting The Rowsource 1
Change a report to a query 1
Copy SQL from query - paste in form 2

Top