multiselect list box problem

[krberube]

Hi all,
I have a form w/ a listbox that allows me to select an "RMA#", then click a button to open another form with the RMA details. I wanted to make this a multiselect listbox, and have it open the form and allow viewing/editing of ONLY the slected records. I got some code from a book called " Access 2003, inside out by John Viescas". Great book so far. I am trying to modify his code but seem to be stuck.
This is what i have so far: StatusList = my listbox

Private Sub lookup_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!StatusList.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me.StatusList.ItemsSelected
'Grab the RMA# column for each selected item
strWhere = strWhere & Me.StatusList.Column(0, varItem)
Next varItem
' Open the RMA form filtered on the selected RMA#'s
strWhere = "[RMA#] IN (" & strWhere & ")"
DoCmd.OpenForm FormName:="InHouseRMADetail", WhereCondition:=strWhere

End Sub

When i run this, I receive "Runtime error 2501, the openform action was cancelled. When i debug it leaves me at the red line above.

Any help would be appreciated.

Kevin

 

[krberube]

thanks for replying Pat,
I am a little confused about this code to begin with, I undeerstand what its trying to do.....just can't get it there. I fooled with the ","'s and quotes but either get a error or no records at all because i get prompted for an input for strWhere.

could you maybe give me a proper syntax for this line??
strWhere = "[RMA#] IN (" & strWhere & ")"

Thanks
Kevin

 

[krberube]

Thank Pat,
I selected 3 "RMA's" from my listobx ( 9016390, 9016391, 9016394 ) and strWhere = "[RMA#] IN ( 901639090163919016394 )"

I think this is where you were talking about the comma's. I again tried different things with the comma's, to no avail.
Here is the code that I have now:

Private Sub lookup_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!StatusList.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me.StatusList.ItemsSelected
'Grab the RMA# column for each selected item
strWhere = strWhere & Me.StatusList.Column(0, varItem)
Next varItem
' Open the RMA form filtered on the selected RMA#'s
strWhere = "[RMA#] IN ( " & strWhere & " )"
DoCmd.OpenForm "Form1", acNormal, , WhereCondition:=strWhere

End Sub

I don't have to have this, I can do 1 at a time. But since I know this possible I would love to get it running.

Thanks
Kevin

 

[EMP]

If MRA# is a numeric field:-

Private Sub Lookup_Click()
   Dim strWhere As String
   Dim varItem As Variant
   
   ' Request to edit items selected in the list box
   ' If no items selected, then nothing to do
   If Me!StatusList.ItemsSelected.Count = 0 Then Exit Sub

   ' Loop through the items selected collection
   For Each varItem In Me.StatusList.ItemsSelected
      'Grab the RMA# column for each selected item
      [b]strWhere = strWhere & "," & Me.StatusList.Column(0, varItem)[/b]
   Next varItem
   
   ' Open the RMA form filtered on the selected RMA#'s
   [b]strWhere = "[RMA#] IN ( " & Mid(strWhere, 2) & " )"[/b]

   DoCmd.OpenForm "Form1", acNormal, , WhereCondition:=strWhere
End Sub

The Mid() function is used to remove the leading comma from the string.

If RMA# is a text field, change the first bold line to:-
strWhere = strWhere & ",'" & Me.StatusList.Column(0, varItem) & "'"

~

 

[krberube]

Thanks EMP, that was it. Its nice to have a pretty good working program, but its even nicer to have something that goes the extra step. Thanks again

Kevin