A DataCombo Problem

A major feature of the MSHFlexGrid control is its ability to display hierarchical recordsets—relational tables displayed in a hierarchical fashion. But my client would like to display hierarchical recordsets—relational tables using DataCombos. I wrote a test code shown belove to try to accomplish this task. The problem is that whateven you make any select in the DataCombo1, the DataCombo2 has no response. Finally, I found that the current cursor position in the recordset can't be changed when make a select in the DataCombo1. If we make a select through DBGrid, it will work correctly. How to make this correctly without the DBGrid?

Option Explicit

Private Sub Form_Load()
   Dim ConnectionString As String
   ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
   "Dbq=C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;" & _
   "Uid=;Pwd="
 
   With Adodc1
      .RecordSource = _
      "SELECT ProductName, ProductID, SupplierID FROM Products"
      .ConnectionString = ConnectionString
      .Refresh
      .Caption = "Products"
      .Visible = False
    End With
   
    With Adodc2
      .RecordSource = "SELECT CompanyName, SupplierID FROM Suppliers"
      .ConnectionString = ConnectionString
      .Refresh
      .Caption = "Suppliers"
      .Visible = False
    End With
   
    Set DataGrid1.DataSource = Adodc1
    With DataCombo1
       Set .DataSource = Adodc1
      .DataField = "SupplierID" ' The field to be changed.
      .BoundColumn = "SupplierID"   ' Field in ADODC2 that change                         ' DataField.      Set .RowSource = Adodc2
       Set .RowSource = Adodc2
      .ListField = "CompanyName" ' Field displayed by DataCombo.
    End With
   ' Synchronize DataGrid and DataCombo by
   ' moving to first record of ADODC1's recordset.
   Adodc1.Recordset.MoveFirst

   With DataCombo2
       Set .DataSource = Adodc1
      .DataField = "ProductID"
      .BoundColumn = "ProductID"  
      .ListField = "ProductName"
    End With


End Sub
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.

leeannAuthor Commented:
Edited text of question.
0
Éric MoreauSenior .Net ConsultantCommented:
Not sure but let me try!

What you want is that when you change your selection in the first datacombo, that your grid reflects your selection?

If this is the case, add an ADODC to your form and change your code like this:

Option Explicit

Private Sub DataCombo1_Change()
    If Len(DataCombo1.BoundText) > 0 Then
        With Adodc3
            .RecordSource = "SELECT ProductName, ProductID, SupplierID " & _
                           "FROM Products " & _
                           "WHERE SupplierID = " & DataCombo1.BoundText
            .Refresh
            Set DataGrid1.DataSource = Adodc3
         End With
    End If
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="
   
   With Adodc1
      .RecordSource = _
      "SELECT ProductName, ProductID, SupplierID FROM Products"
      .ConnectionString = ConnectionString
      .Refresh
      .Caption = "Products"
      .Visible = False
    End With
     
    With Adodc2
      .RecordSource = "SELECT CompanyName, SupplierID FROM Suppliers"
      .ConnectionString = ConnectionString
      .Refresh
      .Caption = "Suppliers"
      .Visible = False
    End With
   
    With Adodc3
        .ConnectionString = ConnectionString
        .Visible = False
    End With
   
    With DataCombo1
       Set .DataSource = Adodc1
      .DataField = "SupplierID" ' The field to be changed.
      .BoundColumn = "SupplierID"   ' Field in ADODC2 that change                         ' DataField.      Set .RowSource = Adodc2
       Set .RowSource = Adodc2
      .ListField = "CompanyName" ' Field displayed by DataCombo.
      .Refresh
    End With

   With DataCombo2
       Set .DataSource = Adodc1
      .DataField = "ProductID"
      .BoundColumn = "ProductID"
      .ListField = "ProductName"
    End With
End Sub


What is the purpose of the second combo box?
0
leeannAuthor Commented:
Thanks,Emoreau.

What I want is that when the user changes selection in the first datacombo, that the second DataCombo reflects the user's selection.
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Éric MoreauSenior .Net ConsultantCommented:
Second try:

Option Explicit

