Solved

Drop Down List Solution

Posted on 2001-08-14
16
147 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
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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now