Combinatorial Query about VB Edition Room Management

Let's start with a form that combines queries to show you the main components

(I like to make the interface simpler, maybe different!)

Of course, all of them will have some time to think about the design of this thing, even if there are references from former teachers, elder brothers and sisters, of course, in order to save more code, also made a lot of changes to the previous.Here's a picture to see the difference.

Of course, what I know is that the difference is above the three Frames. If several conditions are queried, the Enabled of the lower control needs a lot of code to control, so I can simply change the value of the Frame.Enabled property directly and save a lot of code.

Operator Job Records Query

This query is the connection to the database, where the worklog table is queried

Code

'Correspond with the first image command To understand this code
Private Sub cmdExcel_Click()'export excel
  Dim xlApp As Excel.Application 'Define a excel application program
  Dim xlBook As Excel.Workbook 'Define A Workbook
  Dim xlSheet As Excel.Worksheet 'Define a worksheet
  Dim xlRange As Excel.Range 'Define a range of cells

  Dim i As Integer  'Define x-coordinates
  Dim j As Integer  'Define ordinates

  Set xlApp = CreateObject("excel.application") 'Establish excel Application Objects
  Set xlBook = xlApp.Workbooks.Add 'Create A Workbook
  Set xlSheet = xlBook.Worksheets(1)  'Create a worksheet

  DoEvents  'Transfer control so that the operating system can handle other events
  With myFlexGrid  'take FlexGrid Content Import in Control excel In tables
    For i = 0 To .Rows - 1  'Traverse all rows
      For j = 0 To .Cols - 1 'Traverse all columns
       DoEvents 'Transfer control so that the operating system can handle other events
       xlApp.ActiveSheet.Cells(i + 1, j + 1) = .TextMatrix(i, j) 'Create new cells and add flexgrid Content in
       Next j
    Next i
    End With

    xlApp.Visible = True

End Sub

Private Sub cmdExpty_Click()

For Each ctrl In Me.Controls
If TypeOf ctrl Is ComboBox Then
ctrl.Text = ""
End If
If TypeOf ctrl Is TextBox Then
ctrl.Text = ""
End If
Next

  myFlexGrid.Clear
  myFlexGrid.Rows = 0

End Sub
'Query Button Event
Private Sub cmdInquire_Click()

    Dim MsgText As String
    Dim MsgText1 As String
    Dim MsgText2 As String

    Dim txtsql, txtSQL1, txtSQL2 As String
    Dim mrc As adodb.Recordset

    Dim a, b, c As String
    Dim a1, b1, c1 As String
    Dim i

Select Case cb1(0).Text
         Case "Teacher"
           a = "UserId"
         Case "level"
           a = "level"
        Case "Date of registration"
           a = "LoginDate"
         Case "Registration Time"
           a = "LoginTime"
         Case "Logout Date"
           a = "LogoutDate"
        Case "Logoff time"
           a = "LogoutTime"
         Case "machine name"
           a = "computer"
        End Select
Select Case cb1(1).Text
        Case "Teacher"
          b = "UserId"
         Case "level"
           b = "level"
        Case "Date of registration"
           b = "LoginDate"
         Case "Registration Time"
          b = "LoginTime"
         Case "Logout Date"
           b = "LogoutDate"
        Case "Logoff time"
           b = "LogoutTime"
         Case "machine name"
           b = "computer"
        End Select

Select Case cb1(2).Text
        Case "Teacher"
          c = "UserId"
         Case "level"
           c = "level"
        Case "Date of registration"
           c = "LoginDate"
         Case "Registration Time"
          c = "LoginTime"
         Case "Logout Date"
           c = "LogoutDate"
        Case "Logoff time"
           c = "LogoutTime"
         Case "machine name"
         c = "computer"
        End Select


  For i = 0 To 2
  If tt1(i).Visible = True And tt1(i) = "" Then
  tt1(i).SetFocus
  End If
  Next

  If f2.Enabled = True Then
  If cb1(1) = "" Or cb2(1) = "" Then
  MsgBox "Please enter the complete query information!"
  End If
  End If

  If f3.Enabled = True Then
    If cb1(2) = "" Or cb2(2) = "" Then
    MsgBox "Please enter the complete query information!"
    Exit Sub
  End If
  End If

