frmreturn.frm

不错的药房管理系统

         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      _NumberOfBands  =   1
      _Band(0).Cols   =   9
   End
   Begin VB.Label Label7 
      Caption         =   "总金额："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   6870
      TabIndex        =   16
      Top             =   4320
      Width           =   975
   End
   Begin VB.Label Label6 
      Caption         =   "合计金额："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   3210
      TabIndex        =   15
      Top             =   5550
      Width           =   1245
   End
   Begin VB.Label Label5 
      Caption         =   "日期："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   240
      TabIndex        =   14
      Top             =   5550
      Width           =   975
   End
   Begin VB.Label Label3 
      Caption         =   "数量："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   3240
      TabIndex        =   13
      Top             =   4830
      Width           =   975
   End
   Begin VB.Label Label4 
      Caption         =   "客户名称："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   3240
      TabIndex        =   12
      Top             =   4230
      Width           =   1305
   End
   Begin VB.Label Label2 
      Caption         =   "单价："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   240
      TabIndex        =   11
      Top             =   4830
      Width           =   855
   End
   Begin VB.Label Label1 
      Caption         =   "药品名称："
      BeginProperty Font 
         Name            =   "宋体"
         Size            =   12
         Charset         =   134
         Weight          =   400
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   375
      Left            =   240
      TabIndex        =   10
      Top             =   4230
      Width           =   1245
   End
End
Attribute VB_Name = "frmReturn"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Const strSELECT_SQL = "SELECT 退货.ID, 客户.客户名称,药品.药品名称,药品.简称,药品.规格,药品.产地,退货.数量,退货.单价,退货.日期 " & _
                    "FROM 客户,药品,退货 WHERE 客户.客户编号=退货.客户编号 " & _
                    "AND 药品.药品编号=退货.药品编号 "
Dim SearchRst As New ADODB.Recordset

Private Sub cboCustomer_Change()
Dim CustomerRst As New ADODB.Recordset
    CustomerRst.Source = "SELECT * FROM 客户 WHERE 客户.客户名称 Like '%" & Trim(cboCustomer) & "%'"
    CustomerRst.Open , Gado, adOpenStatic, adLockReadOnly
    Do While Not CustomerRst.EOF
        cboCustomer.AddItem CustomerRst!客户名称 & ""
        CustomerRst.MoveNext
    Loop
     RefreshList
End Sub

Private Sub cboMedcine_Change()
Dim MedcineRst As New ADODB.Recordset
    MedcineRst.Source = "SELECT * FROM 药品 WHERE 药品.药品名称 Like '%" & Trim(cboMedcine) & "%'"
    MedcineRst.Open , Gado, adOpenStatic, adLockReadOnly
    Do While Not MedcineRst.EOF
        cboMedcine.AddItem MedcineRst!药品名称 & ""
        MedcineRst.MoveNext
    Loop
     RefreshList
End Sub

Private Sub cboMedcine_Click()
    Dim Rst As New ADODB.Recordset
    Dim Rst1 As New ADODB.Recordset
    
    Rst.Source = "SELECT * FROM 药品 WHERE 药品名称='" & Trim(cboMedcine) & "'"
    Rst.Open , Gado, adOpenStatic, adLockReadOnly
    If Rst.EOF = False Then
            strMedcineCode = Rst!药品编号 & ""
        End If
    
    Rst1.Source = "SELECT * FROM 库存 WHERE 药品编号='" & strMedcineCode & "'"
    Rst1.Open , Gado, adOpenStatic, adLockReadOnly
    txtPrice = Rst1!单价
    
    RefreshList
End Sub

Private Sub cboQueryType_Click()
On Error Resume Next
    Select Case cboQueryType.ListIndex
        Case 0, 1, 2:
            Me.lblValue1.Caption = "输入:"
            Me.lblValue2.Visible = False
            Me.txtQueryValue1.Visible = True
        Case 3:
           Me.lblValue1.Caption = "起始"
           Me.txtQueryValue1.Visible = False
    End Select
