VERSION 5.00
Object = "{5E9E78A0-531B-11CF-91F6-C2863C385E30}#1.0#0"; "MSFLXGRD.OCX"
Object = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}#2.0#0"; "MSCOMCT2.OCX"
Begin VB.Form frmXSCX 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Form1"
   ClientHeight    =   7155
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   10020
   LinkTopic       =   "Form1"
   MaxButton       =   0   'False
   MDIChild        =   -1  'True
   MinButton       =   0   'False
   ScaleHeight     =   7155
   ScaleWidth      =   10020
   Begin VB.CommandButton Command1 
      Caption         =   "查看全部"
      Height          =   375
      Left            =   7560
      TabIndex        =   13
      Top             =   240
      Width           =   1095
   End
   Begin VB.OptionButton Option2 
      Caption         =   "查詢合計"
      Height          =   495
      Left            =   5280
      TabIndex        =   4
      Top             =   240
      Width           =   735
   End
   Begin VB.OptionButton Option1 
      Caption         =   "查詢明細(xì)"
      Height          =   495
      Left            =   3960
      TabIndex        =   3
      Top             =   240
      Width           =   735
   End
   Begin VB.Frame Frame2 
      Caption         =   "查詢銷售合計"
      Height          =   1695
      Left            =   4931
      TabIndex        =   2
      Top             =   840
      Width           =   4575
      Begin VB.CommandButton Command3 
         Caption         =   "確定"
         Enabled         =   0   'False
         Height          =   375
         Left            =   3600
         TabIndex        =   15
         Top             =   1080
         Width           =   855
      End
      Begin VB.ComboBox Combo4 
         Height          =   300
         Left            =   1680
         Sorted          =   -1  'True
         TabIndex        =   11
         Top             =   1080
         Width           =   1695
      End
      Begin VB.ComboBox Combo3 
         Height          =   300
         Left            =   1680
         Sorted          =   -1  'True
         TabIndex        =   10
         Top             =   360
         Width           =   1695
      End
      Begin VB.Label Label4 
         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "選擇日期"
         Height          =   180
         Left            =   240
         TabIndex        =   9
         Top             =   1080
         Width           =   720
      End
      Begin VB.Label Label3 
         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "選擇商品"
         Height          =   180
         Left            =   240
         TabIndex        =   8
         Top             =   480
         Width           =   720
      End
   End
   Begin VB.Frame Frame1 
      Caption         =   "查詢銷售明細(xì)"
      Height          =   1695
      Left            =   514
      TabIndex        =   1
      Top             =   840
      Width           =   4215
      Begin MSComCtl2.DTPicker DTdate 
         Height          =   300
         Left            =   1680
         TabIndex        =   16
         Top             =   1080
         Width           =   1455
         _ExtentX        =   2566
         _ExtentY        =   529
         _Version        =   393216
         Format          =   23724033
         CurrentDate     =   38024
      End
      Begin VB.CommandButton Command2 
         Caption         =   "確定"
         Enabled         =   0   'False
         Height          =   375
         Left            =   3240
         TabIndex        =   14
         Top             =   1080
         Width           =   855
      End
      Begin VB.ComboBox Combo1 
         Height          =   300
         Left            =   1680
         Sorted          =   -1  'True
         TabIndex        =   6
         Top             =   360
         Width           =   1455
      End
      Begin VB.Label Label2 
         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "選擇日期"
         Height          =   180
         Left            =   480
         TabIndex        =   7
         Top             =   1080
         Width           =   720
      End
      Begin VB.Label Label1 
         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "選擇商品"
         Height          =   180
         Left            =   480
         TabIndex        =   5
         Top             =   360
         Width           =   720
      End
   End
   Begin MSFlexGridLib.MSFlexGrid flgGrid 
      Height          =   4455
      Left            =   240
      TabIndex        =   0
      Top             =   2640
      Width           =   9495
      _ExtentX        =   16748
      _ExtentY        =   7858
      _Version        =   393216
   End
   Begin VB.Label Label5 
      AutoSize        =   -1  'True
      BackStyle       =   0  'Transparent
      Caption         =   "選擇："
      BeginProperty Font 
         Name            =   "宋體"
         Size            =   12
         Charset         =   134
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      Height          =   240
      Left            =   2880
      TabIndex        =   12
      Top             =   240
      Width           =   765
   End
End
Attribute VB_Name = "frmXSCX"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
Dim db As Database '聲明數(shù)據(jù)庫
Dim rs As Recordset '聲明記錄集
Dim Sql As String '聲明SQL語句字符串變量
Dim Sname As String '聲明查詢的商品名稱
Dim Soutdate As String '聲明查詢的日期

Private Sub Combo1_Validate(Cancel As Boolean)
    Cancel = True
    If Combo1.Text = "" Then
        MsgBox "請選擇商品"
    Else
        Cancel = False
    End If
    Command2.Enabled = True
End Sub

Private Sub Combo3_Validate(Cancel As Boolean)
    Cancel = True
    If Combo3.Text = "" Then
        MsgBox "請選擇商品"
    Else
        Cancel = False
    End If
End Sub

Private Sub Combo4_Click()
    Command3.Enabled = True
End Sub

Private Sub Combo4_Validate(Cancel As Boolean)
    Cancel = True
    If Combo4.Text = "" Then
        MsgBox "請選擇日期"
    Else
        Cancel = False
    End If
End Sub

