main_rcyw_warehouse.frm

医药进销存管理系统 对医药的购买销售和库存进行系统管理

      If adoMedicine.Recordset.Fields("批号") <> "" Then msgWareHouse.TextMatrix(msgWareHouse.Row, 3) = Trim(adoMedicine.Recordset.Fields("批号"))
      If adoMedicine.Recordset.Fields("规格") <> "" Then msgWareHouse.TextMatrix(msgWareHouse.Row, 4) = Trim(adoMedicine.Recordset.Fields("规格"))
      If adoMedicine.Recordset.Fields("产地") <> "" Then msgWareHouse.TextMatrix(msgWareHouse.Row, 5) = Trim(adoMedicine.Recordset.Fields("产地"))
      If adoMedicine.Recordset.Fields("单位") <> "" Then msgWareHouse.TextMatrix(msgWareHouse.Row, 6) = Trim(adoMedicine.Recordset.Fields("单位"))
      If adoMedicine.Recordset.Fields("进货价") <> "" Then msgWareHouse.TextMatrix(msgWareHouse.Row, 7) = adoMedicine.Recordset.Fields("进货价")
      If adoMedicine.Recordset.Fields("批发价") <> "" Then msgWareHouse.TextMatrix(msgWareHouse.Row, 10) = adoMedicine.Recordset.Fields("批发价")
      If adoMedicine.Recordset.Fields("零售价") <> "" Then msgWareHouse.TextMatrix(msgWareHouse.Row, 11) = adoMedicine.Recordset.Fields("零售价")

      '赋值给txtInput
      txtInput.Text = msgWareHouse.Text
      txtInput.SetFocus
      msgWareHouse.Col = 7
      dgdStock.Visible = False
     Else
      MsgBox ("无数据选择！")
      dgdStock.Visible = False
      txtInput.SetFocus
   End If
  End If
   txtInput.SetFocus
 End If
 If KeyCode = vbKeyEscape Then    '按ESC键dgdStock不可见
    dgdStock.Visible = False
    txtInput.SetFocus     'txtInput获得焦点
 End If
End Sub
Private Sub dblProvider_KeyDown(KeyCode As Integer, Shift As Integer)
  If KeyCode = vbKeyReturn Then
    ProviderNumber = Val(dblProvider.BoundText)
    txtProvider.Text = dblProvider.Text
    dblProvider.Visible = False
    txtProvider.SetFocus
  End If
End Sub
Private Sub msgWareHouse_Click()     '单击msgWareHouse表，txtInput表可见并获得焦点
  If msgWareHouse.Row > 1 And msgWareHouse.TextMatrix(msgWareHouse.Row - 1, 7) <> "" Then
     txtInput.Visible = True
     txtInput.SetFocus
  End If
  If msgWareHouse.Row = 1 Then
     txtInput.Visible = True
     txtInput.SetFocus
  End If
End Sub
Private Sub msgWareHouse_entercell()    '确定txtInput在msgWareHouse表中的位置
  Dim X, y, P As String
  If msgWareHouse.CellWidth <= 0 Or msgWareHouse.CellHeight <= 0 Then Exit Sub
  X = msgWareHouse.TextMatrix(msgWareHouse.FixedRows, msgWareHouse.Col)
  y = msgWareHouse.TextMatrix(msgWareHouse.Row, 0)
  If y <> "" Then
    If msgWareHouse.Col - msgWareHouse.LeftCol <= 3 Then
       msgWareHouse.LeftCol = msgWareHouse.LeftCol + 1
    End If
    If msgWareHouse.CellWidth > 0 And msgWareHouse.CellHeight > 0 Then
      txtInput.Width = msgWareHouse.CellWidth
      txtInput.Height = msgWareHouse.CellHeight
      txtInput.Left = msgWareHouse.CellLeft + msgWareHouse.Left
      txtInput.Top = msgWareHouse.CellTop + msgWareHouse.Top
    End If
    X = msgWareHouse.TextMatrix(msgWareHouse.FixedRows, msgWareHouse.Col)
    y = msgWareHouse.TextMatrix(msgWareHouse.Row, 0)
    P = msgWareHouse.TextMatrix(msgWareHouse.Row, msgWareHouse.Col)
    txtInput.Text = msgWareHouse.Text
  End If
End Sub
Private Sub msgWareHouse_RowColChange()
  '格式化msgWareHouse表的第7列、第8列、第9列、第11列
  For i = 1 To 101
    With msgWareHouse
      .TextMatrix(.Row, 7) = Format(.TextMatrix(.Row, 7), "0.0000")
      .TextMatrix(.Row, 9) = Val(.TextMatrix(.Row, 7)) * Val(.TextMatrix(.Row, 8))
      .TextMatrix(.Row, 9) = Format(.TextMatrix(.Row, 9), "0.0000")
      .TextMatrix(.Row, 10) = Format(.TextMatrix(.Row, 10), "0.0000")
      .TextMatrix(.Row, 11) = Format(.TextMatrix(.Row, 11), "0.0000")
    End With
  Next i
End Sub
Private Sub txtInput_KeyDown(KeyCode As Integer, Shift As Integer)
  With msgWareHouse
    If KeyCode = vbKeyReturn Then
      dgdStock.Visible = False
      If .Col = 1 Then
        If adoMedicine.Recordset.RecordCount > 0 Then
          '赋值给表格
           .TextMatrix(.Row, 1) = adoMedicine.Recordset.Fields("药品名称")
           .TextMatrix(.Row, 2) = adoMedicine.Recordset.Fields("药品编号")
           .TextMatrix(.Row, 3) = adoMedicine.Recordset.Fields("批号")
           .TextMatrix(.Row, 4) = adoMedicine.Recordset.Fields("规格")
           .TextMatrix(.Row, 5) = adoMedicine.Recordset.Fields("产地")
           .TextMatrix(.Row, 6) = adoMedicine.Recordset.Fields("单位")
           .TextMatrix(.Row, 7) = adoMedicine.Recordset.Fields("进货价")
           txtInput.SetFocus
           txtInput.Text = .Text
           dgdStock.Visible = False
           .Col = 6
        End If
      End If
      If .Col = 12 Then
        .Row = .Row + 1
        .Col = 1
      Else
        If .Col + 1 <= .Cols - 1 Then
          .Col = .Col + 1
        Else
          If .Row + 1 <= .Rows - 1 Then
             .Row = .Row + 1
             .Col = 1
          End If
        End If
      End If
    End If
    If KeyCode = vbKeyUp Then
      If .Row > 1 Then .Row = .Row - 1
    End If
    If KeyCode = vbKeyDown Then
      If .Row < 99 Then .Row = .Row + 1
    End If
    If KeyCode = vbKeyLeft Then
      If txtInput.Text <> "" Then
        txtInput.SelStart = 0
        txtInput.SelLength = Len(txtInput.Text)
      End If
      If .Col - 9 <= .Cols + 1 Then
        .Col = .Col - 1
        If .Col = 0 Then .Col = 1
      Else
        If .Row + 1 <= .Row - 1 Then
          .Row = .Row + 1
          .Col = 1
        End If
      End If
    End If
    If KeyCode = vbKeyRight Then
      If txtInput.Text <> "" Then
         txtInput.SelStart = 0
         txtInput.SelLength = Len(txtInput.Text)
      End If
      If .Col + 1 <= .Cols - 1 Then
         .Col = .Col + 1
      Else
        If .Row + 1 <= .Rows - 1 Then
          .Row = .Row + 1
          .Col = 1
        End If
      End If
    End If
    If KeyCode = vbKeyPageDown Then
      If .Col = 1 Then
        adoMedicine.RecordSource = "select * from tb_medicine order by 药品编号"
        adoMedicine.Refresh
        dgdStock.Visible = True
        dgdStock.SetFocus
      End If
    End If
  End With
