Code for referencing multi select list box

[Slez via AccessMonster.com]

I have a multi select list box which allows me to view and print reports
based on selecting one or more desired project bids and then select a
specific report to run based on an option button selection. Works great...
but...now I'd like to be able to open a FORM via command button
"cmdCopyBidInfo". What do I alter in the following code for the new command
button to function based on what is selected in the multi select list box?
I've tried a few things, but can't get it to work. Any help is appreciated!!!
Here is that code:

Private Sub cmdViewBidInfo_Click()
Dim ctlSource As Control
Dim strItems As String
Dim intProjectID As Integer
Dim intCurrentRow As Integer, SelectedRows As Integer
Set ctlSource = Me.lstBidSelectBOM
intProjectID = Forms!frmMainScreen!cboProjectMatl
'Debug.Print intProjectID


For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
If SelectedRows >= 1 Then
strItems = strItems & " or BidNumber = "
End If
strItems = strItems & ctlSource.Column(0, intCurrentRow)
SelectedRows = SelectedRows + 1
End If
Next intCurrentRow
strItems = strItems
If Len(strItems) = 0 Then
MsgBox "You did not select anything from the list. " _
, vbExclamation, "Nothing selected!"
Exit Sub
End If

strCriteria = "BidNumber = "
strCriteria = strCriteria & strItems
'Debug.Print strCriteria
strCriteria = "(" & strCriteria & ")" & " and ProjectID = " &
intProjectID
'Debug.Print strCriteria

Select Case Frame36
Case 1
DoCmd.OpenReport "rptBOMBidItem", acViewPreview, , strCriteria
Case 2
DoCmd.OpenReport "rptBOMBidMatl", acViewPreview, , strCriteria
End Select

End Sub

 

[Douglas J. Steele]

You've commented out the line that prints the content of strCriteria to the
immediate window. If you activate that line, what gets printed? What value
is returned by Frame36?

Incidentally, the following would be better:

Private Sub cmdViewBidInfo_Click()
Dim ctlSource As Control
Dim strItems As String
Dim intProjectID As Integer
Dim intCurrentRow As Integer, SelectedRows As Integer
Dim varSelected As Variant

Set ctlSource = Me.lstBidSelectBOM
intProjectID = Forms!frmMainScreen!cboProjectMatl
'Debug.Print intProjectID

If ctlSource.ItemsSelected.Count > 0 Then
For Each varSelected In ctlSource.ItemsSelected
strItems = strItems & ctlSource.Column(0, varSelected) & ", "
Next varSelected
strItems = Left(strItems, Len(strItems) - 2)
Else
MsgBox "You did not select anything from the list. " _
, vbExclamation, "Nothing selected!"
Exit Sub
End If

strCriteria = "BidNumber IN (" & strItems & ") "
strCriteria = strCriteria & " AND ProjectID = " & intProjectID
Debug.Print strCriteria

Select Case Frame36
Case 1
DoCmd.OpenReport "rptBOMBidItem", acViewPreview, , strCriteria
Case 2
DoCmd.OpenReport "rptBOMBidMatl", acViewPreview, , strCriteria
End Select

End Sub

 

[Slez via AccessMonster.com]

Actually nothing gets printed. I copied this code from a command button that
prints a report and changed it to only preview. It has worked fine, but I
understand where it could be made better. I'm not that good at code so the
fact that it worked as is was sort of a small victory!

Frame 36 contains the option buttons with 2 reports available, so dependiing
on which Case was selected determines which report is run. Just to clarify,
I am trying to alter this to open a form and not run a report as done
previously.

Thanks again!
Slez

You've commented out the line that prints the content of strCriteria to the
immediate window. If you activate that line, what gets printed? What value
is returned by Frame36?

[quoted text clipped - 48 lines]

End Sub

 

[Douglas J. Steele]

Just to be straight. You've removed the single apostrophe in front of the
line Debug.Print strCriteria and nothing shows up in the Immediate Window?

Are you sure that the code is running? Make sure that the Click property
contains [Event Procedure] and that clicking on the ellipsis (...) to the
right of the property takes you into that code. If both of those are
correct, try putting a breakpoint inside the routine and single-step through
the code to see why you're not getting anything written out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

You've commented out the line that prints the content of strCriteria to
the immediate window. If you activate that line, what gets printed? What
value is returned by Frame36?

Incidentally, the following would be better:

Private Sub cmdViewBidInfo_Click()
Dim ctlSource As Control
Dim strItems As String
Dim intProjectID As Integer
Dim intCurrentRow As Integer, SelectedRows As Integer
Dim varSelected As Variant

Set ctlSource = Me.lstBidSelectBOM
intProjectID = Forms!frmMainScreen!cboProjectMatl
'Debug.Print intProjectID

If ctlSource.ItemsSelected.Count > 0 Then
For Each varSelected In ctlSource.ItemsSelected
strItems = strItems & ctlSource.Column(0, varSelected) & ", "
Next varSelected
strItems = Left(strItems, Len(strItems) - 2)
Else
MsgBox "You did not select anything from the list. " _
, vbExclamation, "Nothing selected!"
Exit Sub
End If

strCriteria = "BidNumber IN (" & strItems & ") "
strCriteria = strCriteria & " AND ProjectID = " & intProjectID
Debug.Print strCriteria

Select Case Frame36
Case 1
DoCmd.OpenReport "rptBOMBidItem", acViewPreview, , strCriteria
Case 2
DoCmd.OpenReport "rptBOMBidMatl", acViewPreview, , strCriteria
End Select

End Sub

 

[Slez via AccessMonster.com]

Hi Doug,
I didn't remove the apostrophe. The code is running. The problem is that
the form returns ALL of the data from ALL of the BidNumbers, as opposed to
compiling the data based on what is selected in the multi select list box:
lstBidSelectBOM. When the reports are run, they only display data associated
with what selections are made in the list box.
Thanks
Slez

Just to be straight. You've removed the single apostrophe in front of the
line Debug.Print strCriteria and nothing shows up in the Immediate Window?

Are you sure that the code is running? Make sure that the Click property
contains [Event Procedure] and that clicking on the ellipsis (...) to the
right of the property takes you into that code. If both of those are
correct, try putting a breakpoint inside the routine and single-step through
the code to see why you're not getting anything written out.

You've commented out the line that prints the content of strCriteria to
the immediate window. If you activate that line, what gets printed? What

[quoted text clipped - 90 lines]

 

[Douglas J. Steele]

I was suggesting that you remove the apostrophe so that you could see what
was in the criteria.

Based on what you're saying, your criteria is probably incorrect.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Doug,
I didn't remove the apostrophe. The code is running. The problem is that
the form returns ALL of the data from ALL of the BidNumbers, as opposed to
compiling the data based on what is selected in the multi select list box:
lstBidSelectBOM. When the reports are run, they only display data
associated
with what selections are made in the list box.
Thanks
Slez

Just to be straight. You've removed the single apostrophe in front of the
line Debug.Print strCriteria and nothing shows up in the Immediate Window?

Are you sure that the code is running? Make sure that the Click property
contains [Event Procedure] and that clicking on the ellipsis (...) to the
right of the property takes you into that code. If both of those are
correct, try putting a breakpoint inside the routine and single-step
through
the code to see why you're not getting anything written out.

You've commented out the line that prints the content of strCriteria to
the immediate window. If you activate that line, what gets printed?
What

[quoted text clipped - 90 lines]

End Sub