'e-mail  ：Mndsoft@126.com
'OICQ    ：88382850
'          如果您有新的好的代碼別忘記給枕善居哦!
'****************************************************************************

'Private 供應商編號 As String
Private 客戶編號 As String
Private 倉庫編號 As String
Private 經辦人編號 As String
Private bkcolor As Long

Option Explicit

Private Sub ChangeBackColor(bkcolor As Long)

    Me.BackColor = bkcolor
    保存.BackColor = bkcolor
    打印.BackColor = bkcolor
    更改背景.BackColor = bkcolor

       Text1.BackColor = bkcolor

    編號.BackColor = bkcolor
    貨物編號.BackColor = bkcolor
    年.BackColor = bkcolor
    月.BackColor = bkcolor
    日.BackColor = bkcolor
    貨名規格.BackColor = bkcolor
    單價.BackColor = bkcolor
    單位.BackColor = bkcolor
    數量.BackColor = bkcolor
    金額.BackColor = bkcolor
    其它金額.BackColor = bkcolor
    供應商.BackColor = bkcolor
    存放倉庫.BackColor = bkcolor
    備注.BackColor = bkcolor
    經辦人.BackColor = bkcolor
    Text1.BackColor = bkcolor
End Sub

Private Sub lockcontrol()
    
    編號.Locked = True
    年.Locked = True: 月.Locked = True: 日.Locked = True
    貨物編號.Locked = True
    貨名規格.Locked = True
    單價.Locked = True
    單位.Locked = True
    數量.Locked = True
    金額.Locked = True
    其它金額.Locked = True
   ' 供應商.Locked = True
    存放倉庫.Locked = True
    備注.Locked = True
    經辦人.Locked = True
    
End Sub

Private Sub Form_Resize()
        
    On Error Resume Next
    
    '新增狀態
    If 編號 = "" Then
        '初始化年月日
        年.Text = Year(Date)
        月.Text = Month(Date)
        日.Text = Day(Date)
        
        '初始化編號
        fMainForm.m_checkado.RecordSource = "select 編號 from 入庫單"
        fMainForm.m_checkado.Refresh
        If fMainForm.m_checkado.Recordset.RecordCount > 0 Then
            fMainForm.m_checkado.Recordset.MoveLast
            編號.Text = fMainForm.m_checkado.Recordset.Fields("編號").Value + 1
        Else
            編號.Text = 1
        End If
    Else '打印狀態
        fMainForm.m_checkado.RecordSource = "select 貨物信息.編號,貨物信息.貨物名稱,貨物信息.貨物規格,職員信息.姓名 as 經辦人,入庫單.入庫時間,入庫單.入庫單價,貨物信息.計量單位,入庫單.入庫數量,(入庫單.入庫單價*入庫單.入庫數量) as 金額,入庫單.其它金額,供應商.供應商名稱 as 供應商,倉庫.倉庫名稱 as 存放倉庫,入庫單.備注 from 入庫單,貨物信息,職員信息,供應商,倉庫 where 貨物信息.編號=入庫單.貨物編號 and 職員信息.編號=入庫單.經辦人編號 and 供應商.編號=入庫單.供應商編號 and 倉庫.編號=入庫單.倉庫編號 and 入庫單.編號=" + 編號
        fMainForm.m_checkado.Refresh
        貨物編號.Text = fMainForm.m_checkado.Recordset.Fields("編號").Value
        貨名規格.Text = fMainForm.m_checkado.Recordset.Fields("貨物名稱").Value
        單價.Text = fMainForm.m_checkado.Recordset.Fields("入庫單價").Value
        單位.Text = fMainForm.m_checkado.Recordset.Fields("計量單位").Value
        數量.Text = fMainForm.m_checkado.Recordset.Fields("入庫數量").Value
        金額.Text = fMainForm.m_checkado.Recordset.Fields("金額").Value
        其它金額.Text = fMainForm.m_checkado.Recordset.Fields("其它金額").Value
        供應商.Text = fMainForm.m_checkado.Recordset.Fields("供應商").Value
        存放倉庫.Text = fMainForm.m_checkado.Recordset.Fields("存放倉庫").Value
        備注.Text = fMainForm.m_checkado.Recordset.Fields("備注").Value
        經辦人.Text = fMainForm.m_checkado.Recordset.Fields("經辦人").Value
        
        Dim t As Date
        t = fMainForm.m_checkado.Recordset.Fields("入庫時間").Value
        年.Text = Year(t)
        月.Text = Month(t)
        日.Text = Day(t)
        
        '鎖住控件
        lockcontrol
        
        打印.Visible = True
        保存.Visible = False
        更改背景.Visible = True
        
    End If
End Sub



Private Sub 保存_Click()