Private Sub DataCombo1_Change()
    If Len(DataCombo1.BoundText) > 0 Then
        'Source of DataGrid1
        With Adodc3
            .RecordSource = "SELECT ProductName, ProductID, SupplierID " & _
                           "FROM Products " & _
                           "WHERE SupplierID = " & DataCombo1.BoundText
            .Refresh
            Set DataGrid1.DataSource = Adodc3
         End With
   
        'Source of DataCombo2
        With Adodc1
            .RecordSource = "SELECT ProductName, ProductID, SupplierID " & _
                            "FROM Products " & _
                            "WHERE SupplierID = " & DataCombo1.BoundText
            .Refresh
            Set DataCombo2.RowSource = Adodc1
            DataCombo2.Text = DataGrid1.Text
        End With
    End If
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 DataCombo2
    With Adodc1
        .ConnectionString = ConnectionString
        .Caption = "Products"
        .Visible = False
    End With
     
    'Source of DataCombo1
    With Adodc2
      .RecordSource = "SELECT CompanyName, SupplierID FROM Suppliers"
      .ConnectionString = ConnectionString
      .Refresh
      .Caption = "Suppliers"
      .Visible = False
    End With
     
    'Source of DataGrid1
    With Adodc3
        .ConnectionString = ConnectionString
        .Visible = False
    End With
     
    With DataCombo1
      .BoundColumn = "SupplierID"   ' Field in ADODC2 that change                         ' DataField.      Set .RowSource = Adodc2
       Set .RowSource = Adodc2
      .ListField = "CompanyName" ' Field displayed by DataCombo.
      .Refresh
    End With

   With DataCombo2
      .DataField = "ProductID"
      .ListField = "ProductName"
    End With
End Sub
0
leeannAuthor Commented:
Have you run your code? I got error message said that "[ADODC): no RecordSource specified. [ADO]: No command has been set for the command object"
0
Éric MoreauSenior .Net ConsultantCommented:
Yes I have runned it.

Did you copy it entirely? Did you set properties like DataSource in the Properties window? On which line do you have the error? Is it during the Form_Load event?

I think that a CRLF is missing! If you check in the Form_Load event, in the "With DataCombo1", the code should be like this (I have inserted double CRLF to be sure that at least one will be accepted and I also remove comments):

With DataCombo1

   .BoundColumn = "SupplierID"

   Set .RowSource = Adodc2

   .ListField = "CompanyName"

   .Refresh

End With
0
leeannAuthor Commented:
I tried your code again, but still failed. Would you please tell me what's wrong? Here was what I did:
(1) Opened a Standard Exe project.
(2) Referenced the following component:
  a. Mircrosoft DataGrid Control 6.0(OLEDB)
  b. Mircrosoft DataList Controls 6.0 (OLEDB)
  c. Mircrosoft ADO Data Control 6.0(OLEDB)

(3)Added Three ADO Data controls into Form1.
Added one Data Grid into Form1.
Added two DataCombos into Form1.

(4) Pasted your code(Second try) into the Declarations section of the form, and then press F5 to run the program.
0
Éric MoreauSenior .Net ConsultantCommented:
Works like a charm! I just restart as you explain it.

The only thing I can see is that I have the SP3 version of each of these controls.

Also, on which line did the error occured?

0
leeannAuthor Commented:
I found two statements have errors:

(1) ".Refresh" in following code
'Source of DataCombo1
    With Adodc2
      .RecordSource = "SELECT CompanyName, SupplierID FROM Suppliers"
      .ConnectionString = ConnectionString
      .Refresh
      .Caption = "Suppliers"
      .Visible = False
    End With

(2) "Set .RowSource = Adodc2" in the following code:

With DataCombo1
      .BoundColumn = "SupplierID"   ' Field in ADODC2 that change                         ' DataField.      Set .RowSource = Adodc2
       Set .RowSource = Adodc2
      .ListField = "CompanyName" ' Field displayed by DataCombo.
      .Refresh
    End With
0
Éric MoreauSenior .Net ConsultantCommented:
And what is the error (number and description)?

Oh! I've seend it!

In the ConnectionString variable, replace the d: by c: for the database location.

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:
Thank you for your help!!!
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.