【机房收费系统】组合查询

机房组合查询我感觉是在这上面时间最多的一个，不是太难但就是有很多的不容易让人想到的地方。下面我就来总结一下。

     1、查询条件strA单独查询，组合关系comboZh1符合的时候strA和StrB一起查询，组合关系comboZh2符合的时候strA、StrB、StrC三行条件一起查询。

     2、当字段名是性别的时候操作符中的符号只能是“=”和“< >”并且提示只能输入“男” 或“女”。当字段名为时间或者是日期的时候，txtYc1要有限制只能输入日期或者是时间

3、组合查询ComboZh1为空的时候StrB不能用或者是隐藏。只有单击ComboZh1的时候ComboZh2才能用。否则无效。（这个想着很是费劲，一定要好好的思考。。。）

接下来就是展示一下我的代码。

先应该在新建的模块中写到（这会为你省掉很多的麻烦。。。）

<span style="font-size:14px;">Option Explicit

Public Function FiledName(strFiledName As String) As String
'将字段名转化成数据库表中的字段名
    Select Case strFiledName
        Case "卡号"
            FiledName = "cardno"
        Case "学号"
            FiledName = "studentNo"
        Case "姓名"
            FiledName = "studentName"
        Case "性别"
            FiledName = "sex"
        Case "系别"
            FiledName = "department"
        Case "年级"
            FiledName = "grade"
        Case "班级"
            FiledName = "class"
        
    End Select
End Function

'将操作符转化成数据库表中的字段名
Public Function Operate(strOperate As String)
   Select Case strOperate
      Case "="
         Operate = "="
      Case "<"
         Operate = "<"
      Case ">"
         Operate = ">"
      Case "<>"
         Operate = "<>"
   End Select
End Function

'将连接符转化成数据库表中的字段名
Public Function Connect(strConnect As String) As String
    Select Case strConnect
       Case "或"
          Connect = "or"
       Case "与"
          Connect = "and"
       Case ""
          Connect = ""
    End Select
End Function

Function chkkey(t As String, k As Integer) As Integer   '只能输入数字
    chkkey = k
    If k = 46 And InStr(t, ".") = 0 Then
        Exit Function
    End If
    If k = 8 Then
        Exit Function
    End If
    If k < 48 Or k > 57 Then
        chkkey = 0
    End If
    
    
End Function</span>

<span style="font-size:14px; font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);"><span style="white-space:pre">	<span style="font-size:14px; white-space: pre;">之后</span></span>就是要在窗体中写你的代码了。</span>

<span style="font-size:14px;">Option Explicit
Dim nowrow As Integer

Private Sub ComboZd1_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd1.Text
       Case "性别"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem "<>"
       Case "姓名"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem "<>"
        Case "系别"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem "<>"
        Case "卡号"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
        Case "学号"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
        Case "年级"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
        Case "班级"
            ComboOpt1.Clear
            ComboOpt1.AddItem "="
            ComboOpt1.AddItem ">"
            ComboOpt1.AddItem "<"
            ComboOpt1.AddItem "<>"
         Case ""
            ComboOpt1.Clear
            ComboOpt1.AddItem ""
End Select
'性别选择男女
If ComboZd1.Text = "性别" Then
    MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
End If
End Sub
Private Sub ComboZd2_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd2.Text
       Case "性别"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem "<>"
       Case "姓名"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem "<>"
        Case "系别"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem "<>"
        Case "卡号"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case "学号"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case "年级"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case "班级"
            ComboOpt2.Clear
            ComboOpt2.AddItem "="
            ComboOpt2.AddItem ">"
            ComboOpt2.AddItem "<"
            ComboOpt2.AddItem "<>"
        Case ""
            ComboOpt2.Clear
            ComboOpt2.AddItem ""
End Select
'性别选择男女
If ComboZd2.Text = "性别" Then
    MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
End If
End Sub
Private Sub ComboZd3_Click()
'选择是性别、姓名、系别的时候 操作符添加的是 = 和<>
''选择是卡号、学号、年级、班级 的时候 操作符添加的是 = 、<> 、>、<
Select Case ComboZd3.Text
       Case "性别"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem "<>"
       Case "姓名"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem "<>"
        Case "系别"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem "<>"
        Case "卡号"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case "学号"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case "年级"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case "班级"
            ComboOpt3.Clear
            ComboOpt3.AddItem "="
            ComboOpt3.AddItem ">"
            ComboOpt3.AddItem "<"
            ComboOpt3.AddItem "<>"
        Case ""
            ComboOpt3.Clear
            ComboOpt3.AddItem ""
End Select
'性别选择男女
If ComboZd3.Text = "性别" Then
    MsgBox " 请在 “要查询的内容” 中添加'男'或'女' ", vbOKOnly + vbExclamation, "提示"
