【自定义隐藏数据表字段】【组合框应用技巧】
自定义隐藏数据表字段
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Private Sub Command隐藏_Click()
'-------------------------------显示所有字段
Set con = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "股票基本数据表", con, adOpenDynamic, adLockOptimistic
Dim field_i As Integer
For field_i = 0 To rs.Fields.Count - 1
Me.数据表子窗体.Form.Controls(rs.Fields(field_i).Name).ColumnHidden = False
Next field_i
With Me.List隐藏字段
Dim i
For i = 0 To .ListCount - 1
Me.数据表子窗体.Form.Controls(.ItemData(i)).ColumnHidden = True
Next i
End With
rs.Close
Set rs = Nothing
End Sub
Private Sub Command重置_Click()
'-------------------------------清空列表框，显示全部字段
With Me.List全部字段列表
If .ListCount > 0 Then
Dim remove_i
For remove_i = .ListCount - 1 To 0 Step -1
.RemoveItem Index:=remove_i
Next remove_i
End If
Set con = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "股票基本数据表", con, adOpenDynamic, adLockOptimistic
Dim field_i As Integer
For field_i = 0 To rs.Fields.Count - 1
.AddItem rs.Fields(field_i).Name
Me.数据表子窗体.Form.Controls(rs.Fields(field_i).Name).ColumnHidden = False
Next field_i
rs.Close
Set rs = Nothing
End With
'-------------------------------清空列表框，显示全部字段
With Me.List隐藏字段
If .ListCount > 0 Then
For remove_i = .ListCount - 1 To 0 Step -1
.RemoveItem Index:=remove_i
Next remove_i
End If
End With
End Sub
Private Sub Form_Load()
'-------------------------------清空列表框，显示全部字段
With Me.List全部字段列表
If .ListCount > 0 Then
Dim remove_i
For remove_i = .ListCount - 1 To 0 Step -1
.RemoveItem Index:=remove_i
Next remove_i
End If
Set con = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "股票基本数据表", con, adOpenDynamic, adLockOptimistic
Dim field_i As Integer
For field_i = 0 To rs.Fields.Count - 1
.AddItem rs.Fields(field_i).Name
Me.数据表子窗体.Form.Controls(rs.Fields(field_i).Name).ColumnHidden = False
Next field_i
rs.Close
Set rs = Nothing
End With
'-------------------------------清空列表框，显示全部字段
With Me.List隐藏字段
If .ListCount > 0 Then
For remove_i = .ListCount - 1 To 0 Step -1
.RemoveItem Index:=remove_i
Next remove_i
End If
End With
End Sub
Private Sub List全部字段列表_DblClick(Cancel As Integer)
Dim additemindex As Integer
Dim additemvalue As String
additemindex = Me.List全部字段列表.ListIndex
additemvalue = Me.List全部字段列表.Value
Me.List全部字段列表.RemoveItem additemindex
Me.List隐藏字段.AddItem additemvalue
End Sub
组合框应用技巧
Public filter_text As String '定义为公共变量
Private Sub Command查询_Click()
filter_text = ""
If Me.员工编号 <> "" Then
If filter_text <> "" Then
filter_text = filter_text & " and 员工编号 like '*" & Me.员工编号 & "*'"
Else
filter_text = "员工编号 like '*" & Me.员工编号 & "*'"
End If
End If
If Me.部门 <> "" Then
If filter_text <> "" Then
filter_text = filter_text & " and 部门 like '*" & Me.部门 & "*'"
Else
filter_text = "部门 like '*" & Me.部门 & "*'"
End If
End If
If Me.职位 <> "" Then
If filter_text <> "" Then
filter_text = filter_text & " and 职位 like '*" & Me.职位 & "*'"
Else
filter_text = "职位 like '*" & Me.职位 & "*'"
End If
End If
If Me.姓名 <> "" Then
If filter_text <> "" Then
filter_text = filter_text & " and 姓名 like '*" & Me.姓名 & "*'"
Else
filter_text = "姓名 like '*" & Me.姓名 & "*'"
End If
End If
If Me.销售日期1 <> "" And Me.销售日期2 <> "" Then
If filter_text <> "" Then
filter_text = filter_text & " and 销售日期 between #" & Me.销售日期1 & "# and #" & Me.销售日期2 & "#"
Else
filter_text = "销售日期 between #" & Me.销售日期1 & "# and #" & Me.销售日期2 & "#"
End If
End If
If Me.销售额1 <> "" And Me.销售额2 <> "" Then
If filter_text <> "" Then
filter_text = filter_text & " and 销售额 >= " & Me.销售额1 & " and 销售额<=" & Me.销售额2
Else
filter_text = "销售额 >= " & Me.销售额1 & " and 销售额<=" & Me.销售额2
End If
End If
'------------------------------筛选子窗体
If filter_text <> "" Then
Me.数据表子窗体.Form.Filter = filter_text
Me.数据表子窗体.Form.FilterOn = True
Else
Me.数据表子窗体.Form.FilterOn = False
End If
End Sub
Private Sub Command清空_Click()
员工编号.Value = ""
姓名.Value = ""
部门.Value = ""
职位.Value = ""
销售日期1.Value = ""
销售日期2.Value = ""
销售额1.Value = ""
销售额2.Value = ""
End Sub
Private Sub Command全部_Click()
Me.数据表子窗体.Form.FilterOn = False
End Sub
Private Sub 部门_AfterUpdate()
Me.职位.Requery
End Sub
