Listbox outputs multiple reports - collating into one report

[dd]

Regarding my post, titled, "I need a form to select the items to be
presented in the report" at M.P.A.Formscoding, where SteveS very kindly
helped me with the Form:

I have a Building Inspection Database which contains survey data. Using a
form, a query and a report - the form will output multiple condition-survey
reports for each site selected from the listbox of sites. How do I combine
these reports into a single report?

Regards
D Dawson

 

[Duane Hookom]

If I understand correctly, you can use the selected sites to build a where
clause like:
"[Site] In ('sitea','siteb','sitec','siter')"
Use this as the where clause to open a report.

 

[dd]

< If I understand correctly, you can use the selected sites to build a where
< clause like:
< "[Site] In ('sitea','siteb','sitec','siter')"
< Use this as the where clause to open a report.


Duane

I'm not sure what you mean, but I enclose further info regarding the query
and form as it stands.

Please elaborate further. I know a little Access and a little VBA. If I
can't work this out, then I will definitely learn from it.

My query is as follows:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;

This basically selects everything, unless I add a Like[] statement to the
Site field.

My form preview/print code is as follows:

Private Sub btnPrintReport_Click()
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If


DoCmd.OpenReport "rptSiteSurvey", intView, , "[Site] Like '" &
strCriteria & "*'", intWindowMode


Next vItm
End Sub


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message

[SNIP]

 

[Duane Hookom]

You can use code something like:

strCriteria = "[Site] In ("
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = strCriteria & " '" &
Trim(Me!mslbxSites.ItemData(vItm)) & "', "
Next vItm
strCriteria = Left(strCriteria, Len(strCriteria)-2) & ")"
'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If
DoCmd.OpenReport "rptSiteSurvey", intView, , strCriteria, intWindowMode

--
Duane Hookom
MS Access MVP

< If I understand correctly, you can use the selected sites to build a
where
< clause like:
< "[Site] In ('sitea','siteb','sitec','siter')"
< Use this as the where clause to open a report.


Duane

I'm not sure what you mean, but I enclose further info regarding the query
and form as it stands.

Please elaborate further. I know a little Access and a little VBA. If I
can't work this out, then I will definitely learn from it.

My query is as follows:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;

This basically selects everything, unless I add a Like[] statement to the
Site field.

My form preview/print code is as follows:

Private Sub btnPrintReport_Click()
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If


DoCmd.OpenReport "rptSiteSurvey", intView, , "[Site] Like '" &
strCriteria & "*'", intWindowMode


Next vItm
End Sub


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message

[SNIP]
--
Duane Hookom
MS Access MVP

Regarding my post, titled, "I need a form to select the items to be
presented in the report" at M.P.A.Formscoding, where SteveS very kindly
helped me with the Form:

I have a Building Inspection Database which contains survey data. Using a
form, a query and a report - the form will output multiple
condition-survey
reports for each site selected from the listbox of sites. How do I
combine
these reports into a single report?

Regards
D Dawson

 

[dd]

Duane,

Excellent and compliments for your quick, accurate response.

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
You can use code something like:

strCriteria = "[Site] In ("
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = strCriteria & " '" &
Trim(Me!mslbxSites.ItemData(vItm)) & "', "
Next vItm
strCriteria = Left(strCriteria, Len(strCriteria)-2) & ")"
'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If
DoCmd.OpenReport "rptSiteSurvey", intView, , strCriteria, intWindowMode

--
Duane Hookom
MS Access MVP

< If I understand correctly, you can use the selected sites to build a
where
< clause like:
< "[Site] In ('sitea','siteb','sitec','siter')"
< Use this as the where clause to open a report.


Duane

I'm not sure what you mean, but I enclose further info regarding the query
and form as it stands.

Please elaborate further. I know a little Access and a little VBA. If I
can't work this out, then I will definitely learn from it.

My query is as follows:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element;

This basically selects everything, unless I add a Like[] statement to the
Site field.

My form preview/print code is as follows:

Private Sub btnPrintReport_Click()
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If


DoCmd.OpenReport "rptSiteSurvey", intView, , "[Site] Like '" &
strCriteria & "*'", intWindowMode


Next vItm
End Sub


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message

[SNIP]
--
Duane Hookom
MS Access MVP

Regarding my post, titled, "I need a form to select the items to be
presented in the report" at M.P.A.Formscoding, where SteveS very kindly
helped me with the Form:

I have a Building Inspection Database which contains survey data. Using a
form, a query and a report - the form will output multiple
condition-survey
reports for each site selected from the listbox of sites. How do I
combine
these reports into a single report?

Regards
D Dawson

 

[Duane Hookom]

Thanks, this was "air code" so I was hoping it would either work as written
or you would understand how to correct my errors.

--
Duane Hookom
MS Access MVP

Duane,

Excellent and compliments for your quick, accurate response.

"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
You can use code something like:

strCriteria = "[Site] In ("
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = strCriteria & " '" &
Trim(Me!mslbxSites.ItemData(vItm)) & "', "
Next vItm
strCriteria = Left(strCriteria, Len(strCriteria)-2) & ")"
'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If
DoCmd.OpenReport "rptSiteSurvey", intView, , strCriteria, intWindowMode

--
Duane Hookom
MS Access MVP

< If I understand correctly, you can use the selected sites to build a
where
< clause like:
< "[Site] In ('sitea','siteb','sitec','siter')"
< Use this as the where clause to open a report.


Duane

I'm not sure what you mean, but I enclose further info regarding the
query
and form as it stands.

Please elaborate further. I know a little Access and a little VBA. If I
can't work this out, then I will definitely learn from it.

My query is as follows:

SELECT SurveyData.[Date of Survey], SurveyData.Site, SurveyData.Element,
SurveyData.Condition, SurveyData.PriorityRef, SurveyData.[Revenue Cost],
SurveyData.[Revenue Comments], SurveyData.[Remaining Life],
SurveyData.[Capital Cost], SurveyData.[Capital Comments]
FROM SurveyData
ORDER BY SurveyData.[Date of Survey], SurveyData.Site,
SurveyData.Element;

This basically selects everything, unless I add a Like[] statement to the
Site field.

My form preview/print code is as follows:

Private Sub btnPrintReport_Click()
Dim vItm As Variant
Dim strCriteria As String
Dim intView As Integer
Dim intWindowMode As Integer

strCriteria = ""
For Each vItm In Me!mslbxSites.ItemsSelected
strCriteria = Trim(Me!mslbxSites.ItemData(vItm))

'For CheckBox named ckPreview, where Default=True
If ckPreview Then
intView = acPreview
intWindowMode = acDialog
Else
intView = acViewNormal
intWindowMode = acWindowNormal
End If


DoCmd.OpenReport "rptSiteSurvey", intView, , "[Site] Like '" &
strCriteria & "*'", intWindowMode


Next vItm
End Sub


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message

[SNIP]
--
Duane Hookom
MS Access MVP

Regarding my post, titled, "I need a form to select the items to be
presented in the report" at M.P.A.Formscoding, where SteveS very kindly
helped me with the Form:

I have a Building Inspection Database which contains survey data. Using
a
form, a query and a report - the form will output multiple
condition-survey
reports for each site selected from the listbox of sites. How do I
combine
these reports into a single report?

Regards
D Dawson