機房收費系統之—組合查詢

Public Function Field(i As String) As String
 Select Case i
 Case "卡號"
 Field = "cardno"
 Case "姓名"
 Field = "studentname"
 Case "上機日期"
 Field = "ondate"
 Case "上機時間"
 Field = "ontime"
 Case "下機日期"
 .......
 End select
End Function

Private Sub cmdInqurie_Click()
 Dim ctrl As Control
 Dim mrc As ADODB.Recordset
 Dim txtSQL As String
 Dim Msgtext As String
 '檢查條件輸入
 If Trim(cmbfeild1.Text) = "" Or Trim(cmboperator1.Text) = "" Or Trim(txt1.Text) = "" Then
 MsgBox "請輸入完整的查詢條件", , "提示"
 Exit Sub
 End If
 
 Dim i, iCols As Integer '讓所有列都居中顯示文字
 iCols = MSFlexGrid1.Cols
 For i = 0 To iCols - 1
 MSFlexGrid1.ColAlignment(i) = flexAlignCenterCenter
 Next
 
 txtSQL = "select * from line_info where "
 txtSQL = txtSQL & Trim(Field(cmbfeild1.Text)) & Trim((cmboperator1.Text)) & "'" & Trim(txt1.Text) & "'"
 If Trim(cmbRelation1.Text <> "") Then '第一個組合關系存在
 If Trim(cmbfeild2.Text) = "" Or Trim(cmboperator2.Text = "") Or Trim(txt2.Text = "") Then
 MsgBox "你已經選擇了第一個組合關系，請輸入第二行查詢條件", , "提示"
 Exit Sub
 Else
 txtSQL = txtSQL & Field(Trim(cmbRelation1.Text)) & " " & Field(cmbfeild2.Text) & cmboperator2.Text & "'" & Trim(txt2.Text) & "'"
 End If
 End If
 
 If Trim(cmbRelation2.Text <> "") Then '第二個組合關系存在
 If Trim(cmbfeild3.Text) = "" Or Trim(cmboperator3.Text) = "" Or Trim(txt3.Text) = "" Then
 MsgBox "你已經選擇了第二個組合關系，請輸入第三行查詢條件", , "提示"
 Exit Sub
 Else
 txtSQL = txtSQL & Field(cmbRelation2.Text) & " " & Field(cmbfeild3.Text) & cmboperator3.Text & "'" & Trim(txt3.Text) & "'"
 End If
 End If
 On Error GoTo error1 '錯誤語句保護，當用戶輸入查詢的格式不對時給出提示信息。

 Set mrc = ExecuteSQL(txtSQL, Msgtext)
 If mrc.EOF = True Then '檢查信息是否存在，如果不存在給出提示并清空所有文本框
 MsgBox "沒有查詢到結果，可能會你輸入的信息不存在，或者信息矛盾"
 For Each ctrl In Me.Controls
 If TypeOf ctrl Is TextBox Then '是否為文本框TextBox
 ctrl.Text = "" '清空所有文本框
 End If
 Next
 
 For Each ctrl In Me.Controls
 If TypeOf ctrl Is ComboBox Then '是否為文本框TextBox
 ctrl.Text = ""
 End If
 Next
 Exit Sub
 End If
 With MSFlexGrid1
 .Rows = 1
 .TextMatrix(0, 0) = "卡號"
 .TextMatrix(0, 1) = "姓名"
 .TextMatrix(0, 2) = "上機日期"
 ........
 
 Do While Not mrc.EOF
 .Rows = .Rows + 1
 .TextMatrix(.Rows - 1, 0) = Trim(mrc!cardno)
 .TextMatrix(.Rows - 1, 1) = mrc!studentname
 .TextMatrix(.Rows - 1, 2) = mrc!ondate
 .TextMatrix(.Rows - 1, 3) = mrc!OnTime
 ........
 mrc.MoveNext
 Loop
 End With
 mrc.Close
error1:
 MsgBox "你輸入的查詢信息格式有誤，請按標準格式輸入。"
End Sub