form1.frm

仓库管理系统,使用access数据库

Private Sub cmd_upd_Click()
user_upd.Show            '打开“修改用户”对话框
End Sub

Private Sub cmd_user_add_cancel_Click()
    txt_user_name.Text = ""
    txt_user_passwd.Text = ""
    txt_user_passwd2.Text = ""
End Sub

Private Sub cmd_user_add_OK_Click()
'新建用户入库
If Me.txt_user_name <> "" And Me.txt_user_passwd <> "" And Me.txt_user_passwd2 <> "" Then
    If Me.txt_user_passwd = Me.txt_user_passwd2 Then
        Sql = "insert into [user] values('" & Trim(txt_user_name.Text)
        Sql = Sql & "','" & Trim(txt_user_passwd.Text) & "')"
        dbOperate Sql
        txt_user_name.Text = ""
        txt_user_passwd.Text = ""
        txt_user_passwd2.Text = ""
    Else
        MsgBox "密码确认不成功，请重新输入密码！"
        txt_user_passwd.Text = ""
        txt_user_passwd2.Text = ""
    End If
Else
    MsgBox "请将信息填写完整！"
End If
End Sub



Private Sub cmd_user_cancel_Click()
Unload Me
End Sub

Private Sub cmd_user_OK_Click()
'普通用户修改密码入库
If Me.txt_name.Text <> "" And Me.txt_old_passwd <> "" And Me.txt_new_passwd <> "" And Me.txt_new_passwd2 <> "" Then
    Sql = "select * from [user] where u_name='" & txt_name.Text & "'"
    Set RS = dbSelect(Sql)
    If Not RS.EOF Then
        If RS.Fields("u_passwd") = Me.txt_old_passwd Then
            If Me.txt_new_passwd = Me.txt_new_passwd2 Then
                Sql = "update [user] set u_passwd='" & Me.txt_new_passwd & "'"
                Sql = Sql & "where u_name='" & Me.txt_name & "'"
                dbOperate Sql
            Else
                MsgBox "密码确认不成功，请重新输入新密码！"
                Me.txt_new_passwd = ""
                Me.txt_new_passwd2 = ""
            End If
        Else
            MsgBox "密码不正确，请重新输入！"
            Me.txt_old_passwd = ""
            Me.txt_new_passwd = ""
            Me.txt_new_passwd2 = ""
        End If
    Else
        MsgBox "无此用户,请重新输入！"
        Me.txt_name = ""
        Me.txt_old_passwd = ""
        Me.txt_new_passwd = ""
        Me.txt_new_passwd2 = ""
    End If
Else
    MsgBox "请将信息输入完整！"
End If
End Sub

Private Sub cmd_view_del_Click()
'从库存中删除货物信息
If Trim(Me.cmb_view_del.Text) <> "" Then
    Sql = "delete from [库存] where 货物名称='" & Trim(Me.cmb_view_del.Text) & "'"
    dbOperate Sql
Else
    MsgBox "请选择要删除的货物名称！"
End If
End Sub

Private Sub cmd_view_io_Click()
'显示出入仓库货物信息
If cmb_io.Text <> "" Then
    Sql = "select * from [进出库] "
    If cmb_sea_io.Text <> "" Then
        If cmb_sea_io.Text = "数量" Or cmb_sea_io.Text = "价格" Or cmb_sea_io.Text = "时间" Then
            Sql = Sql & " where " & Me.cmb_sea_io & "=" & Val(Trim(Me.txt_sea_io)) & ""
        Else
            Sql = Sql & " where " & Me.cmb_sea_io & "='" & Trim(Me.txt_sea_io) & "'"
        End If
    End If
    If cmb_io.Text = "按数量从少到多排序" Then
        Sql = Sql & " order by 数量 asc"
    Else
        If cmb_io.Text = "按数量从多到少排序" Then
            Sql = Sql & " order by 数量 desc"
        Else
            If cmb_io.Text = "按进价从低到高排列" Then
                Sql = Sql & " order by 价格 asc"
            Else
                If cmb_io.Text = "按售价从高到低排序" Then
                    Sql = Sql & " order by 价格 desc"
                Else
                    If cmb_io.Text = "按时间最近排序" Then
                        Sql = Sql & " order by 时间 desc"
                    End If
                End If
            End If
        End If
    End If