End Sub

Private Sub cmdAddNew_Click()
On Error Resume Next

 If Trim(cboMedcine) = "" Or Trim(cboCustomer) = "" Or Trim(txtCount) = "" Then
            MsgBox "请输入相应内容！", vbCritical
            Exit Sub
        End If


    Dim strSQL As String
    Dim count As Long
    Dim Rst As New ADODB.Recordset
    Dim Rst1 As New ADODB.Recordset
       
    Dim MedcineRst As New ADODB.Recordset
    MedcineRst.Source = "SELECT * FROM 药品 WHERE 药品名称='" & Trim(cboMedcine) & "'"
    MedcineRst.Open , Gado, adOpenStatic, adLockReadOnly
    If MedcineRst.EOF = False Then
        strMedcineCode = MedcineRst!药品编号 & ""
    End If
    Set MedcineRst = Nothing
    
    Dim CustomerRst As New ADODB.Recordset
    CustomerRst.Source = "SELECT * FROM 客户 WHERE 客户名称='" & Trim(cboCustomer) & "'"
    CustomerRst.Open , Gado, adOpenStatic, adLockReadOnly
    If CustomerRst.EOF = False Then
        strCode = CustomerRst!客户编号 & ""
    End If
    Set CustomerRst = Nothing
    
    
    strSQL = "INSERT INTO 退货(药品编号,数量,单价,日期,客户编号,编辑) " & _
                            " VALUES('" & strMedcineCode & "'," & CLng(txtCount.Text) & ", " & _
                            " " & CLng(txtPrice) & ",'" & txtTime.Text & "','" & strCode & "','是')"
    Gado.Execute strSQL
    
    count = CLng(txtCount.Text)
    Rst.Source = "SELECT * FROM 药品 WHERE 药品名称='" & Trim(cboMedcine) & "'"
    Rst.Open , Gado, adOpenStatic, adLockReadOnly
    strCode = Rst!药品编号
    Set Rst = Nothing
    
    Rst1.Source = "SELECT * FROM 库存 WHERE 药品编号='" & strCode & "'"
    Rst1.Open , Gado, adOpenDynamic, adLockOptimistic
    Rst1!库存数量 = Rst1!库存数量 + count
    Rst1.Update
    Set Rst1 = Nothing
    RefreshList
 
    txtTotal = CSng(txtAll) + CSng(Val(txtTotal))
    
    Me.cboCustomer = ""
    Me.cboMedcine = ""
    Me.txtCount = ""
End Sub

Private Sub cmdCancelQuery_Click()
    RefreshList
End Sub

Private Sub cmdPrint_Click()
   THreport.ShowMe SearchRst
End Sub


Private Sub cmdSearch_Click()
'On Error Resume Next
If Trim(cboQueryType) = "" Then
    MsgBox "请选择查询条件！", vbCritical
       Exit Sub
End If
If Trim(cboQueryType) = "药品名称" And Trim(txtQueryValue1) = "" Then
       MsgBox "请输入查询内容！", vbCritical
       Exit Sub
End If
If Trim(cboQueryType) = "药品简称" And Trim(txtQueryValue1) = "" Then
       MsgBox "请输入查询内容！", vbCritical
       Exit Sub
End If
If Trim(cboQueryType) = "客户名称" And Trim(txtQueryValue1) = "" Then
       MsgBox "请输入查询内容！", vbCritical
       Exit Sub