End Sub
Private Sub txtInput_Change()
  msgWareHouse.Text = txtInput.Text   '将txtInput的值赋给msgWareHouse
  If msgWareHouse.Col = 1 Then
     If txtInput.Text = "" Then
        dgdStock.Visible = False
      Else
        '筛选药品名称或简称符合txtInput的记录
        adoMedicine.RecordSource = "select * from tb_medicine where 药品名称 like '" + txtInput.Text + "'+ '%'or 拼音码 like '" + txtInput.Text + "'+'%'"
        adoMedicine.Refresh
        If adoMedicine.Recordset.RecordCount > 0 Then
           dgdStock.Visible = True
           txtInput.SetFocus
        End If
     End If
  End If
  If msgWareHouse.Col = 3 Then
     If msgWareHouse.TextMatrix(msgWareHouse.Row, 1) = "" Then
        MsgBox "无药品名称,请重新输入！", , Me.Caption
        msgWareHouse.Col = 1
     End If
  End If
  If msgWareHouse.Col = 7 Or 8 Then
     '求金额
     msgWareHouse.TextMatrix(msgWareHouse.Row, 9) = Val(msgWareHouse.TextMatrix(msgWareHouse.Row, 7)) * Val(msgWareHouse.TextMatrix(msgWareHouse.Row, 8))
  End If
  Dim a, b, c As Single
  For i = 1 To 101
    If msgWareHouse.TextMatrix(i, 1) <> "" And msgWareHouse.TextMatrix(i, 8) <> "" Then
      lblBreed = i     '品种数
      a = Val(msgWareHouse.TextMatrix(i, 9)) + a    '求合计金额
      b = Val(msgWareHouse.TextMatrix(i, 8)) + b    '求合计数量
    End If
  Next i
  lblCount = b
  lblSum = Format(a, "0.0000")     '格式化合计金额
End Sub
Private Sub cmdRegister_Click()
  '文本框的大小和位置等于msgWareHouse中网格的大小和位置
  txtInput.Width = msgWareHouse.CellWidth
  txtInput.Height = msgWareHouse.CellHeight
  txtInput.Left = msgWareHouse.CellLeft + msgWareHouse.Left
  txtInput.Top = msgWareHouse.CellTop + msgWareHouse.Top
  msgWareHouse.Enabled = True
  Dim tmpNote As Integer
  rs1.Open "select * from tb_warehouse_detailed order by 进货单据号", cnn, adOpenKeyset, adLockOptimistic
  '创建进货单据号
  If rs1.RecordCount > 0 Then
     If Not rs1.EOF Then rs1.MoveLast
     If rs1.Fields("进货单据号") <> "" Then
        tmpNote = Val(Right(Trim(rs1.Fields("进货单据号")), 4)) + 1
        txtNote.Text = Date & "rk" & Format(tmpNote, "0000")
     End If
   Else
     txtNote.Text = Date & "rk" & "0001"
  End If
  rs1.Close
  '清空msgWareHouse网格中的内容
  For i = 1 To 100
  For j = 1 To 12
      msgWareHouse.TextMatrix(i, j) = ""
  Next j
  Next i
  '清空文本框内容
  txtProvider.Text = ""
  txtInput.Text = ""
  txtHandle.Text = ""
  lblBreed.Caption = "0"
  lblCount = "0"
  lblSum = "0"
  '设置控件可用或不可用状态
  txtProvider.Enabled = True
  txtProvider.SetFocus
  cmdSave.Enabled = True
  cmdCancel.Enabled = True
  cmdRegister.Enabled = False
  txtInput.Enabled = True
