How to use combo box to create a value, but system store another value?

I would like to use combo box to display some values to my user, but when the user select the value, I would like the system to capture a value that really should be stored in the database.

E.g.
Combo box display choice:
Strawberry
Apple
Orange

If user select "Strawberry", application will capture "st", and store it in the database.
If user select "Apple", application will capture "ap" and store it in the database
if User select "Orange", application will capture "or" and store it in the database.

Can someone tell me how to do this in VB? Thanks!
LVL 1
sataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WackoMikeCommented:
Private Sub Combo1_Click()
MsgBox Left(Combo1.Text, 2)
End Sub


that's will just get the first 2 values like you wanted.


I have no idea what database you plan on using or if you even need help with that.
0
sataAuthor Commented:
hmm... this is not what I'm looking for. what I meant actually is

E.g.
Combo box display choice:
Strawberry
Apple
Orange

The value that I want to store can be : if user choose "Strawberry", the system will store "s1",
if user choose "Apple", the system will store "s2".

at present, "S1", "S2" are the key for the value "Strawberry" and "Apple" respectively. I do not want to access the database to match "Apple" to "S2" whenever the user make a choice, coz' I find that too "expensive", in term of resource.

0
WackoMikeCommented:
hmm...Do you have like a submit button so once they pick everything it can goto the database?

I take it S1 S2 are values in the database where Strawberry and Apple would be stored?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MarkRRCommented:
How about creating an array of fruit from the original table.

Small example for you.  Create new project copy code and place Combobox on form.

Option Explicit
Dim aFruit(4, 2) As Variant

Private Sub Form_Load()
Dim i As Long

'-- Setup fruit
   aFruit(0, 1) = "Apple": aFruit(0, 2) = "A1"
   aFruit(1, 1) = "Pair": aFruit(1, 2) = "P1"
   aFruit(2, 1) = "Orange": aFruit(2, 2) = "O1"
   aFruit(3, 1) = "Bannana": aFruit(3, 2) = "B1"
   aFruit(4, 1) = "Peach": aFruit(4, 2) = "P2"
   
   For i = 0 To UBound(aFruit, 1)
        Combo1.AddItem aFruit(i, 1)
   Next i
End Sub

Private Sub Combo1_Click()
    MsgBox Combo1.Text + " Picked.  Ref: " + aFruit(Combo1.ListIndex, 2)
End Sub
0
SteinerCommented:
You say that you want to put the values then into a database. Maybe the correlation between the displayed text and the value you'd like to get is stored in the DB, too? Then you might use a DataCombo.

RowSource - the table where to get the data to fill the combobox
ListField - the column for the values to be displayed (strawberry)
BoundColumn - the column for the corresponding values if the user selects an entry (st)

DataSource - the table where you want the value (st) to be stored
DataField - the column where you want the value (st) to be stored

Daniel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sataAuthor Commented:
Hi Daniel,

this is quite close to what I need. Can you tell me where can I get this "DataCombo"? I tried looking into the components that I have, I can't seems to find it.

By the way, using this combo, can I specify the RowSource to be from a non-table? Coz' at times, the program maybe offline, so I might need to specify a non-db storage to retrieve the values. I would like to also control when will the data be stored in my tables, which means Datasource and Datafield may need to be blank.
0
kishore3576Commented:
This is the way I go about for such a problem

dim strFruit as string

On the form load event I write

cmbFruit.addItem "Strawberry", 0
cmbFruit.addItem "Apple", 1
cmbFruit.addItem "Orange", 2


In the Lost focus event of the cmbFruit I write

select case ucase(cmbFruit.text)
        case "STRAWBERRY"
               strFruit = "S1"
        case "APPLE"
               strFruit = "S2"
        case "ORANGE"
               strFruit = "S3"
end select

IN the save button click I write

sql = "update <tablename> set <fieldname> = '" & strFruit & "'
con.execute sql
0
SteinerCommented:
1. To find the datacombo press Ctrl + T to bring up the components dialog and look for 'Microsoft DataList Controls', this one contains datalist and datacombo.

