Solved

Drop Down List Solution

Posted on 2001-08-14
16
158 Views
Last Modified: 2010-05-02
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)
0
Comment
Question by:escheider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 4

Author Comment

by:escheider
ID: 6384455
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
 
LVL 14

Expert Comment

by:wsh2
ID: 6384527
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
 
LVL 4

Author Comment

by:escheider
ID: 6384566
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 43

Accepted Solution

by:
TimCottee earned 30 total points
ID: 6384586
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
 
LVL 4

Author Comment

by:escheider
ID: 6384656
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 6384687
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
 
LVL 4

Author Comment

by:escheider
ID: 6384806
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 6384819
Even easier, just set the .ListIndex property to the value of the State field and it is done!
0
 
LVL 4

Author Comment

by:escheider
ID: 6384874
How?

State.listindex=?

0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6384885
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
 
LVL 4

Author Comment

by:escheider
ID: 6384890
The items are sorted in the combo box so, a value of 2 may be the 10th item in the combo box.
0
 
LVL 4

Author Comment

by:escheider
ID: 6384931
Stateid field is a numeric value
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6385331
May be you could give a try to recordset's absoluteposition property to set the listindex of the combo.
0
 
LVL 15

Expert Comment

by:ameba
ID: 6385389
>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
 
LVL 10

Expert Comment

by:GoodJun
ID: 6385841
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
 
LVL 4

Author Comment

by:escheider
ID: 6410906
Thanks Tim for your suggestion.  Works like a champ after I did some minor restructuring.


E
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month5 days, 10 hours left to enroll

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question