End Sub
Private Sub cmdSave_Click()
  For i = 1 To 100
    If msgWareHouse.TextMatrix(i, 1) <> "" And msgWareHouse.TextMatrix(i, 8) <> "" Then
      rs1.Open "select * from tb_warehouse_detailed", cnn, adOpenKeyset, adLockOptimistic
      '添加新记录到"tb_warehouse_detailed"表中
      rs1.AddNew
      rs1.Fields("药品名称") = msgWareHouse.TextMatrix(i, 1)
      rs1.Fields("药品编号") = Val(msgWareHouse.TextMatrix(i, 2))
      rs1.Fields("批号") = msgWareHouse.TextMatrix(i, 3)
      rs1.Fields("规格") = msgWareHouse.TextMatrix(i, 4)
      rs1.Fields("产地") = msgWareHouse.TextMatrix(i, 5)
      rs1.Fields("单位") = msgWareHouse.TextMatrix(i, 6)
      rs1.Fields("进货价") = Val(msgWareHouse.TextMatrix(i, 7))
      rs1.Fields("数量") = Val(msgWareHouse.TextMatrix(i, 8))
      rs1.Fields("金额") = Val(msgWareHouse.TextMatrix(i, 9))
      rs1.Fields("批发价") = Val(msgWareHouse.TextMatrix(i, 10))
      rs1.Fields("零售价") = Val(msgWareHouse.TextMatrix(i, 11))
      rs1.Fields("有效期至") = Val(msgWareHouse.TextMatrix(i, 12))
      rs1.Fields("供货商编号") = ProviderNumber
      rs1.Fields("供货商名称") = txtProvider.Text
      rs1.Fields("进货日期") = txtDate.Text
      rs1.Fields("进货单据号") = txtNote.Text
      rs1.Update
      rs1.Close
      Dim rs3 As New ADODB.Recordset
      rs3.Open "select * from tb_stock where 药品编号='" & Val(msgWareHouse.TextMatrix(i, 2)) & "'and 批号='" & Val(msgWareHouse.TextMatrix(i, 3)) & "'", cnn, adOpenKeyset, adLockOptimistic
      '判断是否有记录
      If rs3.RecordCount > 0 Then
        If rs3.Fields("药品名称") <> "" Then
          '有记录,更新库存数量
          rs3.Fields("库存数量") = rs3.Fields("库存数量") + Val(msgWareHouse.TextMatrix(i, 8))
          rs3.Update
        End If
      Else
        rs3.AddNew
        rs3.Fields("药品名称") = msgWareHouse.TextMatrix(i, 1)
        rs3.Fields("药品编号") = Val(msgWareHouse.TextMatrix(i, 2))
        rs3.Fields("批号") = msgWareHouse.TextMatrix(i, 3)
        rs3.Fields("规格") = msgWareHouse.TextMatrix(i, 4)
        rs3.Fields("产地") = msgWareHouse.TextMatrix(i, 5)
        rs3.Fields("单位") = msgWareHouse.TextMatrix(i, 6)
        rs3.Fields("进货价") = Val(msgWareHouse.TextMatrix(i, 7))
        rs3.Fields("库存数量") = Val(msgWareHouse.TextMatrix(i, 8))
        rs3.Fields("批发价") = Val(msgWareHouse.TextMatrix(i, 10))
        rs3.Fields("零售价") = Val(msgWareHouse.TextMatrix(i, 11))
        rs3.Fields("有效期至") = Val(msgWareHouse.TextMatrix(i, 12))
        rs3.Update
      End If
      rs3.Close
      Set rs3 = Nothing
    End If
  Next i
  Dim rs2 As New ADODB.Recordset
  rs2.Open "select * from tb_warehouse_main", cnn, adOpenKeyset, adLockOptimistic
  rs2.AddNew
  rs2.Fields("进货单据号") = txtNote.Text
  rs2.Fields("品种数") = lblBreed
  rs2.Fields("数量") = lblCount
  rs2.Fields("金额") = lblSum
  rs2.Fields("供货商编号") = ProviderNumber
  rs2.Fields("日期") = txtDate.Text
  rs2.Fields("经手人") = txtHandle
  rs2.Update
  rs2.Close
  Set rs2 = Nothing
  txtInput.Visible = False
  dgdStock.Visible = False
  dblProvider.Visible = False
  msgWareHouse.Enabled = False
  cmdRegister.Enabled = True
  cmdSave.Enabled = False
End Sub
Private Sub cmdCancel_Click()     '取消操作
  For i = 1 To 101
  For j = 1 To 12
      msgWareHouse.TextMatrix(i, j) = ""
  Next j
  Next i
  msgWareHouse.Enabled = False
  txtInput.Visible = False
  lblBreed = "0"
  lblSum.Caption = "0"
  lblCount.Caption = "0"
  txtProvider.Text = ""
  cmdRegister.Enabled = True
  cmdRegister.SetFocus
End Sub