Multi-Value ComboBox Selections

[csh2013]

I found a snippet of code online that I'm trying to use in an asset tagging database I'm developing, but I'm struggling to get it working. To start, I have a multi-column ComboBox that displays information in this format:

1001 | iPhone5c
1002 | iPhone5s
1003 | iPhone6
1004 | iPad2
1005 | iPad3

When you select an asset to be assigned to an employee, the ComboBox displays only the asset number (e.g., 1001). While that information is pertinent to our I.T. group, when Human Resources goes to collect an asset from an employee, they don't want to be taking cases off of phones or tablets to verify they have the correct asset number. They want to see the person has an iPhone5s and an iPad2 that they have to collect. So, what I'm trying to accomplish in my VBA is to have access read all the asset numbers and provide the descriptions of those items in another field.

The code I have so far is:

Dim ctl As ComboBox
Dim varItm As Variant, str As String

str = ""
Set ctl = Me.Combo217

For Each varItm In ctl.ItemsSelected

str = ctl.Column(2, varItm) & ","

Next varItm

Me.Text207.Value = str

Since this is code is something I found online, I'm not sure why I can't get it to work. I've never worked with the Variant declaration, but I think this may be where the code is breaking because whenever I remove the "For Each...Next", the code correctly assigns the value of column 2 of my very first row to my text box (Text207). Everything I've seen looks as though I don't have to declare varItm because it's function is to represent the rows that are checkmarked for ctl.ItemsSelected.

Can anyone tell me why this isn't working?

 

[smig]

Declaring a var as variant mean it can have any kind of data.
In this case varItem must be a variant.

You cant find a piece of code and hope it will work. You must understand what are you doing.
This code is for multi select combo.

You need:
MySelectedItemID = me.Combo.column(0)
MySelectedItemName = me.Combo.column(1)

Look at the NumberOfColumns and ColumnsWidths properties how to set multi columns and their widths.

 

[csh2013]

I don't think that code will work. Here's what I want it to accomplish:

User John Smith is assigned 2 pieces of equipment with asset tags 1003 and 1004. So, ComboBox217 shows "1003, 1004". I want my text box to show the descriptions of those items. So, Me.Text207.Value should show "iPhone 6, iPad2". The problem I'm having is looping through the ComboBox and iterating the selected rows.

Maybe that's more clear?

Thanks!

 

[smig]

This line should be fixed in original code:
str = str & ctl.Column(1, varItm) & ","


My code was for a single selection combo

 

[csh2013]

I actually tried using column 1 as well and found that it didn't produce any different results. I also tried column 0 as that's the column with the checkbox. Each way produced the same results, which is that it doesn't fill anything in the text box at all. Everything I tested makes it seem as though the issue is the varItm, but because I'm not sure what's technically stored in the variable, I'm not sure how to validate it. From what I've read, using the Variant in the way that I am, varItm will store an integer that corresponds to a selected row. Is that a correct statement? It's not storing the contents of the entire row, is it?

You obviously are much more familiar with the multi-column/multi-value boxes than I am, so thank you for your assistance!

 

[jdraw]

It seems you have Employees who can be assigned 1 or many Assets.
Instead of multicolumn combo, you might use a form/subform arrangement.
The form would have the Employee info, the subform a list of Assets assigned to that Employee.

Just a thought for a work around.


This may help with your original approach

 

[csh2013]

Thanks, jdraw. I did opt to go for the sub-form in the interim. I don't think I want this as an end result because of the limitations. The employee form has a relationship with the asset listing using the employee name, which, with the way I currently have everything built (and maybe I need to revisit the construction now that I have something that works) the sub-form doesn't allow me to choose from our existing asset list to assign the asset to the employee. So, as assets change hands when employees leave and are replaced, I will have to go into the Asset listing separately to remove the user and assign a new one. Because, of course, the Asset listing uses the Asset Tag as the primary key. Again, I may need to rethink the build when I have some more time now that my mindset has shifted. But, thanks for the suggestion! It did at least least give me a temporary resolution so I can move on until I have a few spare minutes.