[machine room] query - combination

In terms of the basic information maintenance of students, the combination query code is the same,

Enter the conditions in the first line and click query;

If the first combination relationship is selected, you can only query by entering the second line of conditions;

Similarly, if you continue to select the second combination condition, continue to enter the third line of query condition.

The text is too boring. Above:


The logic is clear. Let's see how the code is implemented!

Private Sub cmdInquiry_Click()
    
    txtSQL = "select * from student_Info where "

  'The function is used to determine whether the query condition is empty
  isempty0 = Testtxt(ComboField(0).Text) Or Testtxt(ComboOpSign(0).Text) Or Testtxt(txtInquiry1.Text)

   If isempty0 = False Then
     MsgBox "Please enter query criteria", 0 + 48, "Tips"
   Exit Sub
   Else

   'Query of a condition
   txtSQL = txtSQL & "" & field(ComboField(0).Text) & "" & ComboOpSign(0).Text & "'" & Trim(txtInquiry1.Text) & "'"
   End If

   If ComboCombineRelation(0).Text <> "" Then
    isempty1 = isempty0 And Testtxt(ComboField(1).Text) And Testtxt(ComboOpSign(1).Text) And Testtxt(txtInquiry2.Text)
      If isempty1 = False Then
        MsgBox "Please enter the second query criteria", 0 + 48, "Tips"
        Exit Sub
      Else
   'Add query for second condition
    txtSQL = txtSQL & " " & field(ComboCombineRelation(0).Text) & " " & field(ComboField(1).Text) & ComboOpSign(1).Text & "'" & Trim(txtInquiry2.Text) & "'"
      End If
   End If

   If ComboCombineRelation(1).Text <> "" Then
    isempty2 = isempty0 And isempty1 And Testtxt(ComboField(2).Text) And Testtxt(ComboOpSign(2).Text) And Testtxt(txtInquiry3.Text)
        If isempty2 = False Then
            MsgBox "Please enter the third query criteria", 0 + 48, "Tips"
            Exit Sub
        Else

   'Add query for the third condition
    txtSQL = txtSQL & "" & field(ComboCombineRelation(1).Text) & " " & field(ComboField(2).Text) & ComboOpSign(2).Text & "'" & Trim(txtInquiry3.Text) & "'"
        End If
   End If

 Set mrc = ExecuteSQL(txtSQL, msgText)
Do not fix the first row of data in MsHflexgrid.
Private Sub cmdModify_Click()
    'Determine whether the data to be modified is selected
    With MSFlexGrid1
     If .RowSel = 0 Then
        MsgBox "Please select the data to modify!", 48, "Tips"
        Exit Sub
     Else
        If .RowSel > 0 Then
            txtSQL = "select*from student_Info where cardno='" & Trim(.TextMatrix(.RowSel, 0)) & "'"
            Set mrc = ExecuteSQL(txtSQL, msgText)
            
            SetParent frmModifyStudentInfo.hWnd, frmMain.Picture1.hWnd
            frmModifyStudentInfo.Show
        End If
     End If
    End With
        
End Sub

A Field function is defined to make the Field name in VB program correspond to the column name in database.

Public Function field(a As String) As String
    Select Case a
        Case "Card number"
            field = "cardno"
        Case "Full name"
            field = "studentName"
        Case "Student ID"
            field = "studentNo"
        Case "Tie"
            field = "department"
        Case "class"
            field = "class"
        Case "grade"
            field = "grade"
        Case "On board time"
            field = "ontime"
        Case "On board date"
            field = "ondate"
        Case "Date of disembarkation"
            field = "offdate"
        Case "Disembarkation time"
            field = "offtime"
        Case "Consumption amount"
            field = "consume"
        Case "balance"
            field = "cash"
        Case "Remarks"
            field = "status"
        Case "And"
            field = "and"
        Case "or"
            field = "or"
    End Select
      
 End Function

Because they are all control groups used, the for loop statement is applied to save code.

Private Sub Form_Load()
    Dim i, j, k
        
    For i = 0 To 2
        With ComboField(i)
            .AddItem "Card number"
            .AddItem "Student ID"
            .AddItem "Full name"
            .AddItem "Gender"
            .AddItem "Tie"
            .AddItem "grade"
            .AddItem "class"
        End With
    Next i
    
    For j = 0 To 2
        With ComboOpSign(j)
            .AddItem "="
            .AddItem "<"
            .AddItem ">"
            .AddItem "<>"
        End With
    Next j
    
    For k = 0 To 1
        With ComboCombineRelation(k)
            .AddItem "And"
            .AddItem "or"
        End With
    Next k
    
    
    MSFlexGrid1.SelectionMode = flexSelectionByRow      'Safe choice
    MSFlexGrid1.HighLight = flexHighlightWithFocus      'Highlight appears only when the focus is under control
    
        
    With MSFlexGrid1
    .CellAlignment = 4
    .TextMatrix(0, 0) = "Card number"
    .TextMatrix(0, 1) = "Student ID"
    .TextMatrix(0, 2) = "Full name"
    .TextMatrix(0, 3) = "Gender"
    .TextMatrix(0, 4) = "Tie"
    .TextMatrix(0, 5) = "grade"
    .TextMatrix(0, 6) = "class"
    .TextMatrix(0, 7) = "balance"
    .TextMatrix(0, 8) = "Remarks"
    .TextMatrix(0, 9) = "state"
    .TextMatrix(0, 10) = "date"
    .TextMatrix(0, 11) = "time"
    .TextMatrix(0, 12) = "type"
    End With
    
    Left = 0    'Center horizontally
    Top = 0     'Center in vertical
    
    cmdModify.Enabled = False
    
End Sub
Keep going, and you will be successful soon!

Keywords: Database

Added by SwarleyAUS on Fri, 01 May 2020 21:30:53 +0300