自定義隱藏?cái)?shù)據(jù)表字段

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 "股票基本數(shù)據(jù)表", con, adOpenDynamic, adLockOptimistic

Dim field_i As Integer

For field_i = 0 To rs.Fields.Count - 1

? ? Me.數(shù)據(jù)表子窗體.Form.Controls(rs.Fields(field_i).Name).ColumnHidden = False

Next field_i

With Me.List隱藏字段

? ? Dim i

? ? For i = 0 To .ListCount - 1

? ? Me.數(shù)據(jù)表子窗體.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 "股票基本數(shù)據(jù)表", con, adOpenDynamic, adLockOptimistic

? ? Dim field_i As Integer

? ? For field_i = 0 To rs.Fields.Count - 1

? ? ? ? .AddItem rs.Fields(field_i).Name

? ? ? ? Me.數(shù)據(jù)表子窗體.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 "股票基本數(shù)據(jù)表", con, adOpenDynamic, adLockOptimistic

? ? Dim field_i As Integer

? ? For field_i = 0 To rs.Fields.Count - 1

? ? ? ? .AddItem rs.Fields(field_i).Name

? ? ? ? Me.數(shù)據(jù)表子窗體.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

組合框應(yīng)用技巧

Public filter_text As String? ? '定義為公共變量

Private Sub Command查詢_Click()

filter_text = ""

If Me.員工編號(hào) <> "" Then

? ? If filter_text <> "" Then

? ? filter_text = filter_text & " and 員工編號(hào) like '*" & Me.員工編號(hào) & "*'"

? ? Else

? ? filter_text = "員工編號(hào) like '*" & Me.員工編號(hào) & "*'"

? ? 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.數(shù)據(jù)表子窗體.Form.Filter = filter_text

Me.數(shù)據(jù)表子窗體.Form.FilterOn = True

Else

Me.數(shù)據(jù)表子窗體.Form.FilterOn = False

End If

End Sub

Private Sub Command清空_Click()

員工編號(hào).Value = ""

姓名.Value = ""

部門.Value = ""

職位.Value = ""

銷售日期1.Value = ""

銷售日期2.Value = ""

銷售額1.Value = ""

銷售額2.Value = ""

End Sub

Private Sub Command全部_Click()

Me.數(shù)據(jù)表子窗體.Form.FilterOn = False

End Sub

Private Sub 部門_AfterUpdate()

Me.職位.Requery

End Sub