kumanage.frm

VB+SQL开发的管理系统软件,希望能对学习写软件工程文档的同学们有所帮助 转载的新云软件的程序

      Top             =   5280
      Width           =   3615
   End
   Begin VB.Label Label2 
      BackStyle       =   0  'Transparent
      Caption         =   "库存成本："
      ForeColor       =   &H000000FF&
      Height          =   255
      Left            =   2280
      TabIndex        =   2
      Top             =   5280
      Width           =   2895
   End
   Begin VB.Label Label1 
      BackStyle       =   0  'Transparent
      Caption         =   "总计数量："
      ForeColor       =   &H000000FF&
      Height          =   255
      Left            =   360
      TabIndex        =   1
      Top             =   5280
      Width           =   1815
   End
End
Attribute VB_Name = "kumanage"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Private Sub Form_Load()
Grid1.SetRegisterInformation "CNwinndy", "W]vyY-nonvk-u\nty-Zbl_e-`hms^" '进行注册
Grid1.Column(0).Width = 50
Grid1.Column(3).Width = 115
Grid1.Column(4).Width = 100
Grid1.Column(8).Width = 100
Grid1.Column(9).Width = 100
For i = 1 To 4
Grid1.Column(i).Locked = True
Next
Grid1.Column(10).Locked = True
Grid1.BackColorFixed = RGB(124, 251, 181)
Grid2.BackColorFixed = RGB(124, 251, 181)
Set yx2 = cnn.Execute("select sum(数量) from 库存")
Label1.Caption = "总计数量：" & yx2.Fields(0) & "件"
Set yx2 = cnn.Execute("select sum(数量*进货价格) from 库存")
Label2.Caption = "库存成本：" & yx2.Fields(0) & "元"
Set yx2 = cnn.Execute("select sum(数量*售出价格) from 库存")
Label3.Caption = "按售出价格总计：" & yx2.Fields(0) & "元"
yxsql = "select * from 库存"
yxnumber = 10 '设定库存表中有多少个字段，在这里使用变量是防止以后程序修改后只需要修改
              '这个关键字就可以了
Grid1.Cols = yxnumber + 1
Set yx2 = cnn.Execute(yxsql) '执行某一SQL语句，并将结果返回到记录集YX2
For i = 1 To Grid1.Cols - 1
Grid1.Cell(0, i).Text = yx2.Fields(i - 1).Name
Next
yxkusee
'********************
Grid2.AllowUserResizing = False
Grid2.DisplayFocusRect = False '改变输入模式为平面
Grid2.Column(0).Width = 0
Grid2.Column(2).Width = 100
Grid2.Column(4).Width = 108
Grid2.Column(5).Width = 200
Grid2.Column(1).CellType = cellComboBox
Grid2.Column(2).CellType = cellComboBox
Grid2.Column(3).CellType = cellComboBox
Grid2.Cell(0, 1).Text = "查询模式" '将表格内添入固定内容
Grid2.Cell(0, 2).Text = "查询段"
Grid2.Cell(0, 3).Text = "条件"
Grid2.Cell(0, 4).Text = "关键字"
Grid2.Cell(0, 5).Text = "附加条件"
Grid2.ComboBox(1).AddItem "精确查询" '这里是类似combo框的操作，也是把内容添入combo控件的记录集
Grid2.ComboBox(1).AddItem "模糊查询"
Set yx2 = cnn.Execute("select * from 库存")
For i = 1 To Grid1.Cols - 1
Grid2.ComboBox(2).AddItem yx2.Fields(i - 1).Name
Next

'********************
XPFrame1.Top = Me.Height
End Sub
Private Sub yxkusee() '这里是通过引入的SQL语句来执行数据的显示
Set yx2 = cnn.Execute(yxsql)
Do While Not yx2.EOF '当YX2记录集未到结尾时执行
Grid1.Rows = Grid1.Rows + 1
Grid1.Cell(Grid1.Rows - 1, 0).Text = Grid1.Rows - 1
 For j = 1 To Grid1.Cols - 1 '设定读取列
 If Not yx2.Fields(j - 1) Is Nothing Then '空值的处理
  Grid1.Cell(Grid1.Rows - 1, j).Text = yx2.Fields(j - 1)
    If Mid(Grid1.Cell(Grid1.Rows - 1, j).Text, 1, 1) = "." Then
    Grid1.Cell(Grid1.Rows - 1, j).Text = "0" & Grid1.Cell(Grid1.Rows - 1, j).Text
   End If
 Else
 Grid1.Cell(Grid1.Rows - 1, j).Text = ""
 End If
 Next
 yx2.MoveNext
Loop
End Sub


Private Sub Form_Unload(Cancel As Integer)
Formmain.WindowState = 0
End Sub

Private Sub Grid1_RowColChange(ByVal Row As Long, ByVal Col As Long)
hang = Row
 If Grid1.Rows <> 1 Then
  Grid1.Range(1, 1, Grid1.Rows - 1, Grid1.Cols - 1).BackColor = vbWhite
  Grid1.Range(Row, 1, Row, Grid1.Cols - 1).BackColor = RGB(90, 158, 214)
 End If
End Sub

