?
Solved

Drop Down List Solution

Posted on 2001-08-14
16
Medium Priority
?
162 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 43

Accepted Solution

by:
TimCottee earned 120 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

719 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