chanpin.frm

明珠大厦相机组库存管理系统

Dim rst As Recordset
    Dim db As Connection
    Set rst = New Recordset
  
   ' Set rs2 = New Recordset
    Set db = New Connection
    conn = "select * from 产品类别 where 类别名称 ='" & Combo1 & "'"
    
    db.CursorLocation = adUseClient
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;Persist Security Info=False"
    rst.Open conn, db, adOpenDynamic, adLockOptimistic

    If Not rst.EOF Then
    aaa = rst.Fields("id")
    Else
   
   
        X = MsgBox("是否添加新类别:" & Combo1.Text & "?", vbOKCancel)
        
        If X = 1 Then
            conn = "insert into 产品类别(类别名称) values ('" & Combo1 & "')"
            rst.Close

            rst.Open conn, db, adOpenDynamic, adLockOptimistic
            MsgBox "类别添加成功"
            conn = "select * from 产品类别 where 类别名称 ='" & Combo1 & "'"
            rst.Open conn, db, adOpenDynamic, adLockOptimistic
            aaa = rst.Fields("id")
        Else
            Exit Sub
        End If
   
   
    End If
rst.Close




conn = "select * from 生产厂家 where 品牌 ='" & Combo2 & "'"

rst.Open conn, db, adOpenDynamic, adLockOptimistic

  If Not rst.EOF Then
    bbb = rst.Fields("id")
    
    Else
    
     X = MsgBox("是否添加新品牌:" & Combo2.Text & "?", vbOKCancel)
        If X = 1 Then
        conn = "insert into 生产厂家(品牌) values ('" & Combo2 & "')"
        rst.Close
        rst.Open conn, db, adOpenDynamic, adLockOptimistic
        MsgBox "品牌添加成功"
        conn = "select * from 生产厂家 where 品牌 ='" & Combo2 & "'"
        rst.Open conn, db, adOpenDynamic, adLockOptimistic
        bbb = rst.Fields("id")
        Else
            Exit Sub
        End If
    
    End If
rst.Close



conn = "update 产品库存 set  单价=" & Text3 & "  where id=" & Text1
Debug.Print conn
rst.Open conn, db, adOpenDynamic, adLockOptimistic
 
conn = "update 产品库存 set  数量=" & Text2 & "  where id=" & Text1
Debug.Print conn
rst.Open conn, db, adOpenDynamic, adLockOptimistic
 
conn = "update 产品库存 set  产品类别=" & aaa & "  where id=" & Text1
Debug.Print conn
rst.Open conn, db, adOpenDynamic, adLockOptimistic
 
conn = "update 产品库存 set  生产厂家=" & bbb & "  where id=" & Text1
Debug.Print conn
rst.Open conn, db, adOpenDynamic, adLockOptimistic
 
conn = "update 产品库存 set  产品型号='" & Combo3 & "'  where id=" & Text1
Debug.Print conn
rst.Open conn, db, adOpenDynamic, adLockOptimistic
 
conn = "update 产品库存 set  入库日期='" & DTPicker1.Value & "'  where id=" & Text1
Debug.Print conn
rst.Open conn, db, adOpenDynamic, adLockOptimistic
 
 
 
 
 Label7.Visible = True




End If
End Sub

Private Sub Command2_Click()
Unload Me
End Sub

Private Sub Combo3_GotFocus()
Combo3.Clear
   Label7.Visible = False
   List1.Clear
If Combo1.Text = "" Or Combo2.Text = "" Then
Exit Sub
Else
   Dim rst As Recordset
    Dim db As Connection
    Set rst = New Recordset
  
   ' Set rs2 = New Recordset
    Set db = New Connection
    conn = "select * from 产品类别 where 类别名称 ='" & Combo1 & "'"
   
    db.CursorLocation = adUseClient
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;Persist Security Info=False"
    rst.Open conn, db, adOpenDynamic, adLockOptimistic

    If Not rst.EOF Then
    aaa = rst.Fields("id")
    
    Else
    Exit Sub
    End If
rst.Close

conn = "select * from 生产厂家 where 品牌 ='" & Combo2 & "'"