If DTP1(0).Visible = True Then
    a1 = Format(DTP1(0), "yyyy-mm-dd")
    Else
    a1 = tt1(0).Text
    End If
If DTP1(1).Visible = True Then
   b1 = Format(DTP1(1), "yyyy-mm-dd")
     Else
    b1 = tt1(1)
     End If
   If DTP1(2).Visible = True Then
      c1 = Format(DTP1(2), "yyyy-mm-dd")
   Else
     c1 = tt1(2).Text
   End If


      txtsql = "select * from worklog_info where " & a & cb2(0).Text & "'" & a1 & "'"
      txtSQL1 = txtsql & cb3.Text & " " & b & cb2(1).Text & " '" & b1 & "'"
      txtSQL2 = txtSQL1 & cb6.Text & " " & c & cb2(2).Text & " ' " & c1 & "'"

    If cb3.Text = "" Then
        Set mrc = ExecuteSQL(txtsql, MsgText)
    ElseIf cb6.Text = "" Then
        Set mrc = ExecuteSQL(txtSQL1, MsgText1)
    ElseIf Trim(cb6.Text) > 0 Then
        Set mrc = ExecuteSQL(txtSQL2, MsgText2)

         End If


     If mrc.EOF Then
        MsgBox "No data yet", vbOKOnly + vbExclamation, "Tips"
        End If
     If mrc.EOF = False Then
                  With myFlexGrid
                .Rows = 1
                .CellAlignment = 4
                .TextMatrix(0, 0) = "serial number"
                .TextMatrix(0, 1) = "Teacher"
                .TextMatrix(0, 2) = "level"
                .TextMatrix(0, 3) = "Date of registration"
                .TextMatrix(0, 4) = "Registration Time"
                .TextMatrix(0, 5) = "Logout Date"
                .TextMatrix(0, 6) = "Logoff time"
                .TextMatrix(0, 7) = "machine name"
                .TextMatrix(0, 8) = "state"
                 .ColWidth(5) = 2000

                 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(1))
               .TextMatrix(.Rows - 1, 2) = Trim(mrc.Fields(2))
               .TextMatrix(.Rows - 1, 3) = Format(mrc.Fields(3))
               .TextMatrix(.Rows - 1, 4) = Format(mrc.Fields(4))
               .TextMatrix(.Rows - 1, 5) = Format(mrc.Fields(5))
               .TextMatrix(.Rows - 1, 6) = Format(mrc.Fields(6))
               .TextMatrix(.Rows - 1, 7) = Trim(mrc.Fields(7))
               .TextMatrix(.Rows - 1, 8) = Trim(mrc.Fields(8))

               mrc.MoveNext
          Loop
      End With


End If
End Sub

Private Sub Form_Load() 'for Loop through add content
Dim i, j

For i = 0 To 2
tt1(i).Visible = False
Next

f2.Enabled = False
f3.Enabled = False

For i = 0 To 2
    With cb1(i)
    .AddItem "Teacher"
    .AddItem "Date of registration"
    .AddItem "Registration Time"
    .AddItem "Logout Date"
    .AddItem "Logoff time"
    .AddItem "machine name"
    End With
    Next

For j = 0 To 2
    With cb2(j)
    .AddItem "="
    .AddItem "<"
    .AddItem ">"
    .AddItem "<>"
    End With
Next

With cb3
         .AddItem ""
        .AddItem "and"
        .AddItem "or"
        End With
 With cb6
        .AddItem ""
        .AddItem "and"
        .AddItem "or"
End With


End Sub

Private Sub cb3_click()
    If Trim(cb3.Text) = "" Then
        f2.Enabled = False
    Else
        f2.Enabled = True
    End If
End Sub

Private Sub cb6_click()
    If Trim(cb6.Text) = "" Then
        f3.Enabled = False
    Else
        f3.Enabled = True
    End If
End Sub

Private Sub cb1_click(index As Integer) 'along with cb1(i)Content to replace
    Dim i
    For i = 0 To 2
        If cb1(i).Text = "Teacher" Or cb1(i).Text = "machine name" Then
         tt1(i).Visible = True
        DTP1(i).Visible = False
    Else
        tt1(i).Visible = False
        DTP1(i).Visible = True
    End If
    Next
End Sub

From the code, we can see that the logical judgment of whether the control is displayed or not is much higher than the database query. When thinking is always confusing, I also think about an event to write it out, and I hope you can learn from it.