Form to report

[thewabit via AccessMonster.com]

I have a form with a listbox and 2 graphs. When an item is selected on the
listbox, the chart displays the appropriate informatiom. But when the
"preview report" button is selcted at the bottom, the form displays graphs
for ALL items in the listbox...not the one selected. I am missing passing
something to the report but I don't know what.

Here is the code behind the button:

Private Sub Command20_Click()
Dim stDocName As String

stDocName = "rptTrend_HF"
DoCmd.OpenReport stDocName, acPreview

End Sub

 

[thewabit via AccessMonster.com]

I meant REPORT displays graphs for ALL items....sorry

 

[John W. Vinson]

I have a form with a listbox and 2 graphs. When an item is selected on the
listbox, the chart displays the appropriate informatiom. But when the
"preview report" button is selcted at the bottom, the form displays graphs
for ALL items in the listbox...not the one selected. I am missing passing
something to the report but I don't know what.

Here is the code behind the button:

Private Sub Command20_Click()
Dim stDocName As String

stDocName = "rptTrend_HF"
DoCmd.OpenReport stDocName, acPreview

End Sub

What's the Report's Recordsource?

You *MIGHT* (I'm guessing, I don't know what's in your database) be able to
use something like

DoCmd.OpenReport stDocName, acPreview, , "[Somefield] = " & Me!listboxname

to specify the "Where Condition" argument of OpenReport, limiting the report
to those records where some field (you'ld know which better than I) matches
the value in some listbox (ditto).

 

[thewabit via AccessMonster.com]

The record source is a query.

Ok...that makes some sense. You say "match some value in the listbox". How do
I know the value of something that is highlighted in the list box of the form?
The list box shows the results of a query.

Thanks John.

I have a form with a listbox and 2 graphs. When an item is selected on the
listbox, the chart displays the appropriate informatiom. But when the

[quoted text clipped - 11 lines]

End Sub

What's the Report's Recordsource?

You *MIGHT* (I'm guessing, I don't know what's in your database) be able to
use something like

DoCmd.OpenReport stDocName, acPreview, , "[Somefield] = " & Me!listboxname

to specify the "Where Condition" argument of OpenReport, limiting the report
to those records where some field (you'ld know which better than I) matches
the value in some listbox (ditto).

 

[PieterLinden via AccessMonster.com]

something like this will work...

Private Sub cmdShowSelected_Click()
Dim varItem As Variant
Dim strFilter As String
Const strDelim As String = "'"

For Each varItem In Me.List0.ItemsSelected
strFilter = strFilter & ", " & strDelim & Me.List0.ItemData(varItem)
& strDelim
Next varItem

strFilter = "[PersonName] IN (" & Right$(strFilter, Len(strFilter) - 2) &
")"
DoCmd.OpenReport "rptEmployees", acViewPreview, , strFilter,
acWindowNormal


End Sub

Data type & delimiter
Date #
Text '
Numbers <nothing>

 

[thewabit via AccessMonster.com]

I'm sorry...that makes no sense. I know i am showing my "newbieness" but that
generated all kinds of errors. What of this needs modification for my data?
Probably the report name for sure.

something like this will work...

Private Sub cmdShowSelected_Click()
Dim varItem As Variant
Dim strFilter As String
Const strDelim As String = "'"

For Each varItem In Me.List0.ItemsSelected
strFilter = strFilter & ", " & strDelim & Me.List0.ItemData(varItem)
& strDelim
Next varItem

strFilter = "[PersonName] IN (" & Right$(strFilter, Len(strFilter) - 2) &
")"
DoCmd.OpenReport "rptEmployees", acViewPreview, , strFilter,
acWindowNormal


End Sub

Data type & delimiter
Date #
Text '
Numbers <nothing>

 

[John W. Vinson]

The record source is a query.

Ok...that makes some sense. You say "match some value in the listbox". How do
I know the value of something that is highlighted in the list box of the form?
The list box shows the results of a query.

Well... you know, but I don't. I cannot see either the listbox's query or the
report's query.

Is this a single-select listbox or a multiselect? I.e. do you want to pick A
VALUE from the listbox, or do you want to pick several?

What is the Rowsource of the listbox? Post the SQL. Also post the listbox's
Bound Column property.
What is the Recordsource of the report? ditto.
What field in the listbox identifies the record (or records) that you want in
the report?

 

[thewabit via AccessMonster.com]

It is a single-select listbox.

Here is the rowsource of the listbox ...SQL:

SELECT DISTINCT qryLOSATrend_HF.HumanFactor AS [Human Factor], Sum
(qryLOSATrend_HF.HumanFactorQTY) AS QTY, qryLOSATrend_HF.Year
FROM qryLOSATrend_HF
GROUP BY qryLOSATrend_HF.HumanFactor, qryLOSATrend_HF.Year
HAVING (((qryLOSATrend_HF.Year)=[Forms]![frmLOSA_Chart_Trend]![Year]))
ORDER BY Sum(qryLOSATrend_HF.HumanFactorQTY) DESC;


Bound column is 1.

Here is the recordsorce of the report:

SELECT Format([obdate],"mmm") & "-" & Format(Year([obdate]),"00") AS OBMonth,
tblLOSA_Details.HumanFactor, Count(tblLOSA_Details.HumanFactor) AS
HumanFactorQTY, tblLOSA_Details.RiskLevel, Year([obdate]) AS [Year],
tblLOSA_Details.ErrorType, Count(tblLOSA_Details.ErrorType) AS ErrorTypeQTY
FROM tblObservations INNER JOIN tblLOSA_Details ON tblObservations.ObID =
tblLOSA_Details.ObID
GROUP BY Format([obdate],"mmm") & "-" & Format(Year([obdate]),"00"),
tblLOSA_Details.HumanFactor, tblLOSA_Details.RiskLevel, Year([obdate]),
tblLOSA_Details.ErrorType, (Format([obdate],"mmm-yy"));

Here is the rowsource of the CHART in the report:

SELECT qryLOSATrend_HF.OBMonth, Sum(qryLOSATrend_HF.HumanFactorQTY) AS
SumOfHumanFactorQTY
FROM qryLOSATrend_HF
GROUP BY qryLOSATrend_HF.OBMonth;

The field I want in the report is what is in the listbox query, column 1: QTY:
HumanFactorQTY