End If
End Sub
Private Sub ComboZh1_Click()
'组合关系是1个时候第2行的选择显示
If ComboZh1.Text <> "" Then
   ComboZd2.Enabled = True
   ComboOpt2.Enabled = True
   txtYc2.Enabled = True
   ComboZh2.Enabled = True
Else
    ComboZd2.Enabled = False
    ComboOpt2.Enabled = False
    txtYc2.Enabled = False
    ComboZh2.Enabled = False
    ComboZd3.Enabled = False
   ComboOpt3.Enabled = False
   txtYc3.Enabled = False
    txtYc2.Text = ""
    txtYc3.Text = ""
End If


End Sub


Private Sub ComboZh2_Click()
'组合关系是2个时候第3行的选择显示
If ComboZh2.Text <> "" Then
   ComboZd3.Enabled = True
   ComboOpt3.Enabled = True
   txtYc3.Enabled = True
End If
End Sub
te Sub Command1_Click()
'Command1是清空
    ComboOpt1.Text = ""
    ComboOpt2.Text = ""
    ComboOpt3.Text = ""
    ComboZd1.Text = ""
    ComboZd2.Text = ""
    ComboZd3.Text = ""
    ComboZh1.Text = ""
    ComboZh2.Text = ""
    txtYc1.Text = ""
    txtYc2.Text = ""
    MSHFGd.Clear
   
End Sub

Private Sub Command2_Click()
'Command2是查询
    Dim txtSQl As String
    Dim MsgText As String
    Dim mrc As ADODB.Recordset    'mrc是student数据库
    Dim strA As String   '定义三个保存SQL语句的字符串 代表第一种查询条件
    Dim strB As String  '代表第二种
    Dim strC As String  '代表第三种
    
    txtSQl = "select * from student_Info where "
 
    
    strA = txtSQl & FiledName(ComboZd1.Text) & Operate(ComboOpt1.Text) & "'" & Trim(txtYc1.Text) & "'"
    strB = strA & "" & Connect(ComboZh1.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt2.Text) & "'" & Trim(txtYc2.Text) & "'"
    strC = strB & Connect(ComboZh2.Text) & " " & FiledName(ComboZd2.Text) & Operate(ComboOpt3.Text) & "'" & Trim(txtYc3.Text) & "'"

    If Trim(ComboZh1.Text) = "" Then   '没有组合关系的时候就是一行查询条件
        If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Then
            MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            txtSQl = strA
        Set mrc = ExecuteSQL(txtSQl, MsgText)
        End If
    End If
    
    If Trim(ComboZh1.Text) <> "" Then  '有组合关系的时候是两行和起来的查询条件
        If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Then
            MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            txtSQl = strB
            Set mrc = ExecuteSQL(txtSQl, MsgText)
        End If
    End If
    
    If Trim(ComboZh2.Text) <> "" Then   '两个组合关系时候的查询条件 三行查询条件一起
        If Trim(ComboZd1.Text) = "" Or Trim(ComboOpt1.Text) = "" Or Trim(txtYc1.Text) = "" Or Trim(ComboZd2.Text) = "" Or Trim(ComboOpt2.Text) = "" Or Trim(txtYc2.Text) = "" Or Trim(ComboZd3.Text) = "" Or Trim(ComboOpt3.Text) = "" Or Trim(txtYc3.Text) = "" Then
            MsgBox "请输入完整的查询条件", vbOKOnly + vbExclamation, "提示"
            Exit Sub
        Else
            txtSQl = strC
            Set mrc = ExecuteSQL(txtSQl, MsgText)
        End If
    End If
    

    If mrc.EOF Then
        MSHFGd.Clear
        MsgBox "暂无查询结果", vbOKOnly + vbExclamation, "提示"
        Exit Sub
    End If
    
    With MSHFGd     '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
        .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) = "时间"
    
    
    Do While Not mrc.EOF
        .Rows = .Rows + 1   '防止空白行的出现
        .CellAlignment = 4
        .TextMatrix(.Rows - 1, 0) = Trim(mrc.Fields(1))   '与数据库进行对应
        .TextMatrix(.Rows - 1, 1) = Trim(mrc.Fields(2))
        .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(0))
        .TextMatrix(.Rows - 1, 3) = Trim(mrc.Fields(7))
        .TextMatrix(.Rows - 1, 4) = Trim(mrc.Fields(4))
        .TextMatrix(.Rows - 1, 5) = Trim(mrc.Fields(5))
        .TextMatrix(.Rows - 1, 6) = Trim(mrc.Fields(6))
        .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(3))
        .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(10))
        .TextMatrix(.Rows - 1, 9) = Trim(mrc.Fields(8))
        .TextMatrix(.Rows - 1, 10) = Trim(mrc.Fields(14))
        .TextMatrix(.Rows - 1, 11) = mrc.Fields(12) & ""
        .TextMatrix(.Rows - 1, 12) = mrc.Fields(13) & ""
        mrc.MoveNext
        Loop
        End With
        AdjustColWidth frmcxsjbxxwh, MSHFGd  '调节列宽
        mrc.Close
    
