module1.bas

供销信息查询系统

Attribute VB_Name = "Module1"
Public rs As New ADODB.Recordset
Private strconn As String
Private strsql As String
Private intctr As Integer
Private intj As Integer
Private flag As Integer
Public lastid As Integer
Public add1 As Integer
Public var1 As Variant
Public sst As Integer
Public strcha As String

Sub sstab()
On Error Resume Next
If mainform.SSTab1.Tab = 0 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 厂家"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
    If add1 = 1 Then
    .addnew
    If chunjiaform.Text1.Text = "" Then
    chunjiaform.Text1.Text = lastid
    End If
    If chunjiaform.Text2.Text = "" Then
    chunjiaform.Text2.Text = ""
    End If
    If chunjiaform.Text3.Text = "" Then
    chunjiaform.Text3.Text = ""
    End If
    If chunjiaform.Text4.Text = "" Then
    chunjiaform.Text4.Text = ""
    End If
    If chunjiaform.Text5.Text = "" Then
    chunjiaform.Text5.Text = 0
    End If
    If chunjiaform.Text6.Text = "" Then
    chunjiaform.Text6.Text = 0
    End If
    rs(0) = chunjiaform.Text1.Text
    rs(1) = chunjiaform.Text2.Text
    rs(2) = chunjiaform.Text3.Text
    rs(3) = chunjiaform.Text4.Text
    rs(4) = chunjiaform.Text5.Text
    rs(5) = chunjiaform.Text6.Text
    .Update
    add1 = 0
    var1 = 0
    End If
  End With
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 厂家"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid1.DataSource = rs
  Set mainform.Adodc1.Recordset = rs
  mainform.Adodc1.Caption = "当前记录为：" & mainform.Adodc1.Recordset.AbsolutePosition & "/" & mainform.Adodc1.Recordset.RecordCount
End If
If mainform.SSTab1.Tab = 1 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 产品"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
    If add1 = 1 Then
    .addnew
    If chanpinform.Text1.Text = "" Then
    chanpinform.Text1.Text = lastid
    End If
    If chanpinform.Text2.Text = "" Then
    chanpinform.Text2.Text = ""
    End If
    If chanpinform.Text3.Text = "" Then
    chanpinform.Text3.Text = ""
    End If
    If chanpinform.Text4.Text = "" Then
    chanpinform.Text4.Text = ""
    End If
    If chanpinform.Text5.Text = "" Then
    chanpinform.Text5.Text = 0
    End If
    rs(0) = chanpinform.Text1.Text
    rs(1) = chanpinform.Text2.Text
    rs(2) = chanpinform.Text3.Text
    rs(3) = chanpinform.Text4.Text
    rs(4) = chanpinform.Text5.Text
    .Update
    add1 = 0
    var1 = 1
    End If
  End With
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 产品"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid2.DataSource = rs
  Set mainform.Adodc2.Recordset = rs
  mainform.Adodc2.Caption = "当前记录为：" & mainform.Adodc2.Recordset.AbsolutePosition & "/" & mainform.Adodc2.Recordset.RecordCount
End If
If mainform.SSTab1.Tab = 2 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 销售"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
    If add1 = 1 Then
    .addnew
    If xiaochuoform.Text1.Text = "" Then
    xiaochuoform.Text1.Text = lastid
    End If
    If xiaochuoform.Text2.Text = "" Then
    xiaochuoform.Text2.Text = 0
    End If
    If xiaochuoform.Text3.Text = "" Then
    xiaochuoform.Text3.Text = ""
    End If
    If xiaochuoform.Text4.Text = "" Then
    xiaochuoform.Text4.Text = ""
    End If
    If xiaochuoform.Text5.Text = "" Then
    xiaochuoform.Text5.Text = ""
    End If
    If xiaochuoform.Text6.Text = "" Then
    xiaochuoform.Text6.Text = 0
    End If
    If xiaochuoform.Text7.Text = "" Then
    xiaochuoform.Text7.Text = 0
    End If
    rs(0) = xiaochuoform.Text1.Text
    rs(1) = xiaochuoform.Text2.Text
    rs(2) = xiaochuoform.Text3.Text
    rs(3) = xiaochuoform.Text4.Text
    rs(4) = xiaochuoform.Text5.Text
    rs(5) = xiaochuoform.Text6.Text
    rs(6) = xiaochuoform.Text7.Text
    .Update
    add1 = 0
    var1 = 2
    End If
  End With
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 销售"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid3.DataSource = rs
  Set mainform.Adodc3.Recordset = rs
  mainform.Adodc3.Caption = "当前记录为：" & mainform.Adodc3.Recordset.AbsolutePosition & "/" & mainform.Adodc3.Recordset.RecordCount