rst.Open conn, db, adOpenDynamic, adLockOptimistic

  If Not rst.EOF Then
    bbb = rst.Fields("id")
    
    Else
    Exit Sub
    End If
rst.Close

conn = "select * from 产品库存 where 生产厂家=" & bbb & " and 产品类别=" & aaa & " order by 产品型号 desc"

rst.Open conn, db, adOpenDynamic, adLockOptimistic
If Not rst.EOF Then
    demo = rst.Fields("产品型号")
    Combo3.AddItem demo
    Combo3.Text = demo
    rst.MoveNext
    Do Until rst.EOF
       If demo = rst.Fields("产品型号") Then
            rst.MoveNext
        Else
            Combo3.AddItem rst.Fields("产品型号")
            demo = rst.Fields("产品型号")
            rst.MoveNext
        End If
    Loop
Else
    Combo3.Text = ""
End If
rst.Close
If Not Combo3.Text = "" Then

'conn = "select * from 产品库存 where 生产厂家=" & bbb & " and 产品类别=" & aaa & "and 产品型号='" & Combo3.Text & "'"

'rst.Open conn, db, adOpenDynamic, adLockOptimistic

'Do Until rst.EOF
'List1.AddItem rst.Fields("id")
'rst.MoveNext
'Loop


'rst.Close


End If
End If








End Sub


Private Sub dataz()
    
    
    Combo1.Clear
    Combo2.Clear
    Combo3.Clear
        
    Text2 = ""
    Text3 = ""
    Label7.Visible = False
    
        
    Dim rst As Recordset
    Dim db As Connection
    Set rst = New Recordset
  
   ' Set rs2 = New Recordset
    Set db = New Connection
    conn = "select * from 生产厂家"
    db.CursorLocation = adUseClient
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;Persist Security Info=False"
    rst.Open conn, db, adOpenDynamic, adLockOptimistic
    If Not rst.EOF Then
      rst.MoveFirst
      Combo2.Text = rst.Fields("品牌")
         Do Until rst.EOF
             Combo2.AddItem rst.Fields("品牌")
             rst.MoveNext
         Loop
   
    Else
      Combo1.Text = "无任何品牌"
    End If
    
    rst.Close
    
     conn = "select * from 产品类别"
    rst.Open conn, db, adOpenDynamic, adLockOptimistic
    If Not rst.EOF Then
      rst.MoveFirst
      Combo1.Text = rst.Fields("类别名称")
         Do Until rst.EOF
             Combo1.AddItem rst.Fields("类别名称")
             rst.MoveNext
         Loop
   
    Else
      Combo1.Text = "无任何类别"
    End If
    
    rst.Close
    
    
    
    
    List1.Clear
    
    
    
    
    
    
    
End Sub

Private Sub Form_Load()
Call dataz
DTPicker1.Value = Now()
End Sub

Private Sub List1_Click()

Label7.Visible = False

If Combo1.Text = "" Or Combo2.Text = "" Or Combo3.Text = "" Or List1.Text = "" Then
Exit Sub
Else

Debug.Print List1.Text



    Dim rst As Recordset
    Dim db As Connection
    Set rst = New Recordset
  
   ' Set rs2 = New Recordset
    Set db = New Connection
    conn = "select * from 产品类别 where 类别名称 ='" & Combo1 & "'"
   
    db.CursorLocation = adUseClient
    db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database.mdb;Persist Security Info=False"
    rst.Open conn, db, adOpenDynamic, adLockOptimistic

    If Not rst.EOF Then
    aaa = rst.Fields("id")
    
    Else
    Exit Sub
    End If
    rst.Close

conn = "select * from 生产厂家 where 品牌 ='" & Combo2 & "'"

rst.Open conn, db, adOpenDynamic, adLockOptimistic

  If Not rst.EOF Then
    bbb = rst.Fields("id")
    
    Else
    Exit Sub
    End If
    rst.Close


conn = "select * from 产品库存 where id=" & List1.Text


rst.Open conn, db, adOpenDynamic, adLockOptimistic

DTPicker1.Value = rst.Fields("入库日期")
Text2 = rst.Fields("数量")
Text3 = rst.Fields("单价")
Text1 = List1.Text
rst.Close

















End If


End Sub