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

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
0
leeann
Asked:
leeann
  • 6
  • 5
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
É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
 
leeannAuthor Commented:
Thank you for your help!!!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now