End If
If mainform.SSTab1.Tab = 3 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 商场"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
    If add1 = 1 Then
    .addnew
    If xianchianform.Text1.Text = "" Then
    xianchianform.Text1.Text = lastid
    End If
    If xianchianform.Text2.Text = "" Then
    xianchianform.Text2.Text = ""
    End If
    If xianchianform.Text3.Text = "" Then
    xianchianform.Text3.Text = ""
    End If
    If xianchianform.Text4.Text = "" Then
    xianchianform.Text4.Text = ""
    End If
    If xianchianform.Text5.Text = "" Then
    xianchianform.Text5.Text = 0
    End If
    If xianchianform.Text6.Text = "" Then
    xianchianform.Text6.Text = ""
    End If
    rs(0) = xianchianform.Text1.Text
    rs(1) = xianchianform.Text2.Text
    rs(2) = xianchianform.Text3.Text
    rs(3) = xianchianform.Text4.Text
    rs(4) = xianchianform.Text5.Text
    rs(5) = xianchianform.Text6.Text
    .Update
    add1 = 0
    var1 = 3
    End If
  End With
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 商场"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockOptimistic
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid4.DataSource = rs
  Set mainform.Adodc4.Recordset = rs
  mainform.Adodc4.Caption = "当前记录为：" & mainform.Adodc4.Recordset.AbsolutePosition & "/" & mainform.Adodc4.Recordset.RecordCount
End If
End Sub

Sub findword()
If mainform.SSTab1.Tab = 0 Or sst = 1 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 厂家"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockReadOnly
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid1.DataSource = rs
  Set mainform.Adodc1.Recordset = rs
  lastid = (mainform.Adodc1.Recordset.RecordCount + 1)
  mainform.Adodc1.Caption = "当前记录为：" & mainform.Adodc1.Recordset.AbsolutePosition & "/" & mainform.Adodc1.Recordset.RecordCount
  With mainform.DataGrid1
    .Columns(0).Width = 1000
    .Columns(1).Width = 1300
    .Columns(2).Width = 2000
    .Columns(3).Width = 1000
    .Columns(4).Width = 1300
    .Columns(5).Width = 1200
  End With
  On Error Resume Next
  If wordfindform.Command1.Caption = "查　找" Then
    rs.MoveFirst
  Else
    If (var1 + 1) < rs.RecordCount Or (var1 + 1) = rs.RecordCount Then
      rs.Bookmark = var1 + 1
    End If
  End If
  Do While Not rs.EOF
    For intctr = 0 To rs.Fields.Count - 1
      For intj = 1 To Len(rs(intctr))
        If Mid(rs(intctr), intj, Len(wordfindform.Text1.Text)) = wordfindform.Text1.Text Then
          'rs(intctr).Value.SetFocus
          flag = 1
          wordfindform.Command1.Caption = "查找下一个"
          var1 = rs.Bookmark
          Exit Do
        End If
      Next
    Next
  rs.MoveNext
  Loop
  If rs.EOF Then
    wordfindform.Command1.Caption = "查　找"
    If flag = 0 Then
      MsgBox "记录没有发现", , "查询提示"
    Else
      MsgBox "已查找到最后", , "查询提示"
    End If
    rs.MoveFirst
  End If
End If
If mainform.SSTab1.Tab = 1 Or sst = 2 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 产品"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockReadOnly
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid2.DataSource = rs
  Set mainform.Adodc2.Recordset = rs
  lastid = (mainform.Adodc2.Recordset.RecordCount + 1)
  mainform.Adodc2.Caption = "当前记录为：" & mainform.Adodc2.Recordset.AbsolutePosition & "/" & mainform.Adodc2.Recordset.RecordCount
  With mainform.DataGrid2
    .Columns(0).Width = 1000
    .Columns(1).Width = 1300
    .Columns(2).Width = 2000
    .Columns(3).Width = 2000
    .Columns(4).Width = 1300
  End With
  On Error Resume Next
  If wordfindform.Command1.Caption = "查　找" Then
    rs.MoveFirst
  Else
    If (var1 + 1) < rs.RecordCount Or (var1 + 1) = rs.RecordCount Then
      rs.Bookmark = var1 + 1
    End If
  End If
  Do While Not rs.EOF
    For intctr = 0 To rs.Fields.Count - 1
      For intj = 1 To Len(rs(intctr))
        If Mid(rs(intctr), intj, Len(wordfindform.Text1.Text)) = wordfindform.Text1.Text Then
          flag = 1
          wordfindform.Command1.Caption = "查找下一个"
          var1 = rs.Bookmark
          Exit Do
        End If
      Next
    Next
  rs.MoveNext
  Loop
  If rs.EOF Then
    wordfindform.Command1.Caption = "查　找"
    If flag = 0 Then
      MsgBox "记录没有发现", , "查询提示"
    Else
      MsgBox "已查找到最后", , "查询提示"
    End If
    rs.MoveFirst
  End If
