Solved

Drop Down List Solution

Posted on 2001-08-14
16
151 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Windows 10 start screen issues 9 54
MsgBox 2 54
Spell Check in VB6 13 112
Automatic Email Reminder 4 36
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

808 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