If 年.Text = "" Or 月.Text = "" Or 日.Text = "" Then MsgBox "請填寫日期!", vbQuestion: Exit Sub
    If 貨物編號.Text = "" Then MsgBox "請填寫貨物信息!", vbQuestion: Exit Sub
    If 單價.Text = "" Then MsgBox "請填寫入庫單價!", vbQuestion: Exit Sub
    If 數量.Text = "" Then MsgBox "請填數量!", vbQuestion: Exit Sub
    If 供應商.Text = "" Then MsgBox "請填寫工程名稱!", vbQuestion: Exit Sub
    If 存放倉庫.Text = "" Then MsgBox "請填寫存放倉庫!", vbQuestion: Exit Sub
    If 經辦人.Text = "" Then MsgBox "請填寫經辦人!", vbQuestion: Exit Sub
    On Error Resume Next
    '檢測有沒有超過庫存限量
    Dim max, use, left As String
    
    '得到最高限量max
    fMainForm.m_checkado.RecordSource = "select 最高限量 from 貨物信息 where 編號=" + 貨物編號.Text
    fMainForm.m_checkado.Refresh
    max = fMainForm.m_checkado.Recordset.Fields(0).Value
    '得到當前倉庫庫存量use
    fMainForm.m_checkado.RecordSource = "select 庫存數量 from 貨物信息,庫存狀況 where 貨物編號=" + 貨物編號 + " and 倉庫編號=" + 倉庫編號
    fMainForm.m_checkado.Refresh
    
    use = 0
    On Error GoTo 1:
    use = fMainForm.m_checkado.Recordset.Fields(0).Value
