chaxunto.frm

这是本人利用vb+access 开发的一个二库存进销存系统

  Data2.Recordset.MoveNext
Wend
Combo2.Text = Combo2.List(0)
End Sub
Private Sub Combo1_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub Combo2_Click()
Combo3.Clear
If Data2.Recordset.RecordCount > 0 Then
   Data2.Recordset.MoveFirst
End If
While Data2.Recordset.EOF = False
  If Data2.Recordset.Fields("产品") = Combo2.Text Then
    Combo3.AddItem Data2.Recordset.Fields("规格")
  End If
  Data2.Recordset.MoveNext
Wend
Combo3.Text = Combo3.List(0)
Combo3.Enabled = False
Command1.Enabled = True
End Sub
Private Sub Combo2_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub Combo3_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub Check1_Click()
If Check1.Value = Checked Then
Combo1.Enabled = True
Combo2.Enabled = True
Combo3.Enabled = True
Command1.Enabled = False
Else
Check1.Value = Unchecked
Combo1.Enabled = False
Combo2.Enabled = False
Combo3.Enabled = False
End If
End Sub

Private Sub Command1_Click()
If Text1.Text = "" Or Text2.Text = "" Or Len(Text1.Text) < 8 Or Len(Text2.Text) < 8 Then
   MsgBox "时段格式错误!", 0 + 16, "警告"
   MSFlexGrid1.Visible = False
   Exit Sub
End If
 '使用DAO的数据库连接方式
  Dim db As Database
  Dim rs As Recordset
  Dim sqlstr$ '存放查询语句
If Check1.Value = False Then
  Set db = OpenDatabase(App.Path & "\odb.dll")
  sqlstr = "select * from chuku where 时间>='" + Text1.Text + "' and 时间<='" + Text2.Text + "'"
  Set rs = db.OpenRecordset(sqlstr)
  '显示结果
  If rs.EOF And rs.BOF Then
  MsgBox "没有该记录", 0 + 16, "警告"
  MSFlexGrid1.Visible = False
Exit Sub
Else
  MSFlexGrid1.Visible = True
  Call GridShow(rs)
  End If
Else
   Set db = OpenDatabase(App.Path & "\odb.dll")
  sqlstr = "select * from chuku where 时间>='" + Text1.Text + "' and 时间<='" + Text2.Text + "' and 规格='" + Combo3.Text + "'"
  Set rs = db.OpenRecordset(sqlstr)
  '显示结果
  If rs.EOF And rs.BOF Then
  MsgBox "没有该记录", 0 + 16, "警告"
  MSFlexGrid1.Visible = False
Exit Sub
Else
  MSFlexGrid1.Visible = True
  Call GridShow(rs)
  End If
End If
End Sub
Private Sub ShowByDAO()
  '使用DAO的数据库连接方式
  Dim db As Database
  Dim rs As Recordset
  Dim sqlstr$ '存放查询语句
  Set db = OpenDatabase(App.Path & "\odb.dll")
  sqlstr = "select * from chuku where 编号='" + Text3.Text + "'"
  Set rs = db.OpenRecordset(sqlstr)
  '显示结果
  If rs.EOF And rs.BOF Then
  MsgBox "没有该记录", 0 + 16, "警告"
  MSFlexGrid1.Visible = False
Exit Sub
Else
  MSFlexGrid1.Visible = True
  Call GridShow(rs)
  End If
End Sub
Private Sub showbydao1()
  
End Sub
Private Sub Command2_Click()
If Text3.Text = "" Then
MsgBox "票据编码不能为空!", 0 + 16, "警告"
MSFlexGrid1.Visible = False
Exit Sub
End If
Call ShowByDAO
End Sub
Sub GridShow(rs As Recordset)
  '对dao方式进行显示工作
  MSFlexGrid1.TextMatrix(0, 0) = "产品名称"
  MSFlexGrid1.TextMatrix(0, 1) = "单据编号"
  MSFlexGrid1.TextMatrix(0, 2) = "品牌"
  MSFlexGrid1.TextMatrix(0, 3) = "单位"
  MSFlexGrid1.TextMatrix(0, 4) = "数量"
  MSFlexGrid1.TextMatrix(0, 5) = "出库时间"
  MSFlexGrid1.TextMatrix(0, 6) = "产品规格"
  MSFlexGrid1.TextMatrix(0, 7) = "备注"
  rs.MoveLast
  MSFlexGrid1.Rows = rs.RecordCount + 1
  MSFlexGrid1.Cols = rs.Fields.Count
  Dim i%
  i = 1
  rs.MoveFirst
  While (Not rs.EOF)
    MSFlexGrid1.TextMatrix(i, 0) = rs.Fields(0)
    MSFlexGrid1.TextMatrix(i, 1) = rs.Fields(1)
    MSFlexGrid1.TextMatrix(i, 2) = rs.Fields(2)
    MSFlexGrid1.TextMatrix(i, 3) = rs.Fields(3)
    MSFlexGrid1.TextMatrix(i, 4) = rs.Fields(4)
    MSFlexGrid1.TextMatrix(i, 5) = rs.Fields(5)
    MSFlexGrid1.TextMatrix(i, 6) = rs.Fields(6)
    MSFlexGrid1.TextMatrix(i, 7) = rs.Fields(7)
    rs.MoveNext
    i = i + 1
    'If (rs.EOF = True) Then
    ' Exit For
  Wend
  Dim m As Single
