vb复合查询

       在敲《机房收费系统》的时候，我们遇到了一个组合查询，经过不懈努力，终于做出来了，现把思路和代码总结如下：

       

       首先是组合框列表项的载入，在Form_Load中添加如下代码：

    For intIndex = 0 To 2        cboFields(intIndex).AddItem "教师"        cboFields(intIndex).AddItem "注册日期"        cboFields(intIndex).AddItem "注册时间"        cboFields(intIndex).AddItem "注销日期"        cboFields(intIndex).AddItem "注销时间"        cboFields(intIndex).AddItem "机器名"        cboFields(intIndex).ListIndex = 0                cboOperator(intIndex).AddItem "="        cboOperator(intIndex).AddItem "<"        cboOperator(intIndex).AddItem ">"        cboOperator(intIndex).AddItem "<>"        cboOperator(intIndex).ListIndex = 0    Next intIndex        For intIndex = 0 To 1        cboRelation(intIndex).AddItem ""        cboRelation(intIndex).AddItem "或"        cboRelation(intIndex).AddItem "与"        cboRelation(intIndex).ListIndex = 0    Next intIndex

       这里运用了控件数组，载入列表项时再用for循环，大大减少代码量。因为这些列表在查询的时候必然会选择一项内容，所以通过ListIndex属性让组合框默认显示一项，这样既可以节省部分用户时间，也避免了对未选择列表项的处理。

       下面是“查询”的代码：

Private Sub cmdInquiry_Click()    Dim txtSQL As String    Dim MsgText As String    Dim mrc As ADODB.Recordset        '字段和关系    Dim strFields(0 To 2) As String    Dim strRelation(0 To 1) As String        '当选择不同的字段时，赋给变量相应的数据库字段名    For intIndex = 0 To 2        Select Case cboFields(intIndex).Text            Case "教师"                strFields(intIndex) = "UserID"            Case "注册日期"                strFields(intIndex) = "LoginDate"            Case "注册时间"                strFields(intIndex) = "LoginTime"            Case "注销日期"                strFields(intIndex) = "LogoutDate"            Case "注销时间"                strFields(intIndex) = "LogoutTime"            Case "机器名"                strFields(intIndex) = "computer"        End Select    Next intIndex        '关系    For intIndex = 0 To 1        Select Case cboRelation(intIndex).Text            Case "或"                strRelation(intIndex) = "or"            Case "与"                strRelation(intIndex) = "and"        End Select    Next intIndex        '防止查询条件为空    If txtContent1.Text = "" Then        MsgBox "请输入要查询的内容！", vbOKOnly + vbInformation, "提示"        txtContent1.SetFocus        Exit Sub    End If        '如果只有一个查询条件    If cboRelation(0).Text = "" And cboRelation(1).Text = "" Then        txtSQL = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'"        Set mrc = ExecuteSQL(txtSQL, MsgText)    End If        '如果为一二组合查询    If cboRelation(0).Text <> "" And cboRelation(1).Text = "" Then            '防止查询条件为空        If txtContent2.Text = "" Then            MsgBox "请输入要查询的内容！", vbOKOnly + vbInformation, "提示"            txtContent2.SetFocus            Exit Sub        End If        txtSQL = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'" _                       & " " & strRelation(0) & " " & strFields(1) & cboOperator(1).Text & "'" & Trim(txtContent2.Text) & "'"        Set mrc = ExecuteSQL(txtSQL, MsgText)    End If        '如果为一二三组合查询    If cboRelation(0).Text <> "" And cboRelation(1).Text <> "" Then            '防止查询条件为空        If txtContent2.Text = "" Then            MsgBox "请输入要查询的内容！", vbOKOnly + vbInformation, "提示"            txtContent2.SetFocus            Exit Sub        End If                If txtContent3.Text = "" Then            MsgBox "请输入要查询的内容！", vbOKOnly + vbInformation, "提示"            txtContent3.SetFocus            Exit Sub        End If        txtSQL = "select * from worklog_Info where " & strFields(0) & cboOperator(0).Text & "'" & Trim(txtContent1.Text) & "'" _                       & " " & strRelation(0) & " " & strFields(1) & cboOperator(1).Text & "'" & Trim(txtContent2.Text) & "'" _                       & " " & strRelation(1) & " " & strFields(2) & cboOperator(2).Text & "'" & Trim(txtContent3.Text) & "'"        Set mrc = ExecuteSQL(txtSQL, MsgText)    End If        '如果用户选择了组合关系2没选组合关系1，提示用户    If cboRelation(0).Text = "" And cboRelation(1).Text <> "" Then        MsgBox "请选择组合关系！", vbOKOnly + vbInformation, "提示"        cboRelation(0).SetFocus        Exit Sub    End If        With MSHFlexGrid1        .Rows = 1        .Cols = 8        .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) = "机器名"                Do While Not mrc.EOF            .Rows = .Rows + 1            .CellAlignment = 4            .TextMatrix(.Rows - 1, 0) = mrc!serial            .TextMatrix(.Rows - 1, 1) = mrc!UserID            .TextMatrix(.Rows - 1, 2) = mrc!Level            .TextMatrix(.Rows - 1, 3) = mrc!LoginDate            .TextMatrix(.Rows - 1, 4) = mrc!LoginTime            If IsNull(mrc.Fields("LogoutDate")) Then                .TextMatrix(.Rows - 1, 5) = ""            Else                .TextMatrix(.Rows - 1, 5) = mrc!LogoutDate            End If            If IsNull(mrc.Fields("LogoutTime")) Then                .TextMatrix(.Rows - 1, 6) = ""            Else                .TextMatrix(.Rows - 1, 6) = mrc!LogoutTime            End If            .TextMatrix(.Rows - 1, 7) = mrc!computer            mrc.MoveNext        Loop    End With        mrc.CloseEnd Sub

       这里只是实现了查询的功能，但是假如用户不按顺序，直接在最后一行输入查询内容，不选组合方式，程序不会得到查询结果，也不会进行任何提示，这里仅仅展示查询的逻辑过程，细节方面还需要读者自行完善。

初学者，不当之处敬请批评指正！

欢迎光临我的网易博客：http://blog.163.com/liu_xiaochun/