2. Any recordset should work, it does not matter whether the recordset is connected to a DB or not. Just use the recordset as a rowsource.

3. You can leave out datasource and datafield. To access the selected value later on, refer to the property .BoundText which will give you the corresponding value found in the BoundColumn.
0
S1mon1969Commented:
As an alternative you could use two arrays, one for the visible data in the combo box and one to store the required corresponding values. It would then be a simple case of setting the list index of the combo as the index for the corresponding array..

Option Explicit

Dim astrShown(4) As String
Dim astrHidden(4) As String


Private Sub Combo1_Click()
Debug.Print Combo1.ListIndex
Label1.Caption = astrHidden(Combo1.ListIndex)
End Sub

Private Sub Form_Load()
Dim intcount As Integer

astrShown(0) = "Strawberry"
astrShown(1) = "Banana"
astrShown(2) = "Cherry"
astrShown(3) = "Apple"
astrShown(4) = "Mango"

astrHidden(0) = "st"
astrHidden(1) = "ba"
astrHidden(2) = "ch"
astrHidden(3) = "ap"
astrHidden(4) = "ma"

For intcount = 0 To 4
    Combo1.AddItem astrShown(intcount), intcount
Next intcount

End Sub

Using the principal shown above, you can incorporate a method to populate the arrays from a table in a database (looping through the table data and using the counter as the list index..)

OR..


You could cheat and have two combo boxes, with one hidden. Use the list index from the visible box to get the corresponding value in the hidden box... BUT this can be confusing for anyone reading / modifying your code.. including you :)
0
S1mon1969Commented:
Forgot to mention.. create a new VB project with a form and a combo box (Combo1) and a label (Label1) then paste the above code in...
0
sataAuthor Commented:
Hi,

I tried the below:
Dim rsBizType As New ADODB.Recordset
Dim sSQL As String

sSQL = "Select * from tBusinessType where bIsInactiveBT = 0"
Set rsBizType = gfnSQLReturnRS(sSQL, Co_con)

If Not rsBizType.EOF Then
   
    Set Datacombo1.RowSource = rsBizType
    Datacombo1.BoundColumn = rsBizType("sBusinessTypeBT")
    Datacombo1.ListField = rsBizType("sBusinessTypeDescBT")
   
       
End If

But I keep getting "Object variable or With block variable not set" at the line "Set Datacombo1.RowSource = rsBizType".

I can't seems to find the problem. Can help me with it?
0
sataAuthor Commented:
ok, I settle it. I've got the wrong object... ;p I selected "DBCombo" instead of "Datacombo"
0
SteinerCommented:
Did your function gfnSQLReturnRS give you a valid recordset? Maybe you should check this using the RecordCount to see whether the recordset is correct.

For BoundColumn and ListField you just need the column names as string, no need to reference the recordset again.

I modified your code to work with the nwind.mdb, that's the way it worked:
     Dim adoCn As ADODB.Connection
    Set adoCn = New ADODB.Connection
    With adoCn
        .Provider = "Microsoft.Jet.OLEDB.3.51"
        .CursorLocation = adUseClient
        .Open "C:\Programme\Microsoft Visual Studio\VB98\nwind.mdb"                            'Öffnen der Datenbank
        If .State <> adStateOpen Then
            MsgBox "Öffnen gescheitert", vbCritical, "Fehler"
            End
        End If
    End With
   
   Dim rsBizType As New ADODB.Recordset
   Dim sSQL As String
   
   sSQL = "select * from artikel"
   Set rsBizType = adoCn.Execute(sSQL)
   
   If Not rsBizType.EOF Then
      Set DataCombo1.RowSource = rsBizType
      DataCombo1.BoundColumn = "Artikel-Nr"
      DataCombo1.ListField = "Artikelname"
   End If
0
SteinerCommented:
Ok, you got it, was still writing when you posted you comment, so never mind.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

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.