need help with multi-selct / option group

[Slez via AccessMonster.com]

I have a multi-select list box on a form which has 4 available reports which
open based on command buttons. The code for each of the command buttons
contains SQL which gets quite lengthy. I'm in the process of adding 2
additional report selections (for a total of 6) and would like to switch from
command buttons to an option group. I have done so successfully in the past
when a single record is selected from a combo box, but never with a multi-
select list box. I'm certainly no expert at VBA, and I'm trying to combine
code from both of the above applications to develop one that works. I'd like
to avoid all of the SQL and just feed off of the queries if that's possible.
So far...no luck.

My form, "frmEstimateSelector", has a multi-select list box called
"lstBidSelect", and an option group called "grpEstimReports", and 2 command
buttons, View and Print, for after selections are made.

I thought I'd try to prey on the vast knowledge offered in this forum. I
have done so often as of late and have really benefited a great deal! I have
posted my current code below with the hope that someone can steer me in the
right direction. Thank you in advance for any help with this matter!

Option Compare Database
Option Explicit ' Requires variables to be declared before they are used.

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.

Dim varItem As Variant
Dim strBidSelected As String

For Each varItem In Me!lstBidSelect.ItemsSelected
strBidSelected = strBidSelected & ", " & Me.lstBidSelect.ItemData(varItem)

Next varItem

If Len(strBidSelected) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "No Selection!"
Exit Sub
End If

Select Case Me!grpEstimReports
Case 1
DoCmd.OpenReport "Proposal1", PrintMode
Case 2
DoCmd.OpenReport "rptEstimateDetail", PrintMode
Case 3
DoCmd.OpenReport "rptEstimateDetailInstall", PrintMode
Case 4
DoCmd.OpenReport "rptPrelimSummary", PrintMode
Case 5
DoCmd.OpenReport "rptMaterialSummary", PrintMode
Case 6
DoCmd.OpenReport "rptMaterialSummaryMisc", PrintMode
End Select

Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub

Private Sub cmdPrintEstimInfo_Click()
PrintReports acNormal
End Sub

Private Sub cmdViewEstimInfo_Click()
PrintReports acPreview
End Sub

 

[Rick Brandt]

I have a multi-select list box on a form which has 4 available
reports which open based on command buttons. The code for each of
the command buttons contains SQL which gets quite lengthy. I'm in
the process of adding 2 additional report selections (for a total of
6) and would like to switch from command buttons to an option group.
I have done so successfully in the past when a single record is
selected from a combo box, but never with a multi- select list box.
I'm certainly no expert at VBA, and I'm trying to combine code from
both of the above applications to develop one that works. I'd like
to avoid all of the SQL and just feed off of the queries if that's
possible. So far...no luck.

My form, "frmEstimateSelector", has a multi-select list box called
"lstBidSelect", and an option group called "grpEstimReports", and 2
command buttons, View and Print, for after selections are made.

I thought I'd try to prey on the vast knowledge offered in this
forum. I have done so often as of late and have really benefited a
great deal! I have posted my current code below with the hope that
someone can steer me in the right direction. Thank you in advance
for any help with this matter!

Option Compare Database
Option Explicit ' Requires variables to be declared before they are
used.

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub
procedures. ' Preview or print report selected in the
ReportToPrint option group.

Dim varItem As Variant
Dim strBidSelected As String

For Each varItem In Me!lstBidSelect.ItemsSelected
strBidSelected = strBidSelected & ", " &
Me.lstBidSelect.ItemData(varItem)

Next varItem

If Len(strBidSelected) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "No Selection!"
Exit Sub
End If

Select Case Me!grpEstimReports
Case 1
DoCmd.OpenReport "Proposal1", PrintMode
Case 2
DoCmd.OpenReport "rptEstimateDetail", PrintMode
Case 3
DoCmd.OpenReport "rptEstimateDetailInstall", PrintMode
Case 4
DoCmd.OpenReport "rptPrelimSummary", PrintMode
Case 5
DoCmd.OpenReport "rptMaterialSummary", PrintMode
Case 6
DoCmd.OpenReport "rptMaterialSummaryMisc", PrintMode
End Select

Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
Resume Exit_Preview_Click
End Sub

Private Sub cmdPrintEstimInfo_Click()
PrintReports acNormal
End Sub

Private Sub cmdViewEstimInfo_Click()
PrintReports acPreview
End Sub

Sorry, but I have no idea what your question is. The closest thing to a
question I get from the above is "...would like to switch from command buttons
to an option group". Is that the question?

There is also "...I'd like to avoid all of the SQL and just feed off of the
queries if that's possible...", but I have no idea what to make of that
statement.

 

[Slez via AccessMonster.com]

Ultimately, the question is: What do I need to add to my code to make the
list box selections recognized and my reports run? Currently, I can select
records in my list box, select a report in my option group, and when I click
View or Print, nothing happens.

The reason I mention the SQL is that in a past application, the code included
"strSQL" in the On Click event to run each report. It's my perception that
changing from an individual command button for each available report to an
Option Group would drastically change this approach. It occurred to me that
I may not require as much SQL within the code. My question with that is: Do
I need that SQL within the code?

Thanks
Slez

I have a multi-select list box on a form which has 4 available
reports which open based on command buttons. The code for each of

[quoted text clipped - 71 lines]

PrintReports acPreview
End Sub

Sorry, but I have no idea what your question is. The closest thing to a
question I get from the above is "...would like to switch from command buttons
to an option group". Is that the question?

There is also "...I'd like to avoid all of the SQL and just feed off of the
queries if that's possible...", but I have no idea what to make of that
statement.

 

[Slez via AccessMonster.com]

So does that mean I need to write an "If..Then" statement such as "If Case 1
is selected, then use this strSQL" Or "If Case 2 is selected, then use that
strSQL" ?

Does the code in my initial post look like I am on the right track? Is it
your perception that I'm just missing alot of code?
Slez

Ultimately, the question is: What do I need to add to my code to
make the list box selections recognized and my reports run?

[quoted text clipped - 8 lines]

within the code. My question with that is: Do I need that SQL
within the code?

So the OptionGroup is deciding "which report?" and the ListBox is deciding
"which records?". Is that correct?

If so yes, you will need the same code to build a SQL string as before to
respond to the selection in the ListBox unless you have saved queries that will
automatically make a reference to the ListBox and do their own filtering.

Then a Case statment to decide which report to preview should handle the other
part.

 

[Slez via AccessMonster.com]

Thanks for all your help! You may see future posts as I get closer to the
end result.
Slez