Dim line
line = 0
Set RS = dbSelect(Sql)
msf_io_store.Clear
Do While Not RS.EOF
    line = line + 1
    name1 = RS.Fields(0)
    io = RS.Fields(1)
    num = RS.Fields(2)
    unit1 = RS.Fields(3)
    price = RS.Fields(4)
    time1 = RS.Fields(5)
    buyyer = RS.Fields(6)
    handder = RS.Fields(7)
With Me.msf_io_store
   .Cols = 8
   .TextMatrix(0, 0) = "货物名称"
   .TextMatrix(0, 1) = "进出库"
   .TextMatrix(0, 2) = "数量"
   .TextMatrix(0, 3) = "单位"
   .TextMatrix(0, 4) = "价格"
   .TextMatrix(0, 5) = "时间"
   .TextMatrix(0, 6) = "供货商或顾客"
   .TextMatrix(0, 7) = "经手人"
   .TextMatrix(line, 0) = name1
   .TextMatrix(line, 1) = io
   .TextMatrix(line, 2) = num
   .TextMatrix(line, 3) = unit1
   .TextMatrix(line, 4) = price
   .TextMatrix(line, 5) = time1
   .TextMatrix(line, 6) = buyyer
   .TextMatrix(line, 7) = handder
   .FixedRows = 1
End With
    RS.MoveNext
    Loop
Else
    MsgBox "请选择查看方式！"
End If
End Sub

Private Sub cmd_view_upd_Click()
'打开修改库存信息对话框，并付初值
If cmb_view_del <> "" Then
    view_upd.Show
    Sql = "select * from [库存] where 货物名称='" & Trim(Me.cmb_view_del.Text) & "'"
    Set RS = dbSelect(Sql)
    view_upd.txt_name = RS.Fields(0)
    view_upd.txt_num = RS.Fields(1)
    view_upd.cmb_unit = RS.Fields(2)
    view_upd.txt_iprice = RS.Fields(3)
    view_upd.txt_oprice = RS.Fields(4)
    view_upd.txt_min = RS.Fields(5)
Else
    MsgBox "请选择货物名称！"
End If
End Sub

Private Sub cmd_view_view_Click()
'显示仓库货物信息
If cmb_view.Text <> "" Then
    Sql = "select * from [库存] "
    If cmb_sea_view.Text <> "" Then
        If cmb_sea_view.Text = "货物名称" Or cmb_sea_view.Text = "单位" Then
            Sql = Sql & " where " & Me.cmb_sea_view & "='" & Trim(Me.txt_sea_view) & "'"
        Else
            Sql = Sql & " where " & Me.cmb_sea_view & "=" & Val(Trim(Me.txt_sea_view)) & ""
        End If
    End If
    If cmb_view.Text = "按库存从少到多排序" Then
        Sql = Sql & " order by 库存 asc"
    Else
        If cmb_view.Text = "按库存从多到少排序" Then
            Sql = Sql & " order by 库存 desc"
        End If
    End If

Dim line
line = 0
Set RS = dbSelect(Sql)
msf_view_store.Clear
Do While Not RS.EOF
    line = line + 1
    name1 = RS.Fields(0)
    num = RS.Fields(1)
    unit1 = RS.Fields(2)
    iprice = RS.Fields(3)
    oprice = RS.Fields(4)
    min = RS.Fields(5)
With msf_view_store
   .Cols = 6
   .TextMatrix(0, 0) = "货物名称"
   .TextMatrix(0, 1) = "库存"
   .TextMatrix(0, 2) = "单位"
   .TextMatrix(0, 3) = "进价"
   .TextMatrix(0, 4) = "售价"
   .TextMatrix(0, 5) = "库存报警值"
   .TextMatrix(line, 0) = name1
   .TextMatrix(line, 1) = num
   .TextMatrix(line, 2) = unit1
   .TextMatrix(line, 3) = iprice
   .TextMatrix(line, 4) = oprice
   .TextMatrix(line, 5) = min
   .FixedRows = 1
End With
    RS.MoveNext
    Loop
Else
    MsgBox "请选择查看方式！"
