公司信息.frm

引用枕善居的天宏钢构仓库管理系统 Ver 2.96

'e-mail  ：[e-mail]
'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