DataCombo Control

How to use two Dataombo controls to display a pair record fields received from one table? For example: SELECT 'ProductName', 'ProductID' from 'Products' Table, and let DataCombo1 display 'ProductName' and DataCombo2 display the 'ProductID'. When the user makes a selection on the DataCombo1 or DataCombo2, the other DataCombo should enable to reflect this selection.
leeannAsked:
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.

Éric MoreauSenior .Net ConsultantCommented:
Pretty much like what we did yesterday.

Place 1 adodc and 2 datacombo on a form and paste the following code:

Option Explicit

Private Sub DataCombo1_Change()
    DataCombo2.Text = DataCombo1.BoundText
End Sub

Private Sub Form_Load()
Dim ConnectionString As String

   ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
                      "Dbq=d:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
                      "Uid=;Pwd="

    'Source of DataCombo1
    With Adodc1
        .ConnectionString = ConnectionString
        .RecordSource = "SELECT ProductName, ProductID " & _
                        "FROM Products "
        .Refresh
    End With

    With DataCombo1
       Set .RowSource = Adodc1
      .BoundColumn = "ProductID"
      .ListField = "ProductName"
      .Refresh
    End With
End Sub

Why do you use a datacombo to display the value of ProductID. A textbox should be enough since for one product name, there is only one ProductID!
0
leeannAuthor Commented:
Hi emoreau,

I'm glad to you are here help me again. Good question, why don't I try to use a TextBox instead of a DataCombo to display the value of ProductID? The reason is that I also want to enable the user to select ProductID from the second DataCombo.
0
leeannAuthor Commented:
Emoreau:
One more problem in your code: When the form load, the value of the both the ProductName and ProductID should display in the two DataCombos, rather than "DataCombo1" and "DataCombo2".
0
Éric MoreauSenior .Net ConsultantCommented:
Ok. Let's try something else.

Place 2 adodc and 2 combobox and copy this code. Now, combos are filled on load and you can play with both combos (and the other is updated).

Private Sub DataCombo1_Change()
    DataCombo2.Text = DataCombo1.BoundText
End Sub

Private Sub DataCombo2_Change()
    DataCombo1.Text = DataCombo2.BoundText
End Sub

Private Sub Form_Load()
Dim ConnectionString As String

   ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
                      "Dbq=d:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
                      "Uid=;Pwd="

    'Source of DataCombo1
    With Adodc1
        .ConnectionString = ConnectionString
        .RecordSource = "SELECT ProductName, ProductID " & _
                        "FROM Products "
        .Refresh
    End With

    With Adodc2
        .ConnectionString = ConnectionString
        .RecordSource = "SELECT ProductName, ProductID " & _
                        "FROM Products "
        .Refresh
    End With

    With DataCombo1
       Set .RowSource = Adodc1
      .BoundColumn = "ProductID"
      .ListField = "ProductName"
      .Refresh
    End With

    With DataCombo2
       Set .RowSource = Adodc2
      .BoundColumn = "ProductName"
      .ListField = "ProductID"
      .Refresh
    End With
   
    Adodc1.Recordset.MoveFirst
    DataCombo1.Text = Adodc1.Recordset.Fields(0).Value
End Sub
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
leeannAuthor Commented:
Great Job!
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.