How To Position a DataList Control Directly Under a Cell in a DataGrid (235944)

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