📄 frmxscx.frm
字号:
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 = "查询明细"
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 = "查询销售明细"
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 '声明数据库
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()
'设置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
'设置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
'将组合框中的重复项目删除
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 '设置总列数
flgGrid.FixedCols = 0 '固定列为0
flgGrid.FixedRows = 1 '固定行为1
'设置每一列的宽度
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
'设置列标题
flgGrid.TextMatrix(0, 0) = "商品名称"
flgGrid.TextMatrix(0, 1) = "销售数量"
flgGrid.TextMatrix(0, 4) = "销售日期"
flgGrid.TextMatrix(0, 3) = "类型"
flgGrid.TextMatrix(0, 2) = "价格"
flgGrid.Rows = 2
If rs.EOF = True Then
MsgBox "没有符合条件的记录"
Else
rs.MoveFirst ' 移动到记录集的第一条记录
' 使用循环将记录集中的记录写入表中，循环的退出条件是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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -