Drop Down List Solution

Hello Experts:

I have a drop down combo box that holds the fifty states, and it works fine.  When I navigate through my recordset, it displays the correct state that was selected for that record.

However, every time I navigate through the recordset, im reloading the combo box and setting the listindex equal to the current record (state.itemdata(state.listindex). This seems kind of cumbersome that you have to do it this way, but this was a suggestion from another expert.

Is there any other solution?  Maybe a DBCombo?  What is your suggestion.

Im loading the listbox with a recordset using oledb and a do while loop. (VB6 + SQL7.0)
LVL 4
escheiderAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
escheider, you should be able to set the text property of the combo automatically to select the appropriate item:

CurrentState = .getfield("State")   'State Combo Box
State.Text = CurrentState
0
 
escheiderAuthor Commented:
It would be nice if you only had to load the combo box once and that it would display the correct value for the underlying value.
0
 
wsh2Commented:
Yes.. you are correct.. load the State codes into the Combo once.. and then set the state listindex according to the record you have just read.

If you need help.. post your code here.. <smile>
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
escheiderAuthor Commented:
I've written an ActiveX dll to communicate to a sql database, so, some of the code may not make sense. But, here is the requested code.

Here is my code to load the State Combo.  This happens one time when the form is loaded:

.OrderBy = "stateid"
.retrievefields ("tbl_states"), "Status='A'"

Do While Not .EOF
    statename = Trim(.getfield("statename"))
    StateID = .getfield("stateid")
    State.AddItem statename
    State.ItemData(State.NewIndex) = StateID
    .MoveNext
Loop


And Here is the code that changes what is displayed based on what is in the underlying table.  This happens each time the user navigates to a different record:

CurrentState = .getfield("State")   'State Combo Box
For counter = 0 To (State.ListCount - 1)
    If (State.ItemData(counter) = CurrentState) Then
        State.ListIndex = counter
    End If
Next

Is this the correct way to do it or does this seem a bit cumbersome.  Especially if I were to have several combo boxes on the same form.
0
 
escheiderAuthor Commented:
Tim:

would this set the itemdata value to the corresponding text value?

i.e.

StateID  StateName
1        Alaska
2        Alabama
3        Kentucky
4        Indiana

etc..etc..

If I set the State.Text="Alaska" would that set the State.itemdata = 1?
0
 
TimCotteeHead of Software ServicesCommented:
ABsolutely, all you are doing is forcing it to select the item in the list and therefore the .ListIndex property is set accordingly. Now the only problem with this is that if you attempt to set the State.Text property to a value which does not exist in the list an error is generated "Property is read-only" or something like that. All you need to do is trap this and set the State.ListIndex = -1 which will have the effect of showing the combo with a blank entry.
0
 
escheiderAuthor Commented:
Sound interesting, I'll give her a try.

Here is the catch.  Im actually storing the index value of the state in the table, so the statement CurrentState=.getfield("State") would obtain a number in this field, not text, which means I'd either have to do a lookup on that number or change the way I store my information.

Eric
0
 
TimCotteeHead of Software ServicesCommented:
Even easier, just set the .ListIndex property to the value of the State field and it is done!
0
 
escheiderAuthor Commented:
How?

State.listindex=?

0
 
TimCotteeHead of Software ServicesCommented:
I am assuming that the ItemData property is equivalent to the ListIndex for each row in the combo box. If so then

State.ListIndex = .GetField("State")

However this may be a misapprehension on my part, does the StateID field in the table contain anything special?
0
 
escheiderAuthor Commented:
The items are sorted in the combo box so, a value of 2 may be the 10th item in the combo box.
0
 
escheiderAuthor Commented:
Stateid field is a numeric value
0
 
Valliappan ANSenior Tech ConsultantCommented:
May be you could give a try to recordset's absoluteposition property to set the listindex of the combo.
0
 
amebaCommented:
>Is this the correct way to do it or does this seem a bit cumbersome
What is cumbersome is your naming of controls.  Use some prefix for your control, "cbState" is easier to understand than "State".

For small lists, it is OK to use loop ("For i = 0 To Count-1") to find item.
Maybe you can use this function:

Option Explicit

Private Sub Form_Load()
    Dim i As Integer
    For i = 12 To 1 Step -1
        Combo1.AddItem MonthName(i)
        Combo1.Itemdata(Combo1.NewIndex) = i
    Next
    Caption = "click form to select item with itemdata=3"
    Combo1.Text = ""
End Sub

Private Sub Form_Click()
    Combo1.ListIndex = FindComboIndex(Combo1, 30)
End Sub

' finds ItemData in ComboBox, if not found, returns -1
Private Function FindComboIndex(Combo As ComboBox, ByVal DataToFind As Long) As Integer
    Dim i As Integer
   
    FindComboIndex = -1
    With Combo
        For i = 0 To .ListCount - 1
            If .Itemdata(i) = DataToFind Then
                FindComboIndex = i
                Exit For
            End If
        Next
    End With
End Function

-----
Maybe you can use ImageCombo (Microsoft Windows Common controls):

' add ImageCombo
Option Explicit

Private Sub Form_Click()
    ' select item by key
    ImageCombo1.ComboItems.Item("k" & 3).Selected = True
End Sub

Private Sub Form_Load()
    ImageCombo1.Text = ""
    Dim i As Long
    For i = 12 To 1 Step -1
        ImageCombo1.ComboItems.Add , "k" & i, MonthName(i)
    Next
    Caption = "click to select item with key=3"
End Sub
0
 
GoodJunCommented:
There are several options you can take:
1. Your code is ok, if you want minimum change.
2. when you navigate through the recordset (say rs1),
   have the statename filed available in the recordset,
you can achieve this simply modify your query to similar as this: select table1.*, table2.statename where table1.stateid=table2.state
   then you can simply use
      State.Text = .getfield("Statename")
   in your navigate code.
3. If you have a business layer, it will be more approper to put a the convert in this layer (Keyvalue<-->TextValue),I can give you code on this if you are interested in this approach.

My suggestion will be the second method for your case.

Cheers,
GoodJun
0
 
escheiderAuthor Commented:
Thanks Tim for your suggestion.  Works like a champ after I did some minor restructuring.


E
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.