VB机房收费系统07——组合查询

前言

我们以“学生基本信息维护”窗体为例，介绍下组合查询

正文

如图，首先让我们来理解下组合查询是如何实现的。

我们把第一排的前三个文本框看作第一个限定条件。

例如"框1"选择"卡号"，“框2”选择“=”，“框3”选择"2",这三个框组成了第一个限定条件。

“卡号=2”

当我们只写一个限定条件时，是不需要组合关系框的。

但当我们填写了第一排和第二排后，两个条件共同筛选信息，这是就需要选择组合关系“与”还是“或”。这就是组合查询。

但我们需要搞清楚，如何依据选择限定条件的个数，让“与”“或”分别生效。

完整代码

Private Sub Form_Load()
'添加下拉框信息
    Combo1.AddItem "卡号"
    Combo1.AddItem "学号"
    Combo1.AddItem "姓名"
    Combo1.AddItem "性别"
    Combo1.AddItem "系别"
    Combo1.AddItem "年级"
    Combo1.AddItem "班级"
    
    Combo2.AddItem "卡号"
    Combo2.AddItem "学号"
    Combo2.AddItem "姓名"
    Combo2.AddItem "性别"
    Combo2.AddItem "系别"
    Combo2.AddItem "年级"
    Combo2.AddItem "班级"
    
    Combo3.AddItem "卡号"
    Combo3.AddItem "学号"
    Combo3.AddItem "姓名"
    Combo3.AddItem "性别"
    Combo3.AddItem "系别"
    Combo3.AddItem "年级"
    Combo3.AddItem "班级"
    
    Combo4.AddItem "<"
    Combo4.AddItem ">"
    Combo4.AddItem "="
    Combo4.AddItem "<>"
    
    Combo5.AddItem "<"
    Combo5.AddItem ">"
    Combo5.AddItem "="
    Combo5.AddItem "<>"
    
    Combo6.AddItem "<"
    Combo6.AddItem ">"
    Combo6.AddItem "="
    Combo6.AddItem "<>"
    
    Combo7.AddItem "与"
    Combo7.AddItem "或"
    
    Combo8.AddItem "与"
    Combo8.AddItem "或"
    
    
    

'如果组合查询框控件为空，不能选择如下查询条件
     If Combo7.Text = "" Then

        Combo2.Enabled = False
        Combo3.Enabled = False
        Combo5.Enabled = False
        Combo6.Enabled = False
        Text2.Enabled = False
        Text3.Enabled = False
        Combo8.Enabled = False
     End If

     If Combo8.Text = "" Then

        Combo3.Enabled = False
        Combo6.Enabled = False
        Text3.Enabled = False
     End If
     

    
End Sub





'激活组合控件
Private Sub Timer1_Timer()
  If Combo7.Text <> "" Then
        Combo2.Enabled = True
        Combo5.Enabled = True
        Combo8.Enabled = True
        Text2.Enabled = True
    End If
End Sub

Private Sub Timer2_Timer()
If Combo8.Text <> Trim("") Then
        Combo3.Enabled = True
        Combo6.Enabled = True
        Text3.Enabled = True
    End If
End Sub



'组合查询
Private Sub cmdinquire_Click()
    Dim mrc As ADODB.Recordset
    Dim txtsql As String
    Dim msgtext As String
    Dim fieldname, fieldname2, fieldname3, fieldname4, fieldname5
    
'第一行
'把人话转换成机器话

    Select Case Combo1.Text
        Case "卡号"
            fieldname = "cardno"
        Case "学号"
            fieldname = "studentNo"
        Case "姓名"
            fieldname = "studentName"
        Case "性别"
            fieldname = "sex"
        Case "系别"
            fieldname = "department"
        Case "年级"
            fieldname = "grade"
        Case "班级"
            fieldname = "class"
     End Select
     
     
'判断查询项是否未选择

    If Trim(Combo1.Text = "") Or Trim(Combo4.Text = "") Or Trim(Text1.Text = "") Then
       MsgBox "请填写完整的查询条件!", vbonly + vbExclamation, "提示"
        Exit Sub
    
    Else
    
        txtsql = "select * from student_Info where " & fieldname & Combo4.Text & "'" & Text1.Text & "'"
        Set mrc = ExecuteSQL(txtsql, msgtext)
        
    If mrc.EOF = True Then
        MsgBox "该条件的数据不存在!", vbOKOnly + vbExclamation, "提示"
    Exit Sub

        
End If
End If





'第二行
''组合查询框判断
    If Combo7 = Trim("") Then
        Combo2.Enabled = False
        Combo3.Enabled = False
        Combo5.Enabled = False
        Combo6.Enabled = False
        Combo8.Enabled = False
        Text2.Enabled = False
        Text3.Enabled = False
    Else
    
        Select Case Combo7.Text
        
            Case "与"
                fieldname2 = "and"
            Case "或"
                fieldname2 = "or"
        End Select
        
        
    Select Case Combo2.Text
        Case "卡号"
            fieldname3 = "cardno"
        Case "学号"
            fieldname3 = "studentNo"
        Case "姓名"
            fieldname3 = "studentName"
        Case "性别"
            fieldname3 = "sex"
        Case "系别"
            fieldname3 = "department"
        Case "年级"
            fieldname3 = "grade"
        Case "班级"
            fieldname3 = "class"
     End Select
                