Private Sub Grid2_RowColChange(ByVal Row As Long, ByVal Col As Long)
If Grid2.Cell(1, 1).Text <> "" Then
If Grid2.Cell(1, 1).Text = "精确查询" Then
Grid2.ComboBox(3).Clear '以下清除表格中combo集中的内容,并加入以下内容
Grid2.ComboBox(3).AddItem "="
Grid2.ComboBox(3).AddItem ">"
Grid2.ComboBox(3).AddItem "<"
Grid2.ComboBox(3).AddItem ">="
Grid2.ComboBox(3).AddItem "<="
Grid2.ComboBox(3).AddItem "<>"
Grid2.ComboBox(2).Clear '先清空记录集
Set yx2 = cnn.Execute("select * from 库存")
For i = 1 To Grid1.Cols - 1
Grid2.ComboBox(2).AddItem yx2.Fields(i - 1).Name
Next
Else
Grid2.ComboBox(3).Clear
Grid2.ComboBox(3).AddItem "like"
Grid2.ComboBox(2).Clear
Set yx2 = cnn.Execute("select * from 库存")
For i = 1 To Grid1.Cols - 1
 Select Case i '这里使用case语句是因为模糊查询中数值型是不支持的
   Case 1, 2, 3, 4, 10
   Grid2.ComboBox(2).AddItem yx2.Fields(i - 1).Name
 End Select
Next
End If
End If
End Sub

Private Sub XPButton1_Click()
If hang = 0 Then '当鼠标指向行为0时退出操作,因为0行无数据
Exit Sub
End If
 '*********组织修改库存语句***********
 yxsql1 = "update 库存 set "
 For j = 5 To 8 '这里是组织修改语句的set部分
 yxsql1 = yxsql1 & Grid1.Cell(0, j).Text & "=" & Grid1.Cell(hang, j).Text & ","
 Next
  yxsql1 = yxsql1 & Grid1.Cell(0, 9).Text & "=" & Grid1.Cell(hang, 9).Text & " where "
 For j = 1 To 3
  yxsql1 = yxsql1 & Grid1.Cell(0, j).Text & "='" & Grid1.Cell(hang, j).Text & "' and "
 Next
'因为第9和第10单元格的类型是货币型，所以不能使用单引号
   yxsql1 = yxsql1 & Grid1.Cell(0, 4).Text & "='" & Grid1.Cell(hang, 4).Text & "'"
 Set yx2 = cnn.Execute(yxsql1)
 MsgBox "修改完成!"
 '************************************
End Sub

Private Sub XPButton2_Click()
yxsql1 = "delete from 库存 where "
 For j = 1 To 4 '这里是符合条件的字符型1-4字段
  yxsql1 = yxsql1 & Grid1.Cell(0, j).Text & "='" & Grid1.Cell(hang, j).Text & "' and "
 Next
 For j = 5 To 8 '这里是符合条件的数值型5-8
 yxsql1 = yxsql1 & Grid1.Cell(0, j).Text & "=" & Grid1.Cell(hang, j).Text & " and "
 Next
  yxsql1 = yxsql1 & Grid1.Cell(0, 9).Text & "=" & Grid1.Cell(hang, 9).Text
 Set yx2 = cnn.Execute(yxsql1)
 MsgBox "该条数据已删除,数据将自动刷新!"
 Call XPButton4_Click
End Sub

Private Sub XPButton3_Click()
If XPFrame1.Top < 0 Then
XPFrame1.Top = Me.Height
Exit Sub
End If
For j = Me.Height To 6360 Step -1
XPFrame1.Top = XPFrame1.Top - 1
Next
End Sub

Private Sub XPButton4_Click() '刷新数据
Grid1.Rows = 1
yxsql = "select * from 库存"
yxkusee
End Sub

Private Sub XPButton5_Click()
On Error GoTo finish '防错代码，防止用户组织语句的错误或其它不可预见的错误发生
If Grid2.Cell(1, 1).Text <> "" And Grid2.Cell(1, 2).Text <> "" And Grid2.Cell(1, 3).Text <> "" And Grid2.Cell(1, 4).Text <> "" Then
    If Grid2.Cell(1, 1).Text = "精确查询" Then
      Select Case Grid2.Cell(1, 3).Text '处理数值型的查询组织语句
      Case "数量", "单价", "进货价格", "售出价格", "总价格"
      yxsql = "select * from 库存 where " & Grid2.Cell(1, 2).Text & Grid2.Cell(1, 3).Text & Grid2.Cell(1, 4).Text
      Case Else
     yxsql = "select * from 库存 where " & Grid2.Cell(1, 2).Text & Grid2.Cell(1, 3).Text & "'" & Grid2.Cell(1, 4).Text & "'"
     End Select
    Else
    yxsql = "select * from 库存 where " & Grid2.Cell(1, 2).Text & " like '%" & Grid2.Cell(1, 4).Text & "%'"
    End If
    If Grid2.Cell(1, 5).Text <> "" Then
    yxsql = yxsql & " " & Grid2.Cell(1, 5).Text
    End If
  Grid1.Rows = 1
  yxkusee
Else
MsgBox "查询时关键组成部分不可以出现空格！"
End If
Exit Sub
finish:
  MsgBox Err.Description
End Sub

Private Sub XPButton6_Click()
XPFrame1.Top = Me.Height
End Sub

Private Sub XPButton7_Click()
Unload Me
ku911.Show 1
End Sub