End If
End Sub

Private Sub damage_Click()
pic_dam.Visible = True
pic_store.Visible = False
pic_user_add.Visible = False
pic_user_manage.Visible = False
pic_passwd_upd.Visible = False
pic_view_store.Visible = False
cmb_dam_state.AddItem "报损"
cmb_dam_state.AddItem "已修复"

End Sub

Private Sub exit_Click()
Unload Me

End Sub

Private Sub Form_Load()
Timer1.Interval = 1000
pic_store.Visible = False
pic_user_add.Visible = False
pic_user_manage.Visible = False
pic_passwd_upd.Visible = False
pic_dam.Visible = False
pic_view_store.Visible = False
'窗口载入，为各下拉框付初值
Sql = "select unit from [unit]"
    Set RS = dbSelect(Sql)
    While Not RS.EOF
        cmb_dam_unit.AddItem RS.Fields(0)
        RS.MoveNext
    Wend
        Set RS = Nothing
Combo1.AddItem "进库"
Combo1.AddItem "出库"
Sql = "select unit from [unit]"
    Set RS = dbSelect(Sql)
    While Not RS.EOF
        cmb_unit.AddItem RS.Fields(0)
        RS.MoveNext
    Wend
        Set RS = Nothing
cmb_view.AddItem "按库存从少到多排序"
cmb_view.AddItem "按库存从多到少排序"
cmb_view.AddItem "全部列出"
Sql = "select 货物名称 from [库存]"
    Set RS = dbSelect(Sql)
    While Not RS.EOF
        cmb_view_del.AddItem RS.Fields(0)
        RS.MoveNext
    Wend
        Set RS = Nothing
cmb_sea_view.AddItem "货物名称"
cmb_sea_view.AddItem "库存"
cmb_sea_view.AddItem "单位"
cmb_sea_view.AddItem "进价"
cmb_sea_view.AddItem "售价"
cmb_sea_view.AddItem "报警值"
cmb_sea_io.AddItem "货物名称"
cmb_sea_io.AddItem "进出库"
cmb_sea_io.AddItem "数量"
cmb_sea_io.AddItem "单位"
cmb_sea_io.AddItem "价格"
cmb_sea_io.AddItem "时间"
cmb_sea_io.AddItem "供货商或顾客"
cmb_sea_io.AddItem "经手人"
cmb_io.AddItem "按数量从少到多排序"
cmb_io.AddItem "按数量从多到少排序"
cmb_io.AddItem "按进价从低到高排列"
cmb_io.AddItem "按售价从高到低排序"
cmb_io.AddItem "按时间先后排序"
cmb_io.AddItem "全部列出"
End Sub



Private Sub s_cancel_Click()
txt_is_name.Text = ""
txt_is_price.Text = ""
txt_is_num.Text = ""
txt_is_supplier.Text = ""
txt_is_hand.Text = ""
End Sub