Dim n As Single
If MSFlexGrid1.Rows <> 1 Then
     m = MSFlexGrid1.Rows - 1
     For i = 1 To m
     n = n + MSFlexGrid1.TextMatrix(i, 4)
     Next i
     
     MSFlexGrid1.AddItem "合计" + vbTab + "—" + vbTab + "—" + vbTab + "—" + vbTab + Str(n) + vbTab + "—" + vbTab + "—" + vbTab + "—"
End If
End Sub

Private Sub Command3_Click()
Unload Me
End Sub

Private Sub Command4_Click()
If Val(Month(Now)) < 10 Then
    If Val(Day(Now)) < 10 Then
     Text2.Text = Str(Year(Now)) & "0" & Trim(Str(Month(Now))) & "0" & Trim(Str(Day(Now)))
    Else
    Text2.Text = Str(Year(Now)) & "0" & Trim(Str(Month(Now))) & Trim(Str(Day(Now)))
    End If
    Else
    If Val(Day(Now)) < 10 Then
       Text2.Text = Str(Year(Now)) & Trim(Str(Month(Now))) & "0" & Trim(Str(Day(Now)))
    Else
       Text2.Text = Year(Now) & Trim(Month(Now)) & Trim(Day(Now))
    End If
  End If
End Sub

Private Sub Command5_Click()
Call Toexcel
End Sub

Private Sub Option1_Click()
Option1.Value = True
Frame2.Enabled = False
Frame1.Enabled = True
MSFlexGrid1.Visible = False
Text3.Text = ""
End Sub

Private Sub Option2_Click()
Option2.Value = True
Frame1.Enabled = False
Frame2.Enabled = True
MSFlexGrid1.Visible = False
Text1.Text = ""
Text2.Text = ""
Check1.Value = Unchecked

End Sub
Private Sub Form_Activate()

Data1.DatabaseName = App.Path + "\odb.dll"
Data1.RecordSource = "paizi"
Data1.Refresh
Data2.DatabaseName = App.Path + "\odb.dll"
Data2.RecordSource = "kucun"
Data2.Refresh
Data3.DatabaseName = App.Path + "\odb.dll"
Data3.RecordSource = "chuku"
Data3.Refresh

If Data1.Recordset.RecordCount > 0 Then
   Data1.Recordset.MoveFirst
End If
While Data1.Recordset.EOF = False
  Combo1.AddItem Data1.Recordset.Fields("品牌")
  Data1.Recordset.MoveNext
Wend
Combo1.Text = Combo1.List(0)
If Data2.Recordset.RecordCount > 0 Then
   Data2.Recordset.MoveFirst
End If
While Data2.Recordset.EOF = False
  If Data2.Recordset.Fields("品牌") = Combo1.Text Then
     Combo2.AddItem Data2.Recordset.Fields("产品")
  End If
  Data2.Recordset.MoveNext
Wend
Combo2.Text = Combo2.List(0)

If Data2.Recordset.RecordCount > 0 Then
   Data2.Recordset.MoveFirst
End If
While Data2.Recordset.EOF = False
  If Data2.Recordset.Fields("产品") = Combo2.Text Then
    Combo3.AddItem Data2.Recordset.Fields("规格")
  End If
   Data2.Recordset.MoveNext
Wend
Combo3.Text = Combo3.List(0)


Option1.Value = True
Frame2.Enabled = False

Combo1.Enabled = False
Combo2.Enabled = False
Combo3.Enabled = False
MSFlexGrid1.Visible = False

MSFlexGrid1.ColWidth(0) = 2000
MSFlexGrid1.ColWidth(1) = 1500
MSFlexGrid1.ColWidth(2) = 1000
MSFlexGrid1.ColWidth(3) = 750
MSFlexGrid1.ColWidth(4) = 750
MSFlexGrid1.ColWidth(5) = 1000
MSFlexGrid1.ColWidth(6) = 1500
MSFlexGrid1.ColWidth(7) = 1750
End Sub
Private Sub Text3_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then
       KeyAscii = 0
       Command2.Value = 1
    End If
End Sub
Private Sub Toexcel()
    On Error GoTo trip
    Dim xlsApp As Excel.Application
    Dim xlsBook As Excel.Workbook
    Dim xlsSheet As Excel.Worksheet
    Set xlsApp = New Excel.Application
    Set xlsBook = xlsApp.Workbooks.Add
    Set xlsSheet = xlsBook.Worksheets(1)
    
    Dim i As Integer
    Dim n As Integer
    For i = 0 To Me.MSFlexGrid1.Rows - 1
        For n = 0 To Me.MSFlexGrid1.Cols - 1
            xlsSheet.Cells(i + 3, n + 1) = Me.MSFlexGrid1.TextMatrix(i, n)
        Next
    Next
    xlsApp.Visible = True
trip:         MsgBox "系统缺陷,无法导出!", 0 + 16
   Exit Sub
End Sub