Access VBA如何实现chk_NonC复选框为false时排除指定数据库记录

修改逻辑说明 #

你只需要新增chk_NonC对应的过滤条件分支，再把条件拼接进原有搜索规则即可，核心逻辑如下：

• 当chk_NonC = True：新增条件为恒成立规则，不限制查询结果，显示所有符合其他搜索条件的记录
• 当chk_NonC = False：新增条件[Non_compliant] = False，过滤掉所有不合规记录
  另外顺便修复原有代码中ExecutionDate和Material拼接时遗漏And的bug，避免搜索报错。

完整修改后代码 #

Function SearchCriteria()

    Dim Customer, CustomerLocation, CustomerLocationPlace, ExecutionDate, Material As String
    Dim Intern, Extern, NonC As String ' 新增NonC变量存储不合规过滤条件
    Dim task, strCriteria As String

    ' 新增不合规复选框判断逻辑
    If Me.chk_NonC = True Then
        NonC = "[Non_compliant] like '*'"
    Else
        NonC = "[Non_compliant] = False"
    End If

    If Me.chk_AuditEX = True Then
        Extern = "[AuditEX] = " & Me.chk_AuditEX
    Else
        Extern = "[AuditEX] like '*'"
    End If
    
     If Me.chk_AuditIN = True Then
        Intern = "[AuditIN] = " & Me.chk_AuditIN
    Else
        Intern = "[AuditIN] like '*'"
    End If

    If IsNull(Me.cbo_CustomerLocations) Then
        CustomerLocation = "[CustomerLocationID] like '*'"
        CustomerLocationPlace = "[LocationCompanyPlace] like '*'"
    Else
        CustomerLocation = "[LocationCompanyName] = '" & Me.cbo_CustomerLocations.Column(0) & "'"
        CustomerLocationPlace = "[LocationCompanyPlace] = '" & Me.cbo_CustomerLocations.Column(1) & "'"
    End If
    
    
    If IsNull(Me.cbo_Customers) Then
        Customer = "[CustomerID] like '*'"
    Else
        Customer = "[CustomerID] = " & Me.cbo_Customers
    End If

    If IsNull(Me.txt_ExecutionDateTo) Then
        ExecutionDate = "[ExecutionDate] like '*'"
    Else
        If IsNull(Me.txt_ExecutionDateFrom) Then
            ExecutionDate = "[ExecutionDate] like '" & Me.txt_ExecutionDateTo & "'"
        Else
            ExecutionDate = "([ExecutionDate] >= #" & Format(Me.txt_ExecutionDateFrom, "mm/dd/yyyy") & "# And [ExecutionDate] <= #" & Format(Me.txt_ExecutionDateTo, "mm/dd/yyyy") & "#)"
        End If
    End If

    If IsNull(Me.cbo_Material) Or Me.cbo_Material = "" Then
        Material = "[MaterialID] like '*'"
    ElseIf Me.cbo_Material = 6 Then
        Material = "[MaterialID] in (" & TempVars!tempMaterial & ")"
    Else
        Material = "([MaterialID] = " & Me.cbo_Material & ")"
    End If

    ' 新增NonC条件拼接，同时补全原有遗漏的And
    strCriteria = Customer & " And " & CustomerLocation & " And " & CustomerLocationPlace & " And " & ExecutionDate & " And " & Material & " And " & Extern & " And " & Intern & " And " & NonC
                
    task = "Select * from qry_Administration where (" & strCriteria & ") order by ExecutionDate DESC"

    Debug.Print (task)
    
    Me.Form.RecordSource = task
    Me.Form.Requery
End Function

注意事项 #

如果你的数据表中不合规字段名不是Non_compliant，请自行替换代码中对应的字段名即可。

内容的提问来源于stack exchange，提问作者Mark Bakker