End Sub

Private Sub Command3_Click()
'Command3_是退出
  Unload Me
End Sub

Private Sub Command4_Click()
'修改信息
If MSHFGd.MouseRow = 0 Then
    MsgBox "请选择学生", vbOKOnly + vbExclamation, "警告"
Else
    frmcxsjbxxwh.Hide
    SetParent frmcModifySI.hWnd, frmmain.Picture1.hWnd   '‘显示修改学生信息窗体
    frmcModifySI.Width = 12180
    frmcModifySI.Height = 9255
 End If
End Sub


Private Sub Form_Activate()
'把当前不是活动的窗体最小化
Dim a As Form
For Each a In Forms
    If a.Name <> frmmain.Name And a.Name <> Me.Name Then
        a.WindowState = 1
    End If
Next
    
End Sub
Private Sub Form_Load()

    With MSHFGd      '表示的是设置MSHFlexGrid控件第i行，第j列的文本内容（注意标题算第一行）
        .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) = "时间"
        End With
    

    ComboZd1.AddItem "学号"     '添加字段名
    ComboZd1.AddItem "姓名"
    ComboZd1.AddItem "卡号"
    ComboZd1.AddItem "系别"
    ComboZd1.AddItem "年级"
    ComboZd1.AddItem "班级"
    ComboZd1.AddItem "性别"
    ComboZd1.AddItem ""

    ComboZd2.AddItem "学号"
    ComboZd2.AddItem "姓名"
    ComboZd2.AddItem "卡号"
    ComboZd2.AddItem "系别"
    ComboZd2.AddItem "年级"
    ComboZd2.AddItem "班级"
    ComboZd2.AddItem "性别"
      ComboZd2.AddItem ""
      
    ComboZd3.AddItem "学号"
    ComboZd3.AddItem "姓名"
    ComboZd3.AddItem "卡号"
    ComboZd3.AddItem "系别"
    ComboZd3.AddItem "年级"
    ComboZd3.AddItem "班级"
    ComboZd3.AddItem "性别"
      ComboZd3.AddItem ""

     
    ComboZh1.AddItem "或"
    ComboZh1.AddItem "与"
    ComboZh1.AddItem ""
    
    ComboZh2.AddItem "或"       '添加组合关系
    ComboZh2.AddItem "与"
    ComboZh2.AddItem ""
    
    ComboZd2.Enabled = False
    ComboZd3.Enabled = False
    ComboOpt2.Enabled = False
    ComboOpt3.Enabled = False
    txtYc2.Enabled = False
    txtYc3.Enabled = False
    ComboZh2.Enabled = False
End Sub

Public Sub MSHFgd_Mousedown(button As Integer, Shift As Integer, X As Single, Y As Single)
    With MSHFGd
    .Row = .MouseRow  '当前活动单元就是鼠标按下的单元
    nowrow = .Row   '把单元传给nowrow
    End With
End Sub
Public Sub MSHFgd_mouseup(button As Integer, Shift As Integer, X As Single, Y As Single)
    With MSHFGd
        .RowSel = nowrow
    End With
    
End Sub



Private Sub txtYc1_Click()
   txtYc1.SetFocus
End Sub


Private Sub txtYc2_Click()
   txtYc2.SetFocus
End Sub


Private Sub txtYc3_Click()
   txtYc3.SetFocus
End Sub

 
Private Sub txtYc1_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd1.Text = "卡号") Then
    KeyAscii = chkkey(txtYc1, KeyAscii)
Else
    If (ComboZd1.Text = "学号") Then
    KeyAscii = chkkey(txtYc1, KeyAscii)
End If
End If

End Sub
 
Private Sub txtYc2_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd2.Text = "卡号") Then
    KeyAscii = chkkey(txtYc2, KeyAscii)
Else
    If (ComboZd2.Text = "学号") Then
    KeyAscii = chkkey(txtYc2, KeyAscii)
End If
End If
End Sub
 
Private Sub txtYc3_KeyPress(KeyAscii As Integer)
'确定输入的是数字
If (ComboZd3.Text = "卡号") Then
    KeyAscii = chkkey(txtYc3, KeyAscii)
Else
    If (ComboZd3.Text = "学号") Then
    KeyAscii = chkkey(txtYc3, KeyAscii)
End If
End If

End Sub

</span>

好了这就是我的代码了。虽然过程很是艰辛但是结果还是很美好的。