• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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!
0
sata
Asked:
sata
  • 4
  • 4
  • 2
  • +3
1 Solution
 
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
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.

 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now