1:

    On Error GoTo quit
    left = max - use
    
    '如果超過最高限量
    Dim a, b As Long
    a = 數量.Text: b = left

    If a > b Then
        MsgBox "入庫失敗,不能入庫" + 數量.Text + ",剩余限量為" + Str(left)
        Exit Sub
    End If
    '庫存量足夠
    '開始寫入新的入庫單到數據庫
    On Error Resume Next
    fMainForm.m_checkado.RecordSource = "select 編號,貨物編號,經辦人編號,入庫時間,入庫單價,入庫數量,供應商編號,倉庫編號,定單狀況,其它金額,備注 from 入庫單"
    fMainForm.m_checkado.Refresh
    
    
    fMainForm.m_checkado.Recordset.AddNew
    fMainForm.m_checkado.Recordset.Fields("編號") = 編號.Text
    fMainForm.m_checkado.Recordset.Fields("貨物編號") = 貨物編號.Text
    'fMainForm.m_checkado.Recordset.Fields("經辦人編號") = 經辦人編號
    fMainForm.m_checkado.Recordset.Fields("入庫時間") = 年.Text + "-" + 月.Text + "-" + 日.Text
    fMainForm.m_checkado.Recordset.Fields("入庫單價") = 單價.Text
    fMainForm.m_checkado.Recordset.Fields("入庫數量") = 數量.Text
    'fMainForm.m_checkado.Recordset.Fields("供應商編號") = 供應商編號
    fMainForm.m_checkado.Recordset.Fields("倉庫編號") = 倉庫編號
    fMainForm.m_checkado.Recordset.Fields("定單狀況") = "已處理"
    fMainForm.m_checkado.Recordset.Fields("其它金額") = 其它金額
    fMainForm.m_checkado.Recordset.Fields("備注") = 備注.Text

    fMainForm.m_checkado.Recordset.update
    fMainForm.m_checkado.Refresh
    
    '更新[庫存狀況]
    On Error Resume Next
    fMainForm.m_checkado.RecordSource = "select 編號,貨物編號,庫存數量,倉庫編號 from 庫存狀況 where 貨物編號=" + 貨物編號.Text + " and 倉庫編號=" + Str(倉庫編號)
    fMainForm.m_checkado.Refresh

    '如果已有記錄
    If fMainForm.m_checkado.Recordset.RecordCount > 0 Then
        fMainForm.m_checkado.Recordset.Fields("庫存數量").Value = fMainForm.m_checkado.Recordset.Fields("庫存數量").Value + 數量.Text
        fMainForm.m_checkado.Recordset.update
        fMainForm.m_checkado.Refresh
    Else
        '獲取新的庫存狀況的編號 ncode
        fMainForm.m_checkado.RecordSource = "select * from 庫存狀況"
        fMainForm.m_checkado.Refresh
        
        Dim ncode As String
        
        If fMainForm.m_checkado.Recordset.RecordCount > 0 Then
            '移到最后記錄
            fMainForm.m_checkado.Recordset.MoveLast
            ncode = fMainForm.m_checkado.Recordset.Fields("編號").Value + 1
        Else
            ncode = 1
        End If
        
        fMainForm.m_checkado.Recordset.AddNew
        fMainForm.m_checkado.Recordset.Fields("編號") = ncode
        fMainForm.m_checkado.Recordset.Fields("貨物編號") = 貨物編號.Text
        fMainForm.m_checkado.Recordset.Fields("庫存數量") = 數量.Text
        fMainForm.m_checkado.Recordset.Fields("倉庫編號") = 倉庫編號
        
        fMainForm.m_checkado.Recordset.update
        fMainForm.m_checkado.Refresh
    End If
    
    
    '開始寫入新的退庫單到數據庫
    '說明借用公司信息表為退庫單表
   'select 編號,公司名稱 as 貨物編號,地址 as 經辦人編號,城市 as 入庫時間,國家 as 入庫單價,電話號碼 as 入庫數量,電子郵件 as 工程名稱,傳真號碼 as 倉庫編號,開戶銀行 as 定單狀況,銀行帳號 as 其它金額,業務描述 as 備注 from 公司信息"
    
    On Error Resume Next
    fMainForm.m_checkado.RecordSource = "select 編號,公司名稱 ,地址 ,城市 ,省份,郵政編碼,國家 ,電話號碼 ,電子郵件,傳真號碼 ,開戶銀行 ,銀行帳號 ,業務描述 from 公司信息"
    fMainForm.m_checkado.Refresh
   
   
    fMainForm.m_checkado.Recordset.AddNew
    fMainForm.m_checkado.Recordset.Fields("編號") = 編號.Text      '編號
    fMainForm.m_checkado.Recordset.Fields("公司名稱") = 貨物編號.Text    '貨物編號
    fMainForm.m_checkado.Recordset.Fields("地址") = 貨名規格.Text       '貨物規格
    fMainForm.m_checkado.Recordset.Fields("城市") = 年.Text + "-" + 月.Text + "-" + 日.Text  '入庫時間
    fMainForm.m_checkado.Recordset.Fields("省份") = 經辦人.Text  '經辦人
    fMainForm.m_checkado.Recordset.Fields("郵政編碼") = 單位.Text  '單位
    fMainForm.m_checkado.Recordset.Fields("國家") = 單價.Text   '入庫單價
    fMainForm.m_checkado.Recordset.Fields("電話號碼") = 數量.Text  '入庫數量
    fMainForm.m_checkado.Recordset.Fields("電子郵件") = 供應商.Text   '工程名稱
    fMainForm.m_checkado.Recordset.Fields("傳真號碼") = 存放倉庫.Text   '倉庫名稱
    fMainForm.m_checkado.Recordset.Fields("開戶銀行") = Text1.Text  '部門
    fMainForm.m_checkado.Recordset.Fields("銀行帳號") = 其它金額.Text '其它金額
    fMainForm.m_checkado.Recordset.Fields("業務描述") = 備注.Text     '備注

    fMainForm.m_checkado.Recordset.update
    fMainForm.m_checkado.Refresh
    
    
    
    保存.Visible = False
    更改背景.Visible = True
    打印.Visible = True
    
    '鎖住控件
    lockcontrol
    
    MsgBox "新增退庫單成功!"
     '寫入系統日志
    fMainForm.WriteLog ("新增入庫單")
    GoTo quit2
quit:
    MsgBox "新增退庫單失敗!"
quit2:
        
End Sub



Private Sub 存放倉庫_Click()
    
    If 存放倉庫.Locked Then Exit Sub
    On Error Resume Next
    Dim sel As New 數據選擇
    sel.Adodc1.ConnectionString = DataConnectString
    
    If 貨物編號.Text <> "" Then
        sel.Adodc1.RecordSource = "select 倉庫.編號,倉庫.倉庫名稱,貨物信息.最高限量,sum(select 庫存數量 from 庫存狀況 where 倉庫編號=倉庫.編號 and 貨物編號=" + 貨物編號.Text + ") as 庫存量,(貨物信息.最高限量-庫存量) as 剩余限量 from 倉庫,貨物信息 where 貨物信息.編號=" + 貨物編號.Text
    Else
        sel.Adodc1.RecordSource = "select 編號,倉庫名稱 from 倉庫"
    End If
    
    sel.title = "請選擇存放倉庫"
    
    sel.Show vbModal
    
    If sel.result1 <> "" Then 倉庫編號 = sel.result1
    If sel.result2 <> "" Then 存放倉庫.Text = sel.result2
    
    Unload sel
    
End Sub

Private Sub 存放倉庫_KeyPress(KeyAscii As Integer)
    KeyAscii = 0
End Sub

Private Sub 打印_Click()
    On Error GoTo ms1
    Me.Height = Me.Height - 600
    PrintForm
    Me.Height = Me.Height + 600
     '寫入系統日志
    fMainForm.WriteLog ("打印入庫單")
