标题: VBA代码设置组合框的行来源类型、列数、列宽等值，并添加列表值 [打印本页] 作者: caoguangyao 时间: 2011-6-10 21:42 标题: VBA代码设置组合框的行来源类型、列数、列宽等值，并添加列表值 一、先决条件是：组合框的行来源类型必需设置为“值列表”
二、创建表[tblName],字段列表如下：
file:///C:/Documents%20and%20Settings/caoguangyao/Application%20Data/Tencent/QQ/Temp/RichOle/I1LE8DMQ7MXH%7BH0P$_%25JN%60U.jpg
三、创建窗体[窗体1]，添加一个组合框[Combo1]，添加一个子窗体[Child0]
1。窗体加载事件
Private Sub Form_Load()
Me.Child0.SourceObject = "table.tblName"
End Sub
2。组合框[Combo1]获得焦点事件
Private Sub Combo1_GotFocus()
Dim i As Long
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select [DepartmentID],[Department] from tblName GROUP BY DepartmentID, Department", CurrentProject.Connection, 3, 3
Me.Combo1.RowSourceType = "值列表" '设置行来源类型
If Me.Combo1.ListCount > 0 Then '先删除,方便后继添加
For i = Me.Combo1.ListCount - 1 To 0 Step -1
Me.Combo1.RemoveItem i
Next
End If
With Me.Combo1
.ColumnCount = 2 '设置列数为了2
.Width = 4 * 567 '组合框的宽度为4cm,567为1cm=567缇(像素单位)
.ColumnWidths = "0CM,4CM" '第一列宽0cm,第二列宽2cm
If .ColumnHeads = True Then '设置列标题
.AddItem "部门ID,部门"
End If
.AddItem "无,无"
rs.MoveFirst
For i = 0 To rs.RecordCount - 1
.AddItem rs("DepartmentID") & ", " & rs("Department")'添加记录集到组合框
rs.MoveNext
Next
.AddItem "全部,全部"
.DefaultValue = "全部"
End With
End Sub
3。组合框的更新后事件
Private Sub Combo1_AfterUpdate()
If Me.Combo1.Column(1) = "无" Then
Me.Child0.Form.RecordSource = ""
ElseIf Me.Combo1.Column(1) = "全部" Then
Me.Child0.Form.RecordSource = "select * from tblName"
Else
Me.Child0.Form.RecordSource = "select * from tblName where [Department]='" & Me.Combo1.Column(1) & "'"
End If
Me.Child0.Requery
End Sub
