HOWTO: Position a DataList Control Directly Under a Cell in a DataGrid

• ActiveX Data Objects (ADO), versions 2.1 SP1, 2.1 SP2, 2.5
• Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0

SUMMARY

This article explains how to place an unbound DataList control under an associated child cell in a DataGrid. When the user presses the DataGrid button, the DataList is displayed and appears as if the related cell had a Combo box.

MORE INFORMATION

1. Open a new Microsoft Visual Basic Standard EXE project. Form1 is created by default.



2. Set a reference to Microsoft ActiveX Data Objects 2.1.



3. On Form1, place the following controls:




2 ado data controls (adodc1 and adodc2)
1 DataGrid (DataGrid1)
1 DataList (DataList1)


4. Set the following properties:




DataGrid1.DataSource=adodc1
DataList1.RowSource=adodc2
DataList1.ListField=CustomerID
DataList1.BoundColumn=CustomerID

5. Cut and paste the following code into the General Declarations section:




Option Explicit

Dim RowValue
Dim cn As New ADODB.Connection
Dim rsOrders As New ADODB.Recordset
Dim rsCustomers As New ADODB.Recordset

Private Sub DataList1_Click()
  DataGrid1.Text = DataList1.Text
  DataList1.Visible = False
End Sub

Private Sub DataList1_LostFocus()
  DataList1.Visible = False
End Sub

Private Sub DataGrid1_Scroll(Cancel As Integer)
  DataList1.Visible = False
End Sub

Private Sub Form_Load()
    cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;" & _
            "Data Source=NWind.mdb;"
    With rsOrders
        .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .Open "Select * From Orders"
    End With
    Set Adodc1.Recordset = rsOrders
    With rsCustomers
        .ActiveConnection = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select CustomerID From Customers ORDER BY CustomerID"
    End With
    Set Adodc2.Recordset = rsCustomers
    DataList1.Visible = False
    DataGrid1.Columns(1).Button = True
End Sub

Private Sub DataGrid1_ButtonClick(ByVal ColIndex As Integer)
   If ColIndex = 1 Then
      DataList1.Top = DataGrid1.Top + DataGrid1.RowTop(DataGrid1.Row) + DataGrid1.RowHeight
      DataList1.Left = DataGrid1.Left + DataGrid1.Columns(ColIndex).Left
      ' Width and Height properties can be set a design time
      ' The width of the list does not have to be the same as the width of the grid column
      DataList1.Width = DataGrid1.Columns("CustomerID").Width
      DataList1.Height = 1440
      DataList1.Visible = Not DataList1.Visible
      If DataList1.Visible Then
         DataList1.Text = DataGrid1.Text
         DataList1.ZOrder      ' make sure the list is on top of the grid
      End If
   End If
End Sub

Private Sub DataGrid1_Click()
   DataList1.Visible = False
End Sub
 

6. Run the project and note that the DataGrid displays a drop-down button in the CustomerID column. When you press the button, the DataList box appears with a list of CustomerID codes from the Customers table. Select a new CustomerID. When you make the selection, the CustomerID value you selected in the DataList updates the CustomerID field in the DataGrid.

Additional query words:

Keywords : kbADO kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC250
Version : WINDOWS:2.1 SP1,2.1 SP2,2.5,6.0
Platform : WINDOWS
Issue type : kbhowto