Combo Box Problem

[Stevie]

I have a combo box which I was displaying Department names in, using the ItemData to store the Department Id. This worked lovely when the Department Id was numeric. I have now had to change the Department Id to be alpha numeric, which has stopped my combo box working correctly.
Is there any way of storing both in the combo box?

[andreww]

We use alpha numerics in similar way, not a problem found.
I can only suggest deleting the combo and recreating it.
Sorry cant help more

[Stevie]

It still does not work for me.

ItemData is an array of long integers so how can it hold an alpha numeric value?

[Stevie]

It still does not work for me.

ItemData is an array of long integers so how can it hold an alpha numeric value?

[MartinLiss]

I think andreww misread your question, because you are right - you can't use the ItemData property to store alphanumerics. One solution is to store the Dept ID's in a collection.

Option Explicit
Public m_colDept As New Collection

Private Sub Combo1_Click()
    MsgBox m_colDept.Item(Combo1.ListIndex + 1) 'Collections are 1-based
End Sub

Private Sub Form_Load()

    Combo1.AddItem "Department 1"
    m_colDept.Add "Dept ID for Department 1"
    Combo1.AddItem "Department 2"
    m_colDept.Add "Dept ID for Department 2"
    
End Sub

[Stevie]

I've done that and I keep getting the error :

3420 - Object invalid or no longer set

whenever I try to access the collection.

Also, (I may be totally wrong about this) won't this work incorrectly if my combo box is sorted in alphabetical order of the department.

[browner]

Hi Stevie - when you add an item to a listbox/combo it is given a unique index. Why not have an array that is the same size as the combo box, and when you add a department to the combo box add it's id to the array in the same index position.

e.g.

combo1.AddItem "DepartmentName" 'add to combo
array1(combo1.NewIndex) = "DepartmentID" 'add id to array

'then when a user clicks on the combo box do this:

myString = array1(combo1.ListIndex)

this will still work if the combo is sorted or changed and you can use the Redim statement to set the size of the array to the amount of departments in the database. I hope this helps you.

[Stevie]

I've already solved the problem by having another combo box to store the key. Whenever I add a department to the first combo, I add the key to the second combo using the NewIndex property so that they are always in sync even when sorted. The second combo is invisible so the user is none the wiser.

Thanks for the ideas.

[Stevie]

I've already got round the problem by creating a second combo box to store the department key.

Whenever a department is added to the first combo, the key is added to the second combo using the NewIndex property to ensure both combos are in sync even if sorted.
The second combo is invisible so that the user is none the wiser.

Thanks for the ideas anyway.