End If
    Dim strDate1 As String, strDate2 As String

    If SearchRst.State = adStateOpen Then SearchRst.Close
    Select Case cboQueryType.ListIndex
        Case 0:
            SearchRst.Source = strSELECT_SQL & " AND 药品.药品名称 Like '%" & Trim(txtQueryValue1) & "%'"
        Case 1:
            SearchRst.Source = strSELECT_SQL & " AND 药品.简称 Like '%" & Trim(txtQueryValue1) & "%'"
        Case 2:
            SearchRst.Source = strSELECT_SQL & " AND 客户.客户名称 Like '%" & Trim(txtQueryValue1) & "%'"
        Case 3:
            If "" <> Me.DTPicker1.Value & "" Then strDate1 = " AND 退货.日期 >='" & Format(DTPicker1, "YYYY-MM-DD") & "'"
            If "" <> Me.DTPicker2.Value & "" Then strDate2 = " AND 退货.日期 <='" & Format(DTPicker2, "YYYY-MM-DD") & "'"
            SearchRst.Source = strSELECT_SQL & strDate1 & strDate2
    End Select

    

    SearchRst.Open , Gado, adOpenStatic, adLockReadOnly
    Set Me.fgMedcineList.DataSource = SearchRst
    
    If SearchRst.RecordCount <= 0 Then
        MsgBox "找不到查询内容！", vbCritical
        txtQueryValue1.Text = ""
       Exit Sub
    End If
    
    txtQueryValue1.Text = ""
End Sub


Private Sub Command1_Click()
    Unload Me
End Sub

Private Sub Form_Load()
'   RefreshList
    Gado.Execute "UPDATE 退货 SET 退货.编辑='否'"
   txtTime = Format(Date, "YYYY-MM-DD")
   
   Dim MedcineRst As New ADODB.Recordset
    MedcineRst.Source = "SELECT * FROM 药品"
    MedcineRst.Open , Gado, adOpenStatic, adLockReadOnly
    Do While Not MedcineRst.EOF
        cboMedcine.AddItem MedcineRst!药品名称 & ""
        MedcineRst.MoveNext
    Loop
'    cboMedcine.ListIndex = 0
    
    Dim CustomerRst As New ADODB.Recordset
    CustomerRst.Source = "SELECT * FROM 客户"
    CustomerRst.Open , Gado, adOpenStatic, adLockReadOnly
    Do While Not CustomerRst.EOF
        cboCustomer.AddItem CustomerRst!客户名称 & ""
        CustomerRst.MoveNext
    Loop
'    cboCustomer.ListIndex = 0
    
    Set CustomerRst = Nothing
    
    cboQueryType.AddItem "药品名称"
    cboQueryType.AddItem "简称"
    cboQueryType.AddItem "客户名称"
    cboQueryType.AddItem "日期"
    
    RefreshList
End Sub

Private Sub RefreshList()
    If SearchRst.State = adStateOpen Then SearchRst.Close
    SearchRst.Source = strSELECT_SQL & " AND 退货.编辑='是'"
    SearchRst.Open , Gado, adOpenStatic, adLockReadOnly
    If SearchRst.EOF = False Then
        Set Me.fgMedcineList.DataSource = SearchRst
    Else
        fgMedcineList.Clear
        fgMedcineList.Rows = 2
        fgMedcineList.TextMatrix(0, 1) = "客户名称"
        fgMedcineList.TextMatrix(0, 2) = "药品名称"
        fgMedcineList.TextMatrix(0, 3) = "简称"
        fgMedcineList.TextMatrix(0, 4) = "药品成分"
        fgMedcineList.TextMatrix(0, 5) = "产地"
        fgMedcineList.TextMatrix(0, 6) = "数量"
        fgMedcineList.TextMatrix(0, 7) = "单价"
        fgMedcineList.TextMatrix(0, 8) = "日期"
    End If
    fgMedcineList.ColWidth(0) = 0
End Sub

Private Sub txtCount_Change()
    If Trim(txtPrice) = "" Or Trim(txtCount) = "" Then
        txtAll = ""
        Exit Sub
    End If
    txtAll = CSng(txtCount) * CSng(txtPrice)
End Sub


Private Sub txtCount_KeyPress(KeyAscii As Integer)
    InputControl txtCount, KeyAscii, OnlySingle
End Sub