貨品庫存管理系統(tǒng)-單機(jī)版（1） VBA代碼 源代碼分析 Access數(shù)據(jù)庫管理系統(tǒng)
代碼較多，建議復(fù)制代碼至本地文檔后可按窗體名稱搜索
出庫單查詢
Option Compare Database
Option Explicit
?
?
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
Dim search_field As String
If Me.查詢字段 = "出庫日期" Then
?
??? If 起始日期 <> "" And IsNull(起始日期) = False And 截止日期 <> "" And IsNull(截止日期) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
??????? search_field = Me.查詢字段
??????? ck_filter = search_field & " between #" & Me.起始日期 & "# and #" & Me.截止日期 & "#"
??????? Me.數(shù)據(jù)表子窗體.Form.Filter = ck_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
?
??? Else
????? ??ck_filter = ""
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
?
??? End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
End If
If Me.查詢字段 = "金額" Then
??? If 最小 <> "" And IsNull(最小) = False And 最大 <> "" And IsNull(最大) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
?
?
??????? search_field = Me.查詢字段
??????? ck_filter = search_field & " >= " & Me.最小 & " And " & search_field & " <= " & Me.最大
??????? Me.數(shù)據(jù)表子窗體.Form.Filter = ck_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
??? Else
??????? ck_filter = ""
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
??? End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
End If
?
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
?
??? search_field = Me.查詢字段
??? ck_filter = search_field & " like '*" & Me.查詢內(nèi)容 & "*'"
??? Me.數(shù)據(jù)表子窗體.Form.Filter = ck_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
?
Else
??? ck_filter = ""
??? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
結(jié)束查詢:
??? MsgBox Err.Description
End Sub
?
?
?
Private Sub Command全部_Click()
ck_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
?
?
Private Sub Command數(shù)據(jù)導(dǎo)出_Click()
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------清空表數(shù)據(jù)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 導(dǎo)出出庫單表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
ck_filter = Me.數(shù)據(jù)表子窗體.Form.Filter
?
If ck_filter <> "" And Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 出庫單查詢 " & " Where " & ck_filter
Else
dflink_sql = "SELECT * FROM 出庫單查詢"
End If
?
ck_order = Me.數(shù)據(jù)表子窗體.Form.OrderBy
?
If ck_order <> "" Then
dflink_sql = dflink_sql & " order by " & ck_order
End If
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("導(dǎo)出出庫單表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!出庫單號(hào).Value = !出庫單號(hào).Value
add_rs!領(lǐng)用單位.Value = !領(lǐng)用單位.Value
add_rs!出庫類別.Value = !出庫類別.Value
add_rs!出庫日期.Value = !出庫日期.Value
add_rs!領(lǐng)取人.Value = !領(lǐng)取人.Value
add_rs!經(jīng)辦人.Value = !經(jīng)辦人.Value
add_rs!備注.Value = !備注.Value
add_rs!金額.Value = !金額.Value
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
?
Call 導(dǎo)出查詢表結(jié)果("導(dǎo)出出庫單表")
?
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
Private Sub Command添加_Click()
DoCmd.OpenForm "出庫單添加", acNormal
End Sub
?
Private Sub Form_Load()
DoCmd.Restore
?
?
ck_filter = ""
ck_order = "出庫單號(hào) DESC"
?
'Me.數(shù)據(jù)表子窗體.Form.OrderBy = ck_order
'Me.數(shù)據(jù)表子窗體.Form.OrderByOn = True
?
Me.查詢內(nèi)容.Visible = True
'-----------------------------隱藏日期控件
Me.起始日期.Visible = False
Me.截止日期.Visible = False
'-----------------------------隱藏金額控件
Me.最小.Visible = False
Me.最大.Visible = False
?
End Sub
Private Sub 查詢字段_Change()
If Me.查詢字段 = "出庫日期" Then
Me.起始日期.Visible = True
Me.截止日期.Visible = True
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
If Me.查詢字段 = "金額" Then
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = True
Me.最大.Visible = True
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
End Sub
?
出庫單查詢數(shù)據(jù)表
Private Sub 出庫單號(hào)_DblClick(Cancel As Integer)
ck_num = 出庫單號(hào)
DoCmd.OpenForm "出庫單管理", acNormal, , "出庫單號(hào)='" & 出庫單號(hào) & "'"
End Sub
出庫單管理
Option Compare Database
Option Explicit
?
Public update_num As Integer
?
?
?
Private Sub Command更新_Click()
On Error Resume Next
If 出庫單號(hào) = "" Or IsNull(出庫單號(hào)) = True Then
MsgBox "出庫單號(hào)值為空！"
Exit Sub
End If
?
If 出庫日期 = "" Or IsNull(出庫日期) = True Then
MsgBox "出庫日期值為空！"
Exit Sub
End If
?
?
update_num = 1
If MsgBox("是否更新該記錄", vbYesNo) <> vbYes Then
DoCmd.RunCommand acCmdUndo
Exit Sub
End If
DoCmd.SetWarnings (False)
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
?
ck_num = Me.出庫單號(hào)
?
?
Exit Sub
If Error.Number <> 0 Then
MsgBox Error.Description
Else
?
End If
End Sub
?
Private Sub Command出庫單_Click()
On Error GoTo outputerror
?
'------------------------------------------
Dim copyfilename As String
copyfilename = "出庫單.xlsx"??? '要復(fù)制的源文件（Excel）
'---------------------------------輸入文件名
Dim outputname As String
outputname = InputBox("請(qǐng)輸入導(dǎo)出的文件名", "導(dǎo)出出庫單", "出庫單" & Me.出庫單號(hào))?????? '---------------------------------------------------輸入要生成的表名
If outputname = "" Or IsNull(outputname) Then?? '為空則不執(zhí)行程序
'GoTo outputerror
Exit Sub
End If
'----------------------------------選擇導(dǎo)出的位置（文件夾）
Dim exportpath As String
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
With dlgOpen
???? If .Show = -1 Then
??????? exportpath = .SelectedItems(1)
??????? Else
??????? Exit Sub
???? End If
End With
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
??? fs.copyfile fs.BuildPath(CurrentProject.Path, copyfilename), exportpath & "\" & outputname & ".xlsx"
'================================================================================打開創(chuàng)建的表并處理數(shù)據(jù)
Dim excelopenpathname As String
excelopenpathname = exportpath & "\" & outputname & ".xlsx"???? '復(fù)制粘貼新Excel文件的路徑和名稱
'================================================================================處理打開的表數(shù)據(jù)
?
?
?
?
Dim xlapp As Object
Set xlapp = CreateObject("excel.application")
Dim xlwbk As Object
Dim xlwsh As Object
?
?
Dim CreateExcel As Object
Set CreateExcel = xlapp
xlapp.Visible = False
Set xlwbk = xlapp.WorkBooks.Open(excelopenpathname)
xlwbk.Activate
Set xlwsh = xlwbk.Worksheets(1)
With xlwsh
??? '--------------------------------------------------處理過程
??? .cells(1, "K").Value = Me.出庫單號(hào).Value
??? .cells(3, "D").Value = Me.領(lǐng)用單位.Value
??? .cells(3, "H").Value = Me.出庫類別.Value
??? .cells(3, "K").Value = Me.出庫日期.Value
??? .cells(6, "K").Value = Me.金額.Value
??? .cells(7, "D").Value = Me.領(lǐng)取人.Value
??? .cells(7, "K").Value = Me.經(jīng)辦人.Value
??? .cells(8, "D").Value = Me.備注.Value
??? .cells(6, "D").Value = rmb(Me.金額)
End With
'Set xlwsh = xlwbk.Worksheets(2)
With xlwsh
'****************************************************************循環(huán)采購出庫查詢
?
Dim search_sql As String
?
search_sql = "SELECT * FROM 出庫明細(xì)查詢 " & " Where 出庫單號(hào)='" & Me.出庫單號(hào) & "' Order by 出庫序號(hào) ASC"
?
Dim search_rs As DAO.Recordset
?
'MsgBox search_sql
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
?
'------------------------------------------------------------------
Dim add_row As Long
add_row = 5
Do While search_rs.EOF = False
.rows(add_row).Insert
?
.cells(add_row, 2).Value = search_rs!出庫序號(hào).Value
.cells(add_row, 3).Value = search_rs!貨品編號(hào).Value
.cells(add_row, 4).Value = search_rs!貨品名稱.Value
.cells(add_row, 5).Value = search_rs!規(guī)格型號(hào).Value
.cells(add_row, 6).Value = search_rs!貨品類別.Value
.cells(add_row, 9).Value = search_rs!單位.Value
.cells(add_row, 7).Value = search_rs!單價(jià).Value
.cells(add_row, 8).Value = search_rs!數(shù)量.Value
.cells(add_row, 10).Value = search_rs!貨品金額.Value
.cells(add_row, 11).Value = search_rs!明細(xì)備注.Value
?
add_row = add_row + 1
search_rs.MoveNext
Loop
?
?
'------------------------------------------------------------------
search_rs.Close
Set search_rs = Nothing
?
?
'****************************************************************
End With
xlwbk.Save
xlwbk.Close
xlapp.Quit
MsgBox "導(dǎo)出完成"
?
'------------------------------------------
Exit Sub
outputerror:
MsgBox "導(dǎo)出數(shù)據(jù)出錯(cuò)，請(qǐng)檢查！可能存在同名工作簿" & vbCrLf & Err.Description
End Sub
?
Private Sub Command刪除_Click()
If MsgBox("是否刪除該記錄", vbYesNo) <> vbYes Then
Exit Sub
End If
On Error Resume Next
DoCmd.SetWarnings (False)
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "刪除成功"
DoCmd.Close acForm, Me.Name
If Error.Number <> 0 Then
MsgBox Error.Description
End If
End Sub
?
?
?
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
If update_num = 1 Then
update_num = 0
Exit Sub
End If
?
??? If (MsgBox("是否保存對(duì)出庫單記錄修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
'??????? MsgBox "記錄修改成功", vbyesOnly, "提醒"
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
'??? Exit Function
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub Form_Close()
On Error Resume Next
Forms("出庫單查詢").數(shù)據(jù)表子窗體.Requery
End Sub
?
?
?
Private Sub Form_Load()
ck_num = Me.出庫單號(hào)
Me.金額 = Nz(DLookup("金額", "出庫單金額查詢", "出庫單號(hào)='" & Me.出庫單號(hào) & "'"), 0)
End Sub
?
Private Sub 選擇貨品_Change()
If Me.選擇貨品 <> "" Then
Me.貨品庫存 = Nz(DLookup("當(dāng)前庫存", "庫存統(tǒng)計(jì)查詢", "貨品編號(hào)='" & Me.選擇貨品 & "'"), 0)
Else
Me.貨品庫存 = ""
End If
End Sub
出庫單添加
Option Compare Database
Option Explicit
?
Private Sub Command導(dǎo)入_Click()
On Error GoTo inputerror
Dim upfilename As String
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = False
.Filters.Add "Excel", "*.xlsx; *.xlsm", 1
???? If .Show = -1 Then
??????? upfilename = .SelectedItems(1)
???? Else
??????? Exit Sub
???? End If
End With
?
'------------------------------------------------清空表數(shù)據(jù)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 本地出庫單表"
DoCmd.RunSQL del_sql
'------------------------------------------
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("本地出庫單表", dbOpenTable)
'打開創(chuàng)建的表并處理數(shù)據(jù)
Dim excelopenpathname As String
excelopenpathname = upfilename???? '復(fù)制粘貼新Excel文件的路徑和名稱
'處理打開的表數(shù)據(jù)
Dim xlapp As excel.Application
Dim xlwbk As excel.Workbook
Dim xlwsh As excel.Worksheet
Set xlapp = New excel.Application
Dim CreateExcel As Object
Set CreateExcel = xlapp
xlapp.Visible = True
Set xlwbk = xlapp.WorkBooks.Open(excelopenpathname)
xlwbk.Activate
Set xlwsh = xlwbk.Worksheets(1)
With xlwsh
??? Dim i
??? On Error Resume Next
??? '--------------------------------------------------處理過程
??? If .cells(2, 1).Value <> "" Then
??? Else
??? GoTo exitexcel
??? End If
??? For i = 2 To .Range("A1").End(xlDown).Row
????? ??add_rs.AddNew
add_rs!出庫單號(hào).Value = .cells(i, 1).Value
add_rs!領(lǐng)用單位.Value = .cells(i, 2).Value
add_rs!出庫類別.Value = .cells(i, 3).Value
add_rs!出庫日期.Value = .cells(i, 4).Value
add_rs!領(lǐng)取人.Value = .cells(i, 5).Value
add_rs!經(jīng)辦人.Value = .cells(i, 6).Value
add_rs!備注.Value = .cells(i, 7).Value
?
??????? add_rs.Update
??? Next i
End With
exitexcel:
xlwbk.Save
xlwbk.Close
xlapp.Quit
?
'------------------------------------------
Me.數(shù)據(jù)表子窗體.Requery
Exit Sub
inputerror:
MsgBox Err.Description
End Sub
?
Private Sub Command清空_Click()
出庫單號(hào).Value = 生成出庫單號(hào)(Date)
領(lǐng)用單位.Value = ""
出庫類別.Value = ""
出庫日期.Value = Date
領(lǐng)取人.Value = ""
經(jīng)辦人.Value = ""
備注.Value = ""
?
End Sub
?
Private Sub Command上傳_Click()
On Error GoTo 上傳數(shù)據(jù)失敗錯(cuò)誤
?
If MsgBox("是否將數(shù)據(jù)添加至出庫單表，注意：重復(fù)的出庫單號(hào)將不會(huì)添加", vbOKCancel) <> vbOK Then
Exit Sub
End If
?
If Nz(DCount("出庫單號(hào)", "本地出庫單表"), 0) = 0 Then??? '上傳出庫單記錄數(shù)量
Exit Sub
End If
?
'---------------------------------------------------------------查詢前端
Dim search_rs As DAO.Recordset
Dim search_sql As String
search_sql = "Select * From 本地出庫單表"
Set search_rs = CurrentDb.OpenRecordset(search_sql, dbOpenDynaset)
'---------------------------------------------------------------建立查詢
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("出庫單表", dbOpenTable)
'---------------------------------------------------------------
?
On Error Resume Next
Do While search_rs.EOF = False
?
add_rs.AddNew
?
add_rs!出庫單號(hào).Value = search_rs!出庫單號(hào).Value
add_rs!領(lǐng)用單位.Value = search_rs!領(lǐng)用單位.Value
add_rs!出庫類別.Value = search_rs!出庫類別.Value
add_rs!出庫日期.Value = search_rs!出庫日期.Value
add_rs!領(lǐng)取人.Value = search_rs!領(lǐng)取人.Value
add_rs!經(jīng)辦人.Value = search_rs!經(jīng)辦人.Value
add_rs!備注.Value = search_rs!備注.Value
?
?
add_rs.Update
?
search_rs.MoveNext
Loop
'---------------------------------------------------------------斷開后端鏈接
?
add_rs.Close
Set add_rs = Nothing
?
'---------------------------------------------------------------斷開前端鏈接
search_rs.Close
Set search_rs = Nothing
?
MsgBox "上傳數(shù)據(jù)完成"
Exit Sub
上傳數(shù)據(jù)失敗錯(cuò)誤:
MsgBox "上傳數(shù)據(jù)失??！" & vbCrLf & Err.Description
?
End Sub
?
Private Sub Command添加_Click()
On Error GoTo 添加失敗錯(cuò)誤
?
If 出庫單號(hào) = "" Or IsNull(出庫單號(hào)) = True Then
MsgBox "出庫單號(hào)值為空！"
Exit Sub
End If
?
If 出庫日期 = "" Or IsNull(出庫日期) = True Then
MsgBox "出庫日期值為空！"
Exit Sub
End If
?
?
?
If Nz(DCount("出庫單號(hào)", "出庫單表", "出庫單號(hào)='" & Me.出庫單號(hào) & "'"), 0) > 0 Then
MsgBox "該出庫單號(hào)已存在！"
Exit Sub
End If
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("出庫單表", dbOpenTable)
add_rs.AddNew
'--------------------------------------------------------------字段賦值
On Error Resume Next
With add_rs
add_rs!出庫單號(hào).Value = 出庫單號(hào).Value
add_rs!領(lǐng)用單位.Value = 領(lǐng)用單位.Value
add_rs!出庫類別.Value = 出庫類別.Value
add_rs!出庫日期.Value = 出庫日期.Value
add_rs!領(lǐng)取人.Value = 領(lǐng)取人.Value
add_rs!經(jīng)辦人.Value = 經(jīng)辦人.Value
add_rs!備注.Value = 備注.Value
?
'add_rs!金額.Value = 金額.Value
?
?
End With
'----------------------------------------------------------
add_rs.Update
add_rs.Close
Set add_rs = Nothing
'----------------------------------------------------------
?
MsgBox "添加成功！"
Call Command清空_Click
?
Exit Sub
添加失敗錯(cuò)誤:
MsgBox Err.Description
End Sub
?
Private Sub Form_Close()
On Error Resume Next
ck_filter = ""
Forms("出庫單查詢").數(shù)據(jù)表子窗體.Requery
?
End Sub
?
Private Sub Form_Load()
DoCmd.Restore
Me.出庫日期 = Date
?
?
Me.出庫單號(hào) = 生成出庫單號(hào)(Date)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 本地出庫單表"
DoCmd.RunSQL del_sql
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
?
?
?
?
Private Sub 出庫單號(hào)_DblClick(Cancel As Integer)
If Me.出庫日期 <> "" Then
Me.出庫單號(hào) = 生成出庫單號(hào)(Me.出庫日期)
Else
Me.出庫單號(hào) = 生成出庫單號(hào)(Date)
End If
End Sub
?
Private Sub 出庫日期_DblClick(Cancel As Integer)
Me.出庫日期 = Date
End Sub
?
?
?
Function 生成出庫單號(hào)(ByVal numdate As Date) As String
On Error GoTo 錯(cuò)誤
Dim search_num As String
?
search_num = "CK" & Format(numdate, "YYYYMMDD")
?
Dim num_count As Long
num_count = Nz(DCount("出庫單號(hào)", "出庫單表", "出庫單號(hào) like '*" & search_num & "*'"), 0) + 1
生成出庫單號(hào) = search_num & "" & Format(num_count, "000")
Exit Function
錯(cuò)誤:
生成出庫單號(hào) = ""
?
End Function
出庫類別數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If gx_num = 0 Then
Exit Sub
End If
?
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對(duì)記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
'??????? MsgBox "記錄修改成功", vbyesOnly, "提醒"
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
'??? Exit Function
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
出庫明細(xì)查詢數(shù)據(jù)表
Option Compare Database
?
Public maxid As Long
?
Private Sub Form_AfterUpdate()
?
On Error Resume Next
?
Forms("出庫單管理").金額 = Nz(DLookup("金額", "出庫單金額查詢", "出庫單號(hào)='" & Forms("出庫單管理").出庫單號(hào) & "'"), 0)
?
If Forms("出庫單管理").選擇貨品 = Me.貨品編號(hào) Then
Forms("出庫單管理").貨品庫存 = Nz(DLookup("當(dāng)前庫存", "庫存統(tǒng)計(jì)查詢", "貨品編號(hào)='" & Me.貨品編號(hào) & "'"), 0)
End If
End Sub
?
Private Sub Form_BeforeUpdate(Cancel As Integer)
?
If gx_num = 0 Then
Exit Sub
End If
?
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對(duì)出入庫明細(xì)記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
'??????? MsgBox "記錄修改成功", vbyesOnly, "提醒"
'??????? Me.貨品編號(hào).RowSource = ""
'??????? Me.貨品編號(hào).RowSource = "SELECT 貨品表.貨品編號(hào), 貨品表.貨品名稱, 貨品表.貨品類別, 庫存統(tǒng)計(jì)查詢.當(dāng)前庫存 FROM 貨品表 INNER JOIN 庫存統(tǒng)計(jì)查詢 ON 貨品表.貨品編號(hào) = 庫存統(tǒng)計(jì)查詢.貨品編號(hào)"
??? maxid = Nz(DMax("出庫明細(xì)ID", "出庫明細(xì)表", "出庫單號(hào)='" & Forms("出庫單管理").出庫單號(hào) & "'"), 0)
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
'??? Exit Function
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
?
Private Sub Form_Load()
maxid = Nz(DMax("出庫明細(xì)ID", "出庫明細(xì)表", "出庫單號(hào)='" & Forms("出庫單管理").出庫單號(hào) & "'"), 0)
?
End Sub
?
Private Sub Form_Timer()
If ckhp_num = 1 Then
?
??? If Me.貨品編號(hào) <> ckhp_text Then
??? Me.貨品編號(hào) = ckhp_text
??? Me.單價(jià) = Nz(DLookup("出庫價(jià)", "貨品表", "貨品編號(hào)='" & Me.貨品編號(hào) & "'"), 0)
??? End If
ckhp_num = 0
End If
End Sub
?
Private Sub 貨品編號(hào)_AfterUpdate()
?
Me.單價(jià) = Nz(DLookup("出庫價(jià)", "貨品表", "貨品編號(hào)='" & Me.貨品編號(hào) & "'"), 0)
?
End Sub
?
Private Sub 貨品編號(hào)_Change()
?
Me.單價(jià) = Nz(DLookup("出庫價(jià)", "貨品表", "貨品編號(hào)='" & Me.貨品編號(hào) & "'"), 0)
?
End Sub
?
Private Sub 貨品編號(hào)_DblClick(Cancel As Integer)
hp_type = 4
DoCmd.OpenForm "貨品選擇", acNormal
End Sub
?
Private Sub 數(shù)量_BeforeUpdate(Cancel As Integer)
If 數(shù)量 <> "" And Me.出庫明細(xì)ID > maxid Then
Dim 貨品庫存num As Single
貨品庫存num = Nz(DLookup("當(dāng)前庫存", "庫存統(tǒng)計(jì)查詢", "貨品編號(hào)='" & Me.貨品編號(hào) & "'"), 0)
??? If Me.數(shù)量 > 貨品庫存num Then
??? MsgBox "注意：出庫數(shù)量大于當(dāng)前庫存數(shù)量" & vbCrLf & "當(dāng)前貨品庫存: " & 貨品庫存num & " " & Me.單位
??? End If
End If
End Sub
?
出入庫明細(xì)查詢
Option Compare Database
Option Explicit
?
?
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
Dim search_field As String
If Me.查詢字段 = "出入庫日期" Then
?
??? If 起始日期 <> "" And IsNull(起始日期) = False And 截止日期 <> "" And IsNull(截止日期) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
??????? search_field = Me.查詢字段
??????? crkmx_filter = search_field & " between #" & Me.起始日期 & "# and #" & Me.截止日期 & "#"
??????? Me.數(shù)據(jù)表子窗體.Form.Filter = crkmx_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
?
??? Else
??????? crkmx_filter = ""
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
?
??? End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
End If
If Me.查詢字段 = "單價(jià)" Or Me.查詢字段 = "數(shù)量" Or Me.查詢字段 = "貨品金額" Then
??? If 最小 <> "" And IsNull(最小) = False And 最大 <> "" And IsNull(最大) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
?
?
??????? search_field = Me.查詢字段
??????? crkmx_filter = search_field & " >= " & Me.最小 & " And " & search_field & " <= " & Me.最大
??????? Me.數(shù)據(jù)表子窗體.Form.Filter = crkmx_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
??? Else
??????? crkmx_filter = ""
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
??? End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
End If
?
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
?
??? search_field = Me.查詢字段
??? crkmx_filter = search_field & " like '*" & Me.查詢內(nèi)容 & "*'"
??? Me.數(shù)據(jù)表子窗體.Form.Filter = crkmx_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
?
Else
??? crkmx_filter = ""
??? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
結(jié)束查詢:
??? MsgBox Err.Description
End Sub
?
?
Private Sub Command全部_Click()
crkmx_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
?
?
Private Sub Command數(shù)據(jù)導(dǎo)出_Click()
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------清空表數(shù)據(jù)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 導(dǎo)出出入庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
crkmx_filter = Me.數(shù)據(jù)表子窗體.Form.Filter
?
If crkmx_filter <> "" And Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 出入庫明細(xì)查詢 " & " Where " & crkmx_filter
Else
dflink_sql = "SELECT * FROM 出入庫明細(xì)查詢"
End If
?
crkmx_order = Me.數(shù)據(jù)表子窗體.Form.OrderBy
?
If crkmx_order <> "" Then
dflink_sql = dflink_sql & " order by " & crkmx_order
End If
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("導(dǎo)出出入庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!貨品名稱.Value = !貨品名稱.Value
add_rs!規(guī)格型號(hào).Value = !規(guī)格型號(hào).Value
add_rs!貨品類別.Value = !貨品類別.Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!單位.Value = !單位.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
add_rs!供領(lǐng)人.Value = !供領(lǐng)人.Value
add_rs!經(jīng)辦人.Value = !經(jīng)辦人.Value
add_rs!出入庫類別.Value = !出入庫類別.Value
add_rs!供領(lǐng)單位.Value = !供領(lǐng)單位.Value
add_rs!明細(xì)備注.Value = !明細(xì)備注.Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
?
Call 導(dǎo)出查詢表結(jié)果("導(dǎo)出出入庫明細(xì)表")
?
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
Private Sub Command添加_Click()
DoCmd.OpenForm "出入庫明細(xì)添加", acNormal
End Sub
?
Private Sub Form_Load()
DoCmd.Restore
?
?
crkmx_filter = ""
crkmx_order = "出入庫日期 DESC"
?
?
Me.查詢內(nèi)容.Visible = True
'-----------------------------隱藏日期控件
Me.起始日期.Visible = False
Me.截止日期.Visible = False
'-----------------------------隱藏金額控件
Me.最小.Visible = False
Me.最大.Visible = False
?
End Sub
Private Sub 查詢字段_Change()
If Me.查詢字段 = "出入庫日期" Then
Me.起始日期.Visible = True
Me.截止日期.Visible = True
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
If Me.查詢字段 = "單價(jià)" Or Me.查詢字段 = "數(shù)量" Or Me.查詢字段 = "貨品金額" Then
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = True
Me.最大.Visible = True
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
End Sub
出入庫明細(xì)查詢數(shù)據(jù)表
Option Compare Database
Option Explicit
?
?
?
?
Private Sub Command查詢_Click()
If Me.起始日期 <> "" And Me.截止日期 <> "" Then
?
Call 復(fù)制入庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 復(fù)制出庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 貨品出入庫匯總
?
Else
MsgBox "請(qǐng)選擇日期"
Exit Sub
End If
?
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
Private Sub Command全部_Click()
crkcx_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
Private Sub Command數(shù)據(jù)導(dǎo)出_Click()
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------清空表數(shù)據(jù)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 導(dǎo)出出入庫統(tǒng)計(jì)查詢表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
crkcx_filter = Me.數(shù)據(jù)表子窗體.Form.Filter
?
If crkcx_filter <> "" And Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 出入庫統(tǒng)計(jì)查詢 " & " Where " & crkcx_filter
Else
dflink_sql = "SELECT * FROM 出入庫統(tǒng)計(jì)查詢"
End If
?
crkcx_order = Me.數(shù)據(jù)表子窗體.Form.OrderBy
?
If crkcx_order <> "" Then
dflink_sql = dflink_sql & " order by " & crkcx_order
End If
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("導(dǎo)出出入庫統(tǒng)計(jì)查詢表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!貨品名稱.Value = !貨品名稱.Value
add_rs!規(guī)格型號(hào).Value = !規(guī)格型號(hào).Value
add_rs!貨品類別.Value = !貨品類別.Value
add_rs!單位.Value = !單位.Value
add_rs!入庫數(shù)量.Value = !入庫數(shù)量.Value
add_rs!入庫均價(jià).Value = !入庫均價(jià).Value
add_rs!入庫金額.Value = !入庫金額.Value
add_rs!出庫數(shù)量.Value = !出庫數(shù)量.Value
add_rs!出庫均價(jià).Value = !出庫均價(jià).Value
add_rs!出庫金額.Value = !出庫金額.Value
add_rs!成本均價(jià).Value = !成本均價(jià).Value
add_rs!成本金額.Value = !成本金額.Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
?
Call 導(dǎo)出查詢表結(jié)果("導(dǎo)出出入庫統(tǒng)計(jì)查詢表")
?
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Dim rfdate As Date
Dim rldate As Date
Dim rftext As String
Dim rltext As String
?
Dim cfdate As Date
Dim cldate As Date
Dim cftext As String
Dim cltext As String
?
?
rftext = Nz(DMin("最早入庫日期", "入庫日期查詢"), "")
rltext = Nz(DMax("最后入庫日期", "入庫日期查詢"), "")
?
?
cftext = Nz(DMin("最早出庫日期", "出庫日期查詢"), "")
cltext = Nz(DMax("最后出庫日期", "出庫日期查詢"), "")
?
?
If (rftext <> "" And rltext <> "") Or (cftext <> "" And cltext <> "") Then
?
rfdate = CDate(rftext)
rldate = CDate(rltext)
?
cfdate = CDate(cftext)
cldate = CDate(cltext)
?
If rfdate < cfdate Then
Me.起始日期 = rfdate
Else
Me.起始日期 = cfdate
End If
?
?
If rldate > cldate Then
Me.截止日期 = rldate
Else
Me.截止日期 = cldate
End If
?
Else
Me.起始日期 = ""
Me.截止日期 = ""
End If
?
?
?
'---------------------------如果日期不為空
If Me.起始日期 <> "" And Me.截止日期 <> "" Then
Call 復(fù)制入庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 復(fù)制出庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 貨品出入庫匯總
?
?
Me.數(shù)據(jù)表子窗體.Requery
End If
?
?
End Sub
?
?
?
?
Sub 復(fù)制入庫單明細(xì)表(ByVal fd As Date, ByVal ld As Date)
?
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------------前期
?
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 前期入庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
dflink_sql = "SELECT * FROM 入庫單明細(xì)查詢 Where 出入庫日期 <#" & fd & "#"
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("前期入庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
'------------------------------------------------------期中
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 期中入庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
dflink_sql = "SELECT * FROM 入庫單明細(xì)查詢 Where 出入庫日期 >=#" & fd & "# and 出入庫日期 <=#" & ld & "#"
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
?
Set add_rs = CurrentDb.OpenRecordset("期中入庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------后期
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 后期入庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
?
dflink_sql = "SELECT * FROM 入庫單明細(xì)查詢 Where 出入庫日期 >#" & ld & "#"
?
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
?
Set add_rs = CurrentDb.OpenRecordset("后期入庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
?
?
?
Sub 復(fù)制出庫單明細(xì)表(ByVal fd As Date, ByVal ld As Date)
?
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------------前期
?
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 前期出庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
?
dflink_sql = "SELECT * FROM 出庫單明細(xì)查詢 Where 出入庫日期 <#" & fd & "#"
?
?
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("前期出庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
'------------------------------------------------------期中
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 期中出庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
?
?
dflink_sql = "SELECT * FROM 出庫單明細(xì)查詢 Where 出入庫日期 >=#" & fd & "# and 出入庫日期 <=#" & ld & "#"
?
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
?
Set add_rs = CurrentDb.OpenRecordset("期中出庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------后期
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 后期出庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
?
dflink_sql = "SELECT * FROM 出庫單明細(xì)查詢 Where 出入庫日期 >#" & ld & "#"
?
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Set add_rs = CurrentDb.OpenRecordset("后期出庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
?
?
Sub 貨品出入庫匯總()
?
On Error GoTo 導(dǎo)出失敗
?
?
?
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 出入庫統(tǒng)計(jì)表"
DoCmd.RunSQL del_sql
?
?
?
?
Dim dflink_sql As String
dflink_sql = "SELECT * FROM 貨品表"
?
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("出入庫統(tǒng)計(jì)表", dbOpenTable)
?
'-------------------------
Dim 入庫數(shù)量num As Single
Dim 出庫數(shù)量num As Single
?
Dim 期初庫存num As Single
Dim 期初金額num As Currency
?
?
Dim 前期入庫數(shù)量num As Single
Dim 前期入庫金額num As Currency
?
Dim 期中入庫數(shù)量num As Single
Dim 期中入庫金額num As Currency
?
Dim 成本均價(jià)num As Currency
?
Do While .EOF = False
?
入庫數(shù)量num = Nz(DLookup("期中入庫數(shù)量", "期中入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
出庫數(shù)量num = Nz(DLookup("期中出庫數(shù)量", "期中出庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
期初庫存num = Nz(!期初庫存.Value, 0)
期初金額num = Nz(!期初金額.Value, 0)
?
前期入庫數(shù)量num = Nz(DLookup("前期入庫數(shù)量", "前期入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
前期入庫金額num = Nz(DLookup("前期入庫金額", "前期入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
?
期中入庫數(shù)量num = 入庫數(shù)量num
期中入庫金額num = Nz(DLookup("期中入庫金額", "期中入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
?
If 期中入庫數(shù)量num + 前期入庫數(shù)量num + 期初庫存num > 0 Then
??? 成本均價(jià)num = (期初金額num + 前期入庫金額num + 期中入庫金額num) / (期中入庫數(shù)量num + 前期入庫數(shù)量num + 期初庫存num)
Else
??? 成本均價(jià)num = 0???? '是0還是出庫均價(jià)
End If
?
If 入庫數(shù)量num <> 0 Or 出庫數(shù)量num <> 0 Then
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!入庫數(shù)量.Value = 入庫數(shù)量num
add_rs!入庫均價(jià).Value = Nz(DLookup("期中入庫均價(jià)", "期中入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
add_rs!入庫金額.Value = 期中入庫金額num
add_rs!出庫數(shù)量.Value = 出庫數(shù)量num
add_rs!出庫均價(jià).Value = Nz(DLookup("期中出庫均價(jià)", "期中出庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
add_rs!出庫金額.Value = Nz(DLookup("期中出庫金額", "期中出庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
add_rs!成本均價(jià).Value = 成本均價(jià)num
add_rs!成本金額.Value = 成本均價(jià)num * 出庫數(shù)量num
?
add_rs.Update
End If
?
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
?
'------------------------------------------------------
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
Private Sub 選擇貨品_Change()
If Me.選擇貨品 <> "" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = "貨品編號(hào)='" & Me.選擇貨品 & "'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
End If
End Sub
?
'Private Sub 選擇貨品_DblClick(Cancel As Integer)
'hp_type = 2
'DoCmd.OpenForm "貨品選擇", acNormal
'End Sub
出入庫統(tǒng)計(jì)查詢
Option Compare Database
Option Explicit
?
?
?
?
Private Sub Command查詢_Click()
If Me.起始日期 <> "" And Me.截止日期 <> "" Then
?
Call 復(fù)制入庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 復(fù)制出庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 貨品出入庫匯總
?
Else
MsgBox "請(qǐng)選擇日期"
Exit Sub
End If
?
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
Private Sub Command全部_Click()
crkcx_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
Private Sub Command數(shù)據(jù)導(dǎo)出_Click()
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------清空表數(shù)據(jù)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 導(dǎo)出出入庫統(tǒng)計(jì)查詢表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
crkcx_filter = Me.數(shù)據(jù)表子窗體.Form.Filter
?
If crkcx_filter <> "" And Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 出入庫統(tǒng)計(jì)查詢 " & " Where " & crkcx_filter
Else
dflink_sql = "SELECT * FROM 出入庫統(tǒng)計(jì)查詢"
End If
?
crkcx_order = Me.數(shù)據(jù)表子窗體.Form.OrderBy
?
If crkcx_order <> "" Then
dflink_sql = dflink_sql & " order by " & crkcx_order
End If
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("導(dǎo)出出入庫統(tǒng)計(jì)查詢表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!貨品名稱.Value = !貨品名稱.Value
add_rs!規(guī)格型號(hào).Value = !規(guī)格型號(hào).Value
add_rs!貨品類別.Value = !貨品類別.Value
add_rs!單位.Value = !單位.Value
add_rs!入庫數(shù)量.Value = !入庫數(shù)量.Value
add_rs!入庫均價(jià).Value = !入庫均價(jià).Value
add_rs!入庫金額.Value = !入庫金額.Value
add_rs!出庫數(shù)量.Value = !出庫數(shù)量.Value
add_rs!出庫均價(jià).Value = !出庫均價(jià).Value
add_rs!出庫金額.Value = !出庫金額.Value
add_rs!成本均價(jià).Value = !成本均價(jià).Value
add_rs!成本金額.Value = !成本金額.Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
?
Call 導(dǎo)出查詢表結(jié)果("導(dǎo)出出入庫統(tǒng)計(jì)查詢表")
?
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
Private Sub Form_Load()
Dim rfdate As Date
Dim rldate As Date
Dim rftext As String
Dim rltext As String
?
Dim cfdate As Date
Dim cldate As Date
Dim cftext As String
Dim cltext As String
?
?
rftext = Nz(DMin("最早入庫日期", "入庫日期查詢"), "")
rltext = Nz(DMax("最后入庫日期", "入庫日期查詢"), "")
?
?
cftext = Nz(DMin("最早出庫日期", "出庫日期查詢"), "")
cltext = Nz(DMax("最后出庫日期", "出庫日期查詢"), "")
?
?
If (rftext <> "" And rltext <> "") Or (cftext <> "" And cltext <> "") Then
?
rfdate = CDate(rftext)
rldate = CDate(rltext)
?
cfdate = CDate(cftext)
cldate = CDate(cltext)
?
If rfdate < cfdate Then
Me.起始日期 = rfdate
Else
Me.起始日期 = cfdate
End If
?
?
If rldate > cldate Then
Me.截止日期 = rldate
Else
Me.截止日期 = cldate
End If
?
Else
Me.起始日期 = ""
Me.截止日期 = ""
End If
?
?
?
'---------------------------如果日期不為空
If Me.起始日期 <> "" And Me.截止日期 <> "" Then
Call 復(fù)制入庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 復(fù)制出庫單明細(xì)表(Me.起始日期, Me.截止日期)
Call 貨品出入庫匯總
?
?
Me.數(shù)據(jù)表子窗體.Requery
End If
?
?
End Sub
?
?
?
?
Sub 復(fù)制入庫單明細(xì)表(ByVal fd As Date, ByVal ld As Date)
?
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------------前期
?
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 前期入庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
dflink_sql = "SELECT * FROM 入庫單明細(xì)查詢 Where 出入庫日期 <#" & fd & "#"
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("前期入庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
'------------------------------------------------------期中
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 期中入庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
dflink_sql = "SELECT * FROM 入庫單明細(xì)查詢 Where 出入庫日期 >=#" & fd & "# and 出入庫日期 <=#" & ld & "#"
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
?
Set add_rs = CurrentDb.OpenRecordset("期中入庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------后期
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 后期入庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
?
dflink_sql = "SELECT * FROM 入庫單明細(xì)查詢 Where 出入庫日期 >#" & ld & "#"
?
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
?
Set add_rs = CurrentDb.OpenRecordset("后期入庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
?
?
?
Sub 復(fù)制出庫單明細(xì)表(ByVal fd As Date, ByVal ld As Date)
?
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------------前期
?
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 前期出庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
?
dflink_sql = "SELECT * FROM 出庫單明細(xì)查詢 Where 出入庫日期 <#" & fd & "#"
?
?
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("前期出庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
'------------------------------------------------------期中
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 期中出庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
?
?
dflink_sql = "SELECT * FROM 出庫單明細(xì)查詢 Where 出入庫日期 >=#" & fd & "# and 出入庫日期 <=#" & ld & "#"
?
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
?
Set add_rs = CurrentDb.OpenRecordset("期中出庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------后期
DoCmd.SetWarnings (False)
?
del_sql = "Delete From 后期出庫明細(xì)表"
DoCmd.RunSQL del_sql
?
?
?
?
dflink_sql = "SELECT * FROM 出庫單明細(xì)查詢 Where 出入庫日期 >#" & ld & "#"
?
?
?
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Set add_rs = CurrentDb.OpenRecordset("后期出庫明細(xì)表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!單價(jià).Value = !單價(jià).Value
add_rs!數(shù)量.Value = !數(shù)量.Value
add_rs!貨品金額.Value = !貨品金額.Value
add_rs!出入庫日期.Value = !出入庫日期.Value
add_rs!憑證單號(hào).Value = !憑證單號(hào).Value
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
'------------------------------------------------------
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
?
?
Sub 貨品出入庫匯總()
?
On Error GoTo 導(dǎo)出失敗
?
?
?
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 出入庫統(tǒng)計(jì)表"
DoCmd.RunSQL del_sql
?
?
?
?
Dim dflink_sql As String
dflink_sql = "SELECT * FROM 貨品表"
?
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("出入庫統(tǒng)計(jì)表", dbOpenTable)
?
'-------------------------
Dim 入庫數(shù)量num As Single
Dim 出庫數(shù)量num As Single
?
Dim 期初庫存num As Single
Dim 期初金額num As Currency
?
?
Dim 前期入庫數(shù)量num As Single
Dim 前期入庫金額num As Currency
?
Dim 期中入庫數(shù)量num As Single
Dim 期中入庫金額num As Currency
?
Dim 成本均價(jià)num As Currency
?
Do While .EOF = False
?
入庫數(shù)量num = Nz(DLookup("期中入庫數(shù)量", "期中入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
出庫數(shù)量num = Nz(DLookup("期中出庫數(shù)量", "期中出庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
期初庫存num = Nz(!期初庫存.Value, 0)
期初金額num = Nz(!期初金額.Value, 0)
?
前期入庫數(shù)量num = Nz(DLookup("前期入庫數(shù)量", "前期入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
前期入庫金額num = Nz(DLookup("前期入庫金額", "前期入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
?
期中入庫數(shù)量num = 入庫數(shù)量num
期中入庫金額num = Nz(DLookup("期中入庫金額", "期中入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
?
If 期中入庫數(shù)量num + 前期入庫數(shù)量num + 期初庫存num > 0 Then
??? 成本均價(jià)num = (期初金額num + 前期入庫金額num + 期中入庫金額num) / (期中入庫數(shù)量num + 前期入庫數(shù)量num + 期初庫存num)
Else
??? 成本均價(jià)num = 0???? '是0還是出庫均價(jià)
End If
?
If 入庫數(shù)量num <> 0 Or 出庫數(shù)量num <> 0 Then
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!入庫數(shù)量.Value = 入庫數(shù)量num
add_rs!入庫均價(jià).Value = Nz(DLookup("期中入庫均價(jià)", "期中入庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
add_rs!入庫金額.Value = 期中入庫金額num
add_rs!出庫數(shù)量.Value = 出庫數(shù)量num
add_rs!出庫均價(jià).Value = Nz(DLookup("期中出庫均價(jià)", "期中出庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
add_rs!出庫金額.Value = Nz(DLookup("期中出庫金額", "期中出庫統(tǒng)計(jì)查詢", "貨品編號(hào)='" & !貨品編號(hào).Value & "'"), 0)
add_rs!成本均價(jià).Value = 成本均價(jià)num
add_rs!成本金額.Value = 成本均價(jià)num * 出庫數(shù)量num
?
add_rs.Update
End If
?
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
?
'------------------------------------------------------
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
Private Sub 選擇貨品_Change()
If Me.選擇貨品 <> "" Then
Me.數(shù)據(jù)表子窗體.Form.Filter = "貨品編號(hào)='" & Me.選擇貨品 & "'"
Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
Else
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
End If
End Sub
?
?
供貨單位數(shù)據(jù)表
Private Sub Form_BeforeUpdate(Cancel As Integer)
If gx_num = 0 Then
Exit Sub
End If
?
On Error GoTo 數(shù)據(jù)更新前提醒_Err
?
??? If (MsgBox("是否保存對(duì)記錄的修改", 1, "修改記錄提醒") = 1) Then
??????? Beep
'??????? MsgBox "記錄修改成功", vbyesOnly, "提醒"
??? Else
??????? DoCmd.RunCommand acCmdUndo
??? End If
?
?
數(shù)據(jù)更新前提醒_Exit:
'??? Exit Function
??? Exit Sub
?
數(shù)據(jù)更新前提醒_Err:
??? MsgBox Error$
??? Resume 數(shù)據(jù)更新前提醒_Exit
End Sub
貨品查詢
Option Compare Database
Option Explicit
?
?
Private Sub Command查詢_Click()
On Error GoTo 結(jié)束查詢
Dim search_field As String
If Me.查詢字段 = "日期" Then
?
??? If 起始日期 <> "" And IsNull(起始日期) = False And 截止日期 <> "" And IsNull(截止日期) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
??????? search_field = Me.查詢字段
??????? hp_filter = search_field & " between #" & Me.起始日期 & "# and #" & Me.截止日期 & "#"
??????? Me.數(shù)據(jù)表子窗體.Form.Filter = hp_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
?
??? Else
??????? hp_filter = ""
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
?
??? End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
End If
If Me.查詢字段 = "入庫價(jià)" Or Me.查詢字段 = "出庫價(jià)" Or Me.查詢字段 = "期初庫存" Or Me.查詢字段 = "期初金額" Or Me.查詢字段 = "最低庫存" Or Me.查詢字段 = "最高庫存" Then
??? If 最小 <> "" And IsNull(最小) = False And 最大 <> "" And IsNull(最大) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
?
?
??????? search_field = Me.查詢字段
??????? hp_filter = search_field & " >= " & Me.最小 & " And " & search_field & " <= " & Me.最大
??????? Me.數(shù)據(jù)表子窗體.Form.Filter = hp_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
??? Else
??????? hp_filter = ""
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
??? End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
End If
?
If 查詢內(nèi)容 <> "" And IsNull(查詢內(nèi)容) = False And 查詢字段 <> "" And IsNull(查詢字段) = False Then
?
??? search_field = Me.查詢字段
??? hp_filter = search_field & " like '*" & Me.查詢內(nèi)容 & "*'"
??? Me.數(shù)據(jù)表子窗體.Form.Filter = hp_filter
??????? Me.數(shù)據(jù)表子窗體.Form.FilterOn = True
??????? Me.數(shù)據(jù)表子窗體.Requery
?
Else
??? hp_filter = ""
??? Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
??????? Me.數(shù)據(jù)表子窗體.Requery
End If
??? Me.數(shù)據(jù)表子窗體.SetFocus
??? Exit Sub
結(jié)束查詢:
??? MsgBox Err.Description
End Sub
?
?
?
Private Sub Command全部_Click()
hp_filter = ""
Me.數(shù)據(jù)表子窗體.Form.FilterOn = False
Me.數(shù)據(jù)表子窗體.Requery
End Sub
?
?
?
Private Sub Command數(shù)據(jù)導(dǎo)出_Click()
On Error GoTo 導(dǎo)出失敗
'------------------------------------------------清空表數(shù)據(jù)
DoCmd.SetWarnings (False)
Dim del_sql As String
del_sql = "Delete From 導(dǎo)出貨品表"
DoCmd.RunSQL del_sql
?
?
?
Dim dflink_sql As String
?
hp_filter = Me.數(shù)據(jù)表子窗體.Form.Filter
?
If hp_filter <> "" And Me.數(shù)據(jù)表子窗體.Form.FilterOn = True Then
dflink_sql = "SELECT * FROM 貨品表 " & " Where " & hp_filter
Else
dflink_sql = "SELECT * FROM 貨品表"
End If
?
hp_order = Me.數(shù)據(jù)表子窗體.Form.OrderBy
?
If hp_order <> "" Then
dflink_sql = dflink_sql & " order by " & hp_order
End If
?
Dim dflink_rs As DAO.Recordset
Set dflink_rs = CurrentDb.OpenRecordset(dflink_sql, dbOpenDynaset)
?
With dflink_rs
?
?
Dim add_rs As DAO.Recordset
Set add_rs = CurrentDb.OpenRecordset("導(dǎo)出貨品表", dbOpenTable)
Do While .EOF = False
?
add_rs.AddNew
?
add_rs!貨品編號(hào).Value = !貨品編號(hào).Value
add_rs!貨品名稱.Value = !貨品名稱.Value
add_rs!規(guī)格型號(hào).Value = !規(guī)格型號(hào).Value
add_rs!單位.Value = !單位.Value
add_rs!入庫價(jià).Value = !入庫價(jià).Value
add_rs!出庫價(jià).Value = !出庫價(jià).Value
add_rs!貨品類別.Value = !貨品類別.Value
add_rs!期初庫存.Value = !期初庫存.Value
add_rs!期初金額.Value = !期初金額.Value
add_rs!最低庫存.Value = !最低庫存.Value
add_rs!最高庫存.Value = !最高庫存.Value
add_rs!備注.Value = !備注.Value
?
?
?
add_rs.Update
.MoveNext
Loop
End With
?
add_rs.Close
Set add_rs = Nothing
?
dflink_rs.Close
Set dflink_rs = Nothing
?
?
Call 導(dǎo)出查詢表結(jié)果("導(dǎo)出貨品表")
?
Exit Sub
導(dǎo)出失敗:
MsgBox Err.Description
End Sub
?
Private Sub Command添加_Click()
DoCmd.OpenForm "貨品添加", acNormal
End Sub
?
Private Sub Form_Load()
DoCmd.Restore
?
hp_type = 0
hp_filter = ""
hp_order = "貨品編號(hào) DESC"
?
?
Me.數(shù)據(jù)表子窗體.Form.OrderBy = hp_order
Me.數(shù)據(jù)表子窗體.Form.OrderByOn = True
?
?
Me.查詢內(nèi)容.Visible = True
'-----------------------------隱藏日期控件
Me.起始日期.Visible = False
Me.截止日期.Visible = False
'-----------------------------隱藏金額控件
Me.最小.Visible = False
Me.最大.Visible = False
?
End Sub
Private Sub 查詢字段_Change()
If Me.查詢字段 = "日期" Then
Me.起始日期.Visible = True
Me.截止日期.Visible = True
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
If Me.查詢字段 = "入庫價(jià)" Or Me.查詢字段 = "出庫價(jià)" Or Me.查詢字段 = "期初庫存" Or Me.查詢字段 = "期初金額" Or Me.查詢字段 = "最低庫存" Or Me.查詢字段 = "最高庫存" Then
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = True
Me.最大.Visible = True
Me.查詢內(nèi)容.Visible = False
Exit Sub
Else
Me.起始日期.Visible = False
Me.截止日期.Visible = False
Me.最小.Visible = False
Me.最大.Visible = False
Me.查詢內(nèi)容.Visible = True
End If
End Sub