Private Sub Command1_Click()
     '設(shè)置SQL語句
    Sql = "SELECT 庫存表.name, 銷售表.count, 銷售表.outdate, 銷售表.type, 銷售表.price FROM 庫存表 INNER JOIN 銷售表 ON 庫存表.code = 銷售表.code ORDER BY 銷售表.outdate DESC"
    '打開記錄集
    Set db = OpenDatabase(App.Path & "\db1.mdb")
    Set rs = db.OpenRecordset(Sql)
    Search
    rs.Close
    db.Close
End Sub

Private Sub Command2_Click()
   
    Sname = Combo1.Text
    Soutdate = DTdate.Value
    Sql = "SELECT 庫存表.name, 銷售表.count, 銷售表.outdate, 銷售表.type, 銷售表.price FROM 庫存表 INNER JOIN 銷售表 ON 庫存表.code = 銷售表.code where 庫存表.name='" & Sname & "' and 銷售表.outdate=#" & Soutdate & "# ORDER BY 銷售表.outdate DESC"
    Set db = OpenDatabase(App.Path & "\db1.mdb")
    Set rs = db.OpenRecordset(Sql)
    Search
End Sub

Private Sub Command3_Click()
    Sname = Combo3.Text
    Soutdate = Combo4.Text
    Sql = "SELECT 庫存表.name, sum(銷售表.count) as count, 銷售表.outdate, 銷售表.type, 銷售表.price FROM 庫存表 INNER JOIN 銷售表 ON 庫存表.code = 銷售表.code group by 庫存表.name,銷售表.outdate,銷售表.type, 銷售表.price having 庫存表.name='" & Sname & "' and 銷售表.outdate=#" & Soutdate & "# ORDER BY 銷售表.outdate DESC"
    Set db = OpenDatabase(App.Path & "\db1.mdb")
    Set rs = db.OpenRecordset(Sql)
    Search
End Sub

Private Sub Form_Load()
    Dim i As Integer
    Dim j As Integer
    '設(shè)置SQL語句
    Sql = "SELECT 庫存表.name from 庫存表"
    '打開記錄集
    Set db = OpenDatabase(App.Path & "\db1.mdb")
    Set rs = db.OpenRecordset(Sql)
    rs.MoveFirst
    '將商品名稱添加到組合框
    Do While Not rs.EOF
        Combo1.AddItem rs.Fields("name")
        Combo3.AddItem rs.Fields("name")
        rs.MoveNext
    Loop
    Sql = "SELECT 銷售表.outdate from 銷售表"
    Set rs = db.OpenRecordset(Sql)
    '將商品銷售日期添加到組合框
    rs.MoveFirst
    Do While Not rs.EOF
        
        Combo4.AddItem rs.Fields("outdate")
        rs.MoveNext
    Loop
    '將組合框中的重復(fù)項目刪除
    
    
    Frame1.Enabled = False
    Frame2.Enabled = False
    db.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)
'    db.Close
End Sub

Private Sub Option1_Click()
    flgGrid.Clear
    If Option1.Value = True Then
        Frame1.Enabled = True
        Frame2.Enabled = False
    End If
End Sub

Private Sub Option2_Click()
    flgGrid.Clear
    If Option2.Value = True Then
        Frame2.Enabled = True
        Frame1.Enabled = False
    End If
End Sub

Public Sub Search()
    '初始化MSFlexGrid
    flgGrid.Clear '清空
    flgGrid.Cols = 5 '設(shè)置總列數(shù)
    flgGrid.FixedCols = 0 '固定列為0
    flgGrid.FixedRows = 1 '固定行為1
    '設(shè)置每一列的寬度
    flgGrid.ColWidth(0) = flgGrid.Width / 6
    flgGrid.ColWidth(1) = flgGrid.Width / 5
    flgGrid.ColWidth(2) = flgGrid.Width / 5
    flgGrid.ColWidth(3) = flgGrid.Width / 5
    flgGrid.ColWidth(4) = flgGrid.Width / 5
    '設(shè)置列標(biāo)題
    flgGrid.TextMatrix(0, 0) = "商品名稱"
    flgGrid.TextMatrix(0, 1) = "銷售數(shù)量"
    flgGrid.TextMatrix(0, 4) = "銷售日期"
    flgGrid.TextMatrix(0, 3) = "類型"
    flgGrid.TextMatrix(0, 2) = "價格"
    flgGrid.Rows = 2
    If rs.EOF = True Then
        MsgBox "沒有符合條件的記錄"
    Else
        rs.MoveFirst ' 移動到記錄集的第一條記錄
    '   使用循環(huán)將記錄集中的記錄寫入表中，循環(huán)的退出條件是rs.eof
        Do While Not rs.EOF ' Loops until the Recordset is at end. (EOF = End Of File)
            flgGrid.TextMatrix(flgGrid.Rows - 1, 0) = rs.Fields(0).Value ' Set Grid Col 0 the data from Row 0
            flgGrid.TextMatrix(flgGrid.Rows - 1, 1) = rs.Fields(1).Value ' Set Grid Col 1 the data from Row 1
            flgGrid.TextMatrix(flgGrid.Rows - 1, 2) = rs.Fields(4).Value ' Set Grid Col 2 the data from Row 2
            flgGrid.TextMatrix(flgGrid.Rows - 1, 3) = rs.Fields(3).Value ' Set Grid Col 3 the data from Row 3
            flgGrid.TextMatrix(flgGrid.Rows - 1, 4) = rs.Fields(2).Value
            flgGrid.Rows = flgGrid.Rows + 1 '給表加一行
            rs.MoveNext ' 下一條記錄
        Loop
    End If
End Sub