'判断查询项是否未选择

    If Trim(Combo2.Text = "") Or Trim(Combo5.Text = "") Or Trim(Text2.Text = "") Then
       MsgBox "请填写完整的查询条件!", vbonly + vbExclamation, "提示"
        Exit Sub
    Else
        txtsql = txtsql & fieldname2 & " " & fieldname3 & Combo5.Text & "'" & Text2.Text & "'"
        Set mrc = ExecuteSQL(txtsql, msgtext)
        
    If mrc.EOF = True Then
        MsgBox "该条件的数据不存在!", vbonly + vbExclamation, "提示"
        Exit Sub
 End If
 End If
 End If
                
                
'第三行
    If Trim(Combo8.Text) = "" Then
        Combo3.Enabled = False
        Combo6.Enabled = False
        Text3.Enabled = False
'显示上机信息

    
    Else
    
        Select Case Combo8.Text
        
            Case "与"
                fieldname4 = "and"
            Case "或"
                fieldname4 = "or"
        End Select
                
                
        Select Case Combo3.Text
            Case "卡号"
                fieldname5 = "cardno"
            Case "学号"
                fieldname5 = "studentNo"
            Case "姓名"
                fieldname5 = "studentName"
            Case "性别"
                fieldname5 = "sex"
            Case "系别"
                fieldname5 = "department"
            Case "年级"
                fieldname5 = "grade"
            Case "班级"
                fieldname5 = "class"
     End Select
                
                
                
    If Trim(Combo3.Text = "") Or Trim(Combo6.Text = "") Or Trim(Text3.Text = "") Then
       MsgBox "请填写完整的查询条件!", vbonly + vbExclamation, "提示"
        Exit Sub
    Else
        txtsql = txtsql & fieldname4 & " " & fieldname5 & Combo6.Text & "'" & Text3.Text & "'"
        Set mrc = ExecuteSQL(txtsql, msgtext)
        
    If mrc.EOF = True Then
        MsgBox "该条件的数据不存在!", vbonly + vbExclamation, "提示"
        Exit Sub
End If
End If
End If

                
                
'显示上机信息

    With MSFlexGrid1
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0, 0) = "卡号"
        .TextMatrix(0, 1) = "姓名"
        .TextMatrix(0, 2) = "性别"
        .TextMatrix(0, 3) = "系别"
        .TextMatrix(0, 4) = "年级"
        .TextMatrix(0, 5) = "班级"
        .TextMatrix(0, 6) = "余额"
        .TextMatrix(0, 7) = "备注"
        .TextMatrix(0, 8) = "注册教师"
        .TextMatrix(0, 9) = "状态"
        .TextMatrix(0, 10) = "结账"
        .TextMatrix(0, 11) = "注册日期"
        .TextMatrix(0, 12) = "注册时间"
        .TextMatrix(0, 13) = "用户类型"
        .TextMatrix(0, 14) = "学号"
        
    Do While Not mrc.EOF
        .Rows = .Rows + 1
        .CellAlignment = 4
        .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(0))
        .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))
        .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(3))
        .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(4))
        .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(5))
        .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(6))
        .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(7))
        .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(8))
        .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(9))
        .TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(10))
        .TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(11))
        .TextMatrix(.Rows - 1, 11) = Trim(mrc.Fields(12))
        .TextMatrix(.Rows - 1, 12) = Trim(mrc.Fields(13))
        .TextMatrix(.Rows - 1, 13) = Trim(mrc.Fields(14))
        .TextMatrix(.Rows - 1, 14) = Trim(mrc.Fields(1)) & ""
        mrc.MoveNext
    Loop
    End With
                

End Sub


Private Sub cmdempty_Click()
'清空选项
    Combo1.Text = ""
    Combo2.Text = ""
    Combo3.Text = ""
    Combo4.Text = ""
    Combo5.Text = ""
    Combo6.Text = ""
    Combo7.Text = ""
    Combo8.Text = ""
    Text1.Text = ""
    Text2.Text = ""
    Text3.Text = ""
    
'清空上机信息

    With MSFlexGrid1
        .Rows = 1
        .CellAlignment = 4
        .TextMatrix(0, 0) = "卡号"
        .TextMatrix(0, 1) = "姓名"
        .TextMatrix(0, 2) = "性别"
        .TextMatrix(0, 3) = "系别"
        .TextMatrix(0, 4) = "年级"
        .TextMatrix(0, 5) = "班级"
        .TextMatrix(0, 6) = "余额"
        .TextMatrix(0, 7) = "备注"
        .TextMatrix(0, 8) = "注册教师"
        .TextMatrix(0, 9) = "状态"
        .TextMatrix(0, 10) = "结账"
        .TextMatrix(0, 11) = "注册日期"
        .TextMatrix(0, 12) = "注册时间"
        .TextMatrix(0, 13) = "用户类型"
        .TextMatrix(0, 14) = "学号"
    End With
                

End Sub

Private Sub cmdexit_Click()
'退出
    Unload Me
End Sub
Private Sub cmdmodify_Click()



'显示选中列的信息
    Dim txtsql As String
    Dim msgtext As String
    Dim mrc As ADODB.Recordset
    

With MSFlexGrid1

    studentinfomodify.cardno.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 0)
    studentinfomodify.studentname.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 1)
    studentinfomodify.combosex.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 8)
    studentinfomodify.dept.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 5)
    studentinfomodify.grade.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 6)
    studentinfomodify.class.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 7)
    studentinfomodify.studentno.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 3)
    studentinfomodify.money.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 4)
    studentinfomodify.status.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 9)
    studentinfomodify.information.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 10)
    studentinfomodify.combostyle.Text = MSFlexGrid1.TextMatrix(MSFlexGrid1.Row, 13)
    
 
    Unload Me


'弹出修改窗体
    studentinfomodify.Show
End With
End Sub

结语

我的代码有些繁琐之处，聪明的你能发现更简洁的表示代码吗？