Private Sub s_ok_Click()
'货物入库
If txt_is_name.Text <> "" And txt_is_num.Text <> "" And txt_is_price.Text <> "" And txt_is_supplier.Text <> "" And txt_is_hand.Text <> "" And Trim(cmb_unit.Text) <> "" And Trim(Combo1.Text) <> "" Then
    If Trim(Combo1.Text) = "进库" Then
        Sql = "select * from [库存] where 货物名称='" & Trim(txt_is_name.Text) & "'"
        Set RS = dbSelect(Sql)
        
        If RS.EOF Then
            alerm.Show
        Else
            If Me.cmb_unit = RS.Fields("单位") Then
            num = RS.Fields("库存")
            name1 = RS.Fields("货物名称")
            Sql = "update [库存] set 库存='" & Val(Trim(txt_is_num.Text)) + Val(num) & "',进价='" & Val(Trim(txt_is_price.Text)) & "'"
            Sql = Sql & "where 货物名称='" & name1 & "'"
            dbOperate Sql
            Sql = "insert into [进出库] values('" & Trim(txt_is_name.Text)
            Sql = Sql & "','" & Trim(Combo1.Text)
            Sql = Sql & "'," & Str(Val(Trim(txt_is_num.Text)))
            Sql = Sql & ",'" & Trim(cmb_unit.Text)
            Sql = Sql & "'," & Str(Val(Trim(txt_is_price.Text)))
            Sql = Sql & ",'" & Trim(dtp_is_time.Value)
            Sql = Sql & "','" & txt_is_supplier.Text
            Sql = Sql & "','" & txt_is_hand.Text & "')"
            dbOperate Sql
            Else
            MsgBox "请使用统一的单位！"
            End If
        End If
    Else
        Sql = "select * from [库存] where 货物名称='" & Trim(txt_is_name.Text) & "'"
        Set RS = dbSelect(Sql)
        If RS.EOF Then
            MsgBox "仓库中无此货物！"
        Else
            If Me.cmb_unit = RS.Fields("单位") Then
                num = RS.Fields("库存")
                min = RS.Fields("报警值")
                If Val(num) - Val(Trim(txt_is_num.Text)) >= 0 Then
                    If Val(num) - Val(Trim(txt_is_num.Text)) < Val(min) Then
                        MsgBox "库存不足！请尽快购买此货物！"
                    End If
                    Sql = "update [库存] set 库存='" & Val(num) - Val(Trim(txt_is_num.Text)) & "',售价='" & Val(Trim(txt_is_price.Text)) & "'"
                    Sql = Sql & "where 货物名称='" & txt_is_name.Text & "'"
                    dbOperate Sql
                    Sql = "insert into [进出库] values('" & Trim(txt_is_name.Text)
                    Sql = Sql & "','" & Trim(Combo1.Text)
                    Sql = Sql & "'," & Str(Val(Trim(txt_is_num.Text)))
                    Sql = Sql & ",'" & Trim(cmb_unit.Text)
                    Sql = Sql & "'," & Str(Val(Trim(txt_is_price.Text)))
                    Sql = Sql & ",'" & Trim(dtp_is_time.Value)
                    Sql = Sql & "','" & txt_is_supplier.Text
                    Sql = Sql & "','" & txt_is_hand.Text & "')"
                    dbOperate Sql
                Else
                    MsgBox "库存不足提供此次出货数目！"
                End If
            Else
                MsgBox "请使用统一的单位！"
            End If
        End If
    End If
Else: MsgBox "请将货物信息输入完整！"
End If
End Sub







Private Sub store_Click()
    pic_store.Visible = True
    pic_user_add.Visible = False
    pic_user_manage.Visible = False
    pic_passwd_upd.Visible = False
    pic_dam.Visible = False
    pic_view_store.Visible = False

End Sub



Private Sub Timer1_Timer()
'显示时间
lbl_time.Caption = Year(Date) & "年" & Month(Date) & "月" & Day(Date) & "日 " & Time
End Sub


Private Sub user_add_Click()
pic_user_add.Visible = True
pic_store.Visible = False
pic_user_manage.Visible = False
pic_passwd_upd.Visible = False
pic_dam.Visible = False
pic_view_store.Visible = False
End Sub


Private Sub user_manage_Click()
pic_user_manage.Visible = True
pic_user_add.Visible = False
pic_store.Visible = False
pic_passwd_upd.Visible = False
pic_dam.Visible = False
pic_view_store.Visible = False
Dim line
line = 0
Sql = "select * from [user] "
Set RS = dbSelect(Sql)
Do While Not RS.EOF
    line = line + 1
    name1 = RS.Fields(0)
    passwd = RS.Fields(1)
With msf_user
   .Cols = 2
   .TextMatrix(0, 0) = "用户名"
   .TextMatrix(0, 1) = "密码"
   .TextMatrix(line, 0) = name1
   .TextMatrix(line, 1) = passwd
   .FixedRows = 1
End With
    RS.MoveNext
    Loop
End Sub

Private Sub user_passwd_Click()
pic_passwd_upd.Visible = True
pic_store.Visible = False
pic_user_add.Visible = False
pic_user_manage.Visible = False
pic_dam.Visible = False
pic_view_store.Visible = False
End Sub

Private Sub view_store_Click()
pic_passwd_upd.Visible = False
pic_store.Visible = False
pic_user_add.Visible = False
pic_user_manage.Visible = False
pic_dam.Visible = False
pic_view_store.Visible = True


End Sub