End If
If mainform.SSTab1.Tab = 2 Or sst = 3 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 销售"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockReadOnly
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid3.DataSource = rs
  Set mainform.Adodc3.Recordset = rs
  lastid = (mainform.Adodc3.Recordset.RecordCount + 1)
  mainform.Adodc3.Caption = "当前记录为：" & mainform.Adodc3.Recordset.AbsolutePosition & "/" & mainform.Adodc3.Recordset.RecordCount
  With mainform.DataGrid3
    .Columns(0).Width = 800
    .Columns(1).Width = 800
    .Columns(2).Width = 900
    .Columns(3).Width = 1800
    .Columns(4).Width = 1300
    .Columns(5).Width = 1000
    .Columns(6).Width = 1000
  End With
  On Error Resume Next
  If wordfindform.Command1.Caption = "查　找" Then
    rs.MoveFirst
  Else
    If (var1 + 1) < rs.RecordCount Or (var1 + 1) = rs.RecordCount Then
      rs.Bookmark = var1 + 1
    End If
  End If
  Do While Not rs.EOF
    For intctr = 0 To rs.Fields.Count - 1
      For intj = 1 To Len(rs(intctr))
        If Mid(rs(intctr), intj, Len(wordfindform.Text1.Text)) = wordfindform.Text1.Text Then
          flag = 1
          wordfindform.Command1.Caption = "查找下一个"
          var1 = rs.Bookmark
          Exit Do
        End If
      Next
    Next
  rs.MoveNext
  Loop
  If rs.EOF Then
    wordfindform.Command1.Caption = "查　找"
    If flag = 0 Then
      MsgBox "记录没有发现", , "查询提示"
    Else
      MsgBox "已查找到最后", , "查询提示"
    End If
    rs.MoveFirst
  End If
End If
If mainform.SSTab1.Tab = 3 Or sst = 4 Then
  strconn = "provider=microsoft.jet.oledb.4.0;data source=" & App.Path & "\供销信息.mdb"
  strsql = "select * from 商场"
  With rs
    If .State = adStateOpen Then
       .Close
    End If
    .ActiveConnection = strconn
    .Source = strsql
    .LockType = adLockReadOnly
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .Open
  End With
  Set mainform.DataGrid4.DataSource = rs
  Set mainform.Adodc4.Recordset = rs
  lastid = (mainform.Adodc4.Recordset.RecordCount + 1)
  mainform.Adodc4.Caption = "当前记录为：" & mainform.Adodc4.Recordset.AbsolutePosition & "/" & mainform.Adodc4.Recordset.RecordCount
  With mainform.DataGrid4
    .Columns(0).Width = 1000
    .Columns(1).Width = 1300
    .Columns(2).Width = 2000
    .Columns(3).Width = 1000
    .Columns(4).Width = 1300
    .Columns(5).Width = 1200
  End With
  On Error Resume Next
  If wordfindform.Command1.Caption = "查　找" Then
    rs.MoveFirst
  Else
    If (var1 + 1) < rs.RecordCount Or (var1 + 1) = rs.RecordCount Then
      rs.Bookmark = var1 + 1
    End If
  End If
  Do While Not rs.EOF
    For intctr = 0 To rs.Fields.Count - 1
      For intj = 1 To Len(rs(intctr))
        If Mid(rs(intctr), intj, Len(wordfindform.Text1.Text)) = wordfindform.Text1.Text Then
          flag = 1
          wordfindform.Command1.Caption = "查找下一个"
          var1 = rs.Bookmark
          Exit Do
        End If
      Next
    Next
  rs.MoveNext
  Loop
  If rs.EOF Then
    wordfindform.Command1.Caption = "查　找"
    If flag = 0 Then
      MsgBox "记录没有发现", , "查询提示"
    Else
      MsgBox "已查找到最后", , "查询提示"
    End If
    rs.MoveFirst
  End If
End If
End Sub