ms1:
        MsgBox "打印機設置錯誤！", vbExclamation, "打印機設置錯誤"
End Sub

Private Sub 更改背景_Click()

    On Error Resume Next
    With CommonDialog1
        .DialogTitle = "頁面設置"
        .CancelError = True
        .ShowColor
    End With
    
    ChangeBackColor (CommonDialog1.Color)
    
End Sub

Private Sub 供應商_Click()

'供應商變為工程名稱
    If 供應商.Locked Then Exit Sub
    On Error Resume Next
    Dim sel As New 數據選擇
    sel.Adodc1.ConnectionString = DataConnectString
    sel.Adodc1.RecordSource = "select 編號,備注 as 工程名稱 from 出庫單 where 備注 like  '%工程%' "
    sel.title = "請選擇工程名稱"
    
    sel.Show vbModal
    
    'If sel.result1 <> "" Then 編號 = sel.result1
    If sel.result2 <> "" Then 供應商.Text = sel.result2
    
    Unload sel
    
End Sub

Private Sub 供應商_KeyPress(KeyAscii As Integer)
    KeyAscii = 0
End Sub

Private Sub 貨物編號_Click()
    
    If 貨物編號.Locked Then Exit Sub
    On Error Resume Next
    Dim sel As New 數據選擇
    sel.Adodc1.ConnectionString = DataConnectString
    sel.Adodc1.RecordSource = "select 編號,貨物名稱,貨物類別,貨物規格 from 貨物信息"
    sel.title = "請選擇入庫貨物"
    
    sel.Show vbModal
    
    If sel.result1 <> "" Then
        貨物編號.Text = sel.result1
    Else
        Exit Sub
        Unload sel
    End If
    
    Unload sel
    
    '更新貨名規格與計量單位
    fMainForm.m_checkado.RecordSource = "select 貨物名稱,貨物規格,計量單位 from 貨物信息 where 編號=" + 貨物編號.Text
    fMainForm.m_checkado.Refresh
    
    貨名規格.Text = fMainForm.m_checkado.Recordset.Fields("貨物名稱").Value
    On Error GoTo cont
    Dim s As String
    s = fMainForm.m_checkado.Recordset.Fields("貨物規格").Value
    If Len(s) > 0 Then 貨名規格.Text = 貨名規格.Text + "(" + s + ")"
cont:
    On Error Resume Next
    單位.Text = fMainForm.m_checkado.Recordset.Fields("計量單位").Value
     
   

End Sub

Private Sub 貨物編號_KeyPress(KeyAscii As Integer)
    KeyAscii = 0
End Sub

Private Sub 金額_Click()

    If 金額.Locked Then Exit Sub
    On Error Resume Next
    金額.Text = 單價.Text * 數量.Text
End Sub

Private Sub 經辦人_Click()

    
    If 經辦人.Locked Then Exit Sub
    On Error Resume Next
    Dim sel As New 數據選擇
    sel.Adodc1.ConnectionString = DataConnectString
    sel.Adodc1.RecordSource = "select 編號,聯系人,客戶名稱 as 部門名稱,電話號碼,手機,傳真號碼 as 職務,備注 from 客戶"
    sel.title = "請選擇經辦人"
    
    sel.Show vbModal
    If sel.result3 <> "" Then Text1.Text = sel.result3
   ' If sel.result1 <> "" Then 經辦人編號 = sel.result1
    If sel.result2 <> "" Then 經辦人.Text = sel.result2
    
    Unload sel
    
End Sub

Private Sub 經辦人_KeyPress(KeyAscii As Integer)
    KeyAscii = 0
End Sub


Private Sub Form_Load()
     bkcolor = RGB(128, 255, 255)

On Error GoTo quit
    Adodc1.ConnectionString = DataConnectString
    
    Set 公司名稱.DataSource = Adodc1
    Set 地址.DataSource = Adodc1
    Set 城市.DataSource = Adodc1
    Set 省份.DataSource = Adodc1
    Set 國家.DataSource = Adodc1
    Set 郵政編碼.DataSource = Adodc1
    Set 電話號碼.DataSource = Adodc1
    Set 電子郵件.DataSource = Adodc1
    Set 傳真號碼.DataSource = Adodc1
    Set 開戶銀行.DataSource = Adodc1
    Set 銀行帳號.DataSource = Adodc1
    Set 業務描述.DataSource = Adodc1
    
    
    Adodc1.Refresh
    Adodc1.Visible = False
quit:
End Sub

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next

    If Adodc1.Recordset.RecordCount > 0 Then Adodc1.Recordset.update
    
    '寫入系統日志
    fMainForm.WriteLog ("查看或修改了退庫單")

End Sub