篩選批量更新記錄

Private Sub Command更新價格_Click()

Dim changeprice As Currency

Dim a1 As String

a1 = InputBox("請輸入調(diào)整的價格（+增加，-為減少）")

If a1 <> "" And IsNumeric(a1) = True Then

changeprice = CCur(a1)

Else

MsgBox "數(shù)據(jù)不符合規(guī)范"

End If

Dim search_rs As DAO.Recordset

Dim search_sql As String

If Me.選擇類別 <> "" Then

search_sql = "Select * From 商品價格表 Where 類別='" & Me.選擇類別 & "'"

Else

search_sql = "Select * From 商品價格表"

End If

Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)

Dim np As Currency

Do While search_rs.EOF = False

search_rs.Edit

np = search_rs!價格.Value

search_rs!價格.Value = np + changeprice

search_rs.Update

search_rs.MoveNext

Loop

search_rs.Close

Set search_rs = Nothing

Me.數(shù)據(jù)表子窗體.Requery

End Sub

Private Sub Command全部記錄_Click()

Me.選擇類別 = ""

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

End Sub

Private Sub 選擇類別_Change()

If Me.選擇類別 <> "" Then

Me.數(shù)據(jù)表子窗體.Form.Filter = "類別='" & Me.選擇類別 & "'"

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

Else

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

End If

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.數(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()

員工編號.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

生成分組數(shù)據(jù)表

Private Sub Command生成_Click()

Dim group_name As String

Dim data_table As String

If Me.原數(shù)據(jù)表 <> "" And Me.分組字段 <> "" Then

data_table = Me.原數(shù)據(jù)表

group_name = Me.分組字段

Else

MsgBox "請輸入原數(shù)據(jù)表和分組字段名稱"

End If

Dim table_sql As String

Dim group_sql As String

group_sql = "Select " & group_name & " From " & data_table & " Group By " & group_name

Dim rs1 As Recordset

Set rs1 = CurrentDb.OpenRecordset(group_sql, dbOpenDynaset)

Do While rs1.EOF = False

'生成表

table_sql = "select * into " & rs1.Fields(group_name) & " from (select * from " & data_table & " where " & group_name & " = '" & rs1.Fields(group_name) & "')"

DoCmd.SetWarnings (False)

DoCmd.RunSQL table_sql

rs1.MoveNext

Loop

End Sub