DBGrid Drop Down Lists
Michael Dockery
down lists? If so, how?
Alick [MS]
Sample Begin
***********
This example uses the Pubs database that comes with SQL Server.
The ADO datacontrol, named adcTitles, uses the Titles table.
The datagrid, named grdTitles, uses the Titles table, and is bound to
adcTitles.
The datacombo, named cboPubs, style DataList, uses the Publishers table.
The datacombo is used to select the Pub_ID for a Title.
I. Create the User Interface.
a. In Visual Basic 6, create a new Data Project.
DataProject is created by default, and frmDataEnv is created by
default.
Optionally, remove DataEnvironment1 and DataReport1 from the project,
since they are not used.
b. Add the following controls to frmDataEnv, modifying properties for
your
server, user, password:
1. ADO Data Control
Name adcTitles
ConnectionString Provider=SQLOLEDB;User
ID=sa;Password=;Initial Catalog=Pubs;
Data Source=servername
CommandType adCmdTable
RecordSource Titles
Visible False
2. DataGrid
Name grdTitles
DataSource adcTitles
After binding the grdTitles, right-click and choose Retrieve
Fields...
If necessary, choose Yes to Replace existing grid
layout
with new field definitions?
Right-click on grdTitles, choose Properties..
Select the Layout tab.
In the Column dropdown list, select the Pub_id column
Select the Button checkbox.
Increase the Width to 1500.
Note that if you again Retrieve Fields, all settings are lost.
You must again select the Button checkbox, for example.
3. Combo box
Name cboPubs
Style 2 - Dropdown List
Visible False
Since the Combo box is positioned in code, you may place it
anywhere convenient on the form.
II. Add the code.
Paste the following code into frmDataEnv's Code window.
Modify the ConnectionString in the Form Load event to use your server:
Option Explicit
'The datagrid's ButtonClick event is used to display the combo box
' and to select the current Publisher from the combo
'Note that you need to have set the Publisher column's Button property on
'You can use the combobox's ItemData array, if the key field is numeric
'Otherwise, you must workaround, eg. with a parallel array. This sample
shows
this technique.
'Or, use a recordset.
Private PubList() As String
Private Sub grdTitles_ButtonClick(ByVal ColIndex As Integer)
cboPubs.ZOrder 0
'Position the combo box when the button is clicked
cboPubs.Left = grdTitles.Left + grdTitles.Columns(3).Left
cboPubs.Top = grdTitles.Top + grdTitles.RowTop(grdTitles.Row)
cboPubs.Width = grdTitles.Columns(3).Width
'Display the combo box
cboPubs.Visible = True
cboPubs.SetFocus
'Select the Publisher in the list
' based on the value in the grid
Dim i As Integer
For i = 0 To cboPubs.ListCount - 1
If PubList(i) = Val(grdTitles.Text) Then
cboPubs.ListIndex = i
Exit For
End If
Next i
End Sub
'The Click event of the combo box places the Pub ID number into the grid
Private Sub cboPubs_Click()
'When the combo is clicked, update the grid
'example using the ItemData array
'adcTitles.Recordset.Fields("pub_id") =
cboPubs.ItemData(cboPubs.ListIndex)
'example using a parallel array
adcTitles.Recordset.Fields("pub_id") = PubList(cboPubs.ListIndex)
End Sub
Private Sub Command1_Click() 'Delete button
On Error GoTo handler
Dim vBookmark As Variant
With adcTitles.Recordset
If .EditMode = adEditAdd Then
grdTitles.SetFocus
SendKeys "{escape}"
SendKeys "{escape}"
.CancelUpdate
adcTitles.Refresh
.MoveLast
Else
.Delete
.MoveNext
If .EOF Then .MoveLast
End If
End With
Exit Sub
handler:
Select Case Err.Number
Case -2147217864: 'row could not be located
Case -2147217887: 'errors occurred
Resume Next
Case Else:
MsgBox Err.Number & ":" & Err.Description
End Select
End Sub
Private Sub Command2_Click() 'Add button
If adcTitles.Recordset.EditMode <> adEditAdd Then
On Error GoTo AddErr
adcTitles.Recordset.MoveLast
grdTitles.SetFocus
SendKeys "{down}"
Exit Sub
AddErr:
MsgBox Err.Description
cboPubs.Visible = False
End If
End Sub
Private Sub Command3_Click() 'Update button
On Error GoTo handler
adcTitles.Recordset.Update
Exit Sub
handler:
MsgBox Err.Number & ":" & Err.Description
cboStudents.Visible = False
End Sub
'The Form Load event is used to fill the combo box
Private Sub Form_Load()
'Fill cboPubs
Dim rs As New ADODB.Recordset
Dim strCn As String
Dim i As Integer
strCn = "Provider=SQLOLEDB.1;User ID=sa;Password=;Initial
Catalog=pubs;Data
Source=(local)"
rs.Open "select * from publishers", strCn, adOpenStatic, adLockReadOnly
rs.MoveLast
rs.MoveFirst
ReDim PubList(rs.RecordCount)
i = 0
While Not rs.EOF
'Display the student's name in the combo box
cboPubs.AddItem (rs!pub_name) & ""
'But we'll place the Pub ID in the orders table
'example using ItemData
'cboPubs.ItemData(cboPubs.NewIndex) = rs!pub_id
'example using parallel array
PubList(i) = rs!pub_id & ""
i = i + 1
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
End Sub
'The next 3 subs are used to correctly display or hide the combo box:
Private Sub grdTitles_ColResize(ByVal ColIndex As Integer, Cancel As
Integer)
If ColIndex = 3 Then 'Students column is being resized
cboPubs.Width = grdTitles.Columns(ColIndex).Width
End If
End Sub
Private Sub grdTitles_Error(ByVal DataError As Integer, Response As
Integer)
cboPubs.Visible = False
Select Case DataError:
Case 6153: 'specified row could not be located for updating
MsgBox "6153"
adcTitles.Refresh
adcTitles.Recordset.MoveLast
Case Else:
MsgBox DataError
End Select
End Sub
Private Sub grdTitles_RowColChange(LastRow As Variant, ByVal LastCol As
Integer)
cboPubs.Visible = False
End Sub
Private Sub grdTitles_Scroll(Cancel As Integer)
cboPubs.Visible = False
End Sub
Private Sub Form_Unload(Cancel As Integer)
'Perform a final update, in case the user didn't
' move off the last record that they updated
adcTitles.Move 0
End Sub
III. Test.
When you click in a Pub_Id cell, you'll get a Dropdown list of Publisher
names.
You may want to resize the column to see the whole names.
Notice that the current Publisher for the Title is already selected in
the
list.
Select a different Publisher from the dropdown list.
When you move to a different cell, the new Publisher ID is displayed in
the
Pub_Id column.
If you move to a different cell without selecting a new Publisher, the
current value is retained. ***************
Sample End
Hope it helps.
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Michael Dockery" <Michael...@jpmchase.com>
| X-Tomcat-NG: microsoft.public.vb.database