Populating a combo box where ID is varchar 2

[traveller4]

I am hoping to get somwe help on this one.

I am trying to populate a combo Box with two field values.

MinistryID
MinistryName

Ministry name is showing but the ministry ID is passed on as a parameter to a stored procedure in a SQL server 2000 database

The problem I have come across is that the ministry ID is varchar with alpha numeric characters
(example: 3CI4CCDA43L120)

Here is my code sample:

'Set a reference to the ADO recordset object
Set g_objRS = New ADODB.Recordset

'Open the recordset object
g_objRS.Open "USP_select_Ministries_SLA_Tacking", g_objConn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

'Loop through the recordset and load the Ministry combo box
Do While Not g_objRS.EOF

'Add the Ministry Name
cboMinistryID.AddItem g_objRS!MinistryName


'Add the Ministry ID
cboMinistryID.ItemData(cboMinistryID.NewIndex) = g_objRS!MinistryID

'Move ot the next record
g_objRS.MoveNext

Loop

'Close and dereference the recordest object
g_objRS.Close
Set g_objRS = Nothing

This method has worked in the past as all my ID numbers are integers.

Is there a special way to handle varchar data in combo boxes indexes. Any help would be greatly appreciated

--Thanks in advance

 

[ZOR]

I will give someone a star if they find a way to do that. I recently had the same problem, the itemdata only accepting numerics. I had to use another combo which mirrored the other one, and was hidden. There putting my data into the hidden combo. A bit messey, but the only way I could do it. Will watch this space with interest. Regards.

 

[strongm]

Indirect it through a collection or dictionary...
Here's an illustrative example:

Option Explicit

Private demo As Collection

Private Sub Combo1_Click()
    MsgBox demo(CStr(Combo1.ItemData(Combo1.ListIndex)))
End Sub

Private Sub Form_Load()
    Set demo = New Collection
    Combo1.Clear
    

    Combo1.AddItem "Hello"
    Combo1.ItemData(Combo1.ListCount - 1) = 0
    demo.Add "linked to hello", "0"
    
    Combo1.AddItem "there"
    Combo1.ItemData(Combo1.ListCount - 1) = 1
    demo.Add "linked to there", "1"
    
    Combo1.AddItem "everyone"
    Combo1.ItemData(Combo1.ListCount - 1) = 2
    demo.Add "linked to everyone", "2"
End Sub

 

[traveller4]

strongm

Thank you for your response.

Forgive me for my ignorance but I am fairly recent to Visual Basic.

The collection looks like it would work but I need to loop through the recordsets to populate the data as the MinistryID and Ministry Name are dynamic and changing all the time. Is this possible with the Collection properties and methods to loop through a recordset and populate the combo box

--Thanks again

 

[mmilan]

Yeah - I think what strongm is suggesting is that you populate the collection / dictionary at the same time as you deal with the populating the combo box (ie the same loop cycle).

That could cause you grief though, as there is not necessary a one to one relationship between the id field and the ministry name (several ministries could have the same name, but with different ids).

I would suggest that you create an array for the ids, and populate that - keeping it in the same order as the combo box (so sorting = false, and sort in an Order By clause instead). That would allow you to get at the correct id value by inspecting the list index of your combo - though I am making the assumption that you are using a DropDown combo (style 2)

mmilan

 

[traveller4]

strongm and mmilan

Thanks for your assistance on this one. Due to time pressures I opted for an easy way out and created the second combo box to pass the parameter.

I will try to go with your suggestions in the future when I have more time to apply to this problem

Thanks again

 

[traveller4]

mmilan

I have had a few hours today to figure this out and used your idea about the array. It works great.

Thanks again for the hint and the idea certainly deseves another star



--Micheal