need help with multi-selct / option group

  • Thread starter Slez via AccessMonster.com
  • Start date
S

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
 
R

Rick Brandt

Slez said:
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.
 
S

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


Rick said:
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.
 
R

Rick Brandt

Slez said:
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?

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.
 
S

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

Rick said:
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.
 
R

Rick Brandt

Slez said:
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" ?

I would expect the code in trwo parts. The first part looks at the ListBox
selection and builds an appropriate string to use either as SQL or a filter.
This could be placed into a single variable.

The second part would use the variable initialized in the first part and only
needs to decide which report to open using that variable.

Now...if the first part of the code that is setting the string varaible can only
do its job if it ALSO knows which report will be run then it will have to take
the OptionGroup selection into account as well. In that case a single code
block that has nested Case statements might be the better way to go. In that
case you will have a lot of code redundancy. That might be mitigated by using
separate functions or sub-routines that are called and the use of variables with
module level scope instead of routine level scope.
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?

Base on an optimistic level of what I think your are trying to do it looked
okay.
 
S

Slez via AccessMonster.com

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

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

Top