Load Combo via .ItemData 2

[SteveMac32]

Hi Guys I am loading a combo box

With cboSalesCallReason
.Clear
Do Until rs.EOF
.AddItem Trim(rs!CallReason)
.ItemData(.NewIndex) = rs!URN
rs.MoveNext
Loop
End With

I need to set the combo box depending on the .ItemData (the URN).

So is it possible to select an item in a drop down list via it's .ItemData value as opposed to the .List/.ListItem value..

the only way I can see is by using this loop


Dim i As Integer

For i = 0 To cboSalesCallReason.ListCount - 1
If cboSalesCallReason.ItemData(i) = URN Then
cboSalesCallReason.ListIndex = i
Exit For
End If
Next


Any other ideas ??????

Thanks

Steve

 

[BobRodes]

That way is fine in a small list, as lists often are. One way to speed it up is to use the with keyword, so:

    With cboSalesCallReason
   For i = 0 To .ListCount - 1
       If .ItemData(i) = URN Then
          .ListIndex = i
          Exit For
       End If
   Next
End With

That way, the pointer to cboSalesCallReason only has to be resolved once, rather than once for each call.

Now, I don't know how many call reasons that you have, but in most scenarios there are only a few reasons for making a sales call. If that's the case, this way of doing this is fine. Also, it looks like your "URN" is a long integer rather than a string, so that's fine too.

If you are working with a large list and don't like the idea of sequential access lookup (essentially the technique you're using here), then there's a trick. Create a recordset and add two fields to it. (Look up recordset.fields.append.) In one field, put the ItemData value, in the other the ListIndex value. Put an index on the ItemData field in your recordset (e. g. rs.Fields("myURN").Properties("Optimize") = True; for more info see

http://msdn.microsoft.com/en-us/library/ms677521(VS.85).aspx).

Open the recordset, iterate your ComboBox, populating the recordset with .ListIndex/.ItemData pairs.

Now you can do an indexed lookup in your recordset on your URN, pull the ListIndex value associated with it, and directly set the ComboBox's ListIndex property to the value.

I also use this technique (or use an array if I don't need the indexing), if the ItemData value that I want to keep is a string rather than a long integer.

 

[HughLerwill]

Perhaps something like;

Dim MyCollection As New Collection

Private Sub Command1_Click()

Dim Names As Variant
Dim URN As Variant

Names = Array("Steve", "Bob", "Hugh")
URN = Array(7234, 5678, 2345)

With Combo1
For i = 0 To UBound(Names)
.AddItem Names(0)
.ItemData(.NewIndex) = URN(i)
MyCollection.Add .NewIndex, CStr(URN(i))
Next
End With

MsgBox "URN 2345 is in Listindex " & MyCollection("2345")

 

[BobRodes]

By the way, Hugh, if you put your MyCollection object in the middle of a loop, you'll not want to use the "As New" construct in VB6. "As new" doesn't actually instantiate the class. Rather, every time the class is referenced in code in any way, it looks to see if there's a reference to it and if not it creates one. This is a fair amount of code overhead, and is particularly an issue therefore in a loop.

 

[HughLerwill]

>overhead
May be on the i=0 loop, but thereafter?

 

[strongm]

Yes, every time the object is accessed. Basically VB treats on object declared As New as an auto-instantiation object - which means, as Bob has pointed out, that VB checks if the object is already instantiated every time that you use the variable.

 

[HughLerwill]

Thanks again guys, a good tip Bob. At home I prefer to avoid Dim var as New Obj too, it just seemed the quikest way to set things up when I threw the demo together, no excuse just laziness.

I guess you spotted the typo too, in .AddItem Names(0) which should have been .AddItem Names(i)

SteveMac32, how are you getting on?

 

[SteveMac32]

Hi Guys

I ended up making a procedure out of the original loop and called it when I had a combo that needed to be set.

thanks for all the input.


Steve