variable as data type mismatch

[Guest]

First off, forgive the messy code, being a newbie, I know this is not tight.
My problem lies in the line:
"For Each varItem In strItemsSelectedList"
I get a data type mismatch error. I think the problem has something to do
with how access handles objects as opposed to variants. If I insert the non
variable code to call selected items from the list boxes, the sequence works.
The purpose of this code is to select choices from multiple list boxes which
will then be placed into a text box.

Public Sub ConcatenateProcedures()
Dim strformname As Object
Dim strproclistcode As Variant
Dim strproclistproc As Variant
Dim strItemsSelectedList As Variant
Dim strItemsSelCount As Variant
Dim varItem As Variant

strformname = "[form_frm" & strproclist
strproclistcode = "str" & strproclist & "code"
strproclistproc = "str" & strproclist & "proc"
strproclistcode = ""
strproclistproc = ""
varItem = ""
strItemsSelectedList = ""
strItemsSelCount = strformname & "!List0.ItemsSelected.Count"
strItemsSelectedList = strformname & "!List0.ItemsSelected"

' If no items selected, then nothing to do
If strItemsSelCount = 0 Then
Exit Sub
End If

' Loop through the items selected collection
For Each varItem In strItemsSelectedList
' Grab the procedure code and text columns for each selected item
strproclistcode = strproclistcode & strformname & "!List0.Column(1,
varItem)" & Chr$(44)
strproclistproc = strproclistproc & strformname & "!List0.Column(2,
varItem)" & Chr$(44)
Next varItem
' Throw away the extra comma on the "IN" string
strproclistcode = Left$(strproclistcode, Len(strproclistcode) - 1)
strproclistproc = Left$(strproclistproc, Len(strproclistproc) - 1)

Tx
Rocky

 

[John Nurick]

Hi Rocky,

Here's one place you're going wrong:

strItemsSelectedList = strformname & "!List0.ItemsSelected"

strItemsSelectedList is a string variable, and this line gives it a value
like
[form_frmXXXX!List0.ItemsSelected

where XXXX is the value of the global or module-level variable strproclist.

In this line
For Each varItem In strItemsSelectedList
you pass that string where VBA expects a collection, array or dictionary.
Hence the failure.

If you want to iterate through the SelectedItems of a listbox and
concatenate all the values in a column, just use something like this air
code:

Dim FormName as String, ListBoxName As String
Dim S As String
Dim Item As Variant
Dim ColumnNo as Long
Dim lbX As ListBox

FormName = "frmMyForm"
ListBoxName = "lbMyListbox"
ColumnNo = 1 'second column

Set lbX = Forms(FormName).Controls(ListBoxName)
S = vbNullStr
With lbX
For Each Item In .ItemsSelected
S = S & ", " & .Column(ColumnNo, Item)
Next
End With
Set lbX = Nothing
S = Mid(S, 3) 'Trim superfluous ", "