MS Access表单过滤报错求助：未选comboPO时触发错误

问题描述 #

我用Access表单实现数据库过滤，表单包含多个下拉列表。点击过滤按钮时，如果未选定红标标注的comboPO选项就会报错。推测问题出在手动选择的comboPO过滤条件和后续权限过滤逻辑的冲突，需要实现「comboPO为空则跳过该过滤条件」的逻辑。

现有VBA代码如下：

Private Sub btnFilter_Click()
'---------------------------------------------------------------------------------------------------'
'
' When Button "Fitler" is Clicked
'---------------------------------------------------------------------------------------------------'
    Dim sSQL As String
    Dim rst As DAO.Recordset

On Error GoTo Fin
    
    sSQL = "SELECT * FROM R_Select_Emploi"
    sSQL = sSQL & vbCrLf & "WHERE (EMPLOI_entreprise_id) =1"

    'We create the SQL  query with different drop down lists shown before that can or cannot be selected

    If Not IsNull(comboBassin) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_bassin_id) =" & Me.comboBassin.Column(1)
    End If
    If Not IsNull(comboOrga2) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_orga2_id) =" & Me.comboOrga2.Column(1)
    End If
    If Not IsNull(comboOrga3) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_orga3_id) =" & Me.comboOrga3.Column(1)
    End If
    If Not IsNull(comboOrga4) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_orga4_id) =" & Me.comboOrga4.Column(1)
    End If
    If Not IsNull(comboOrga5) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_orga5_id) =" & Me.comboOrga5.Column(1)
    End If
    If Not IsNull(comboOrga6) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_orga6_id) =" & Me.comboOrga6.Column(1)
    End If
    If Not IsNull(comboEtab) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_etablissement_id) =" & Me.comboEtab.Column(1)
    End If
    If Not IsNull(comboEmploi) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_code_id) =" & Me.comboEmploi.Column(1)
    End If
    If Not IsNull(comboManagement) And comboManagement.Column(0) Like "Masquer*" Then
        sSQL = sSQL & vbCrLf & "And (CODE_EMPLOI_management) =False"
    ElseIf Not IsNull(comboManagement) And comboManagement.Column(0) Like "Afficher*" Then
        sSQL = sSQL & vbCrLf & "And (CODE_EMPLOI_management) =True"
    End If
    If Not IsNull(comboInstance) Then
        sSQL = sSQL & vbCrLf & "And (PILOTAGE_EMPLOI_instance_id) =" & Me.comboInstance.Column(1)
    End If
    If Not IsNull(comboPE) Then
        sSQL = sSQL & vbCrLf & "And (EMPLOI_peiv_id) =" & Me.comboPE.Column(1)
    End If
' Here is the code for the row causing a problem it is no different than the others bu for some resons it's causing it. 
'Can it be because of that -----------> (Look further below please)
    If Not IsNull(comboPO) Then
        sSQL = sSQL & vbCrLf & "And (CODE_EMPLOI_categorie_emploi_id) =" & Me.comboPO.Column(0)
    End If
    If Not IsNull(comboMouvement) And comboMouvement.Column(0) Like "*mouvement*" Then
        sSQL = sSQL & vbCrLf & "And ([Mouvement?]) =True"
    ElseIf Not IsNull(comboMouvement) And comboMouvement.Column(0) Like "*créations*" Then
        sSQL = sSQL & vbCrLf & "And ([Création?]) =True"
    ElseIf Not IsNull(comboMouvement) And comboMouvement.Column(0) Like "*successions*" Then
        sSQL = sSQL & vbCrLf & "And ([Succession?]) =True"
    End If
    If Not IsNull(comboRecrutement) And comboRecrutement.Column(0) Like "*recrutements*" Then
        sSQL = sSQL & vbCrLf & "And ([Recrutement?]) =True"
    End If
    If Not IsNull(comboEtatCoMob) Then
        sSQL = sSQL & vbCrLf & "And (PILOTAGE_EMPLOI_etat_emploi_id) =" & Me.comboEtatCoMob.Column(1)
    End If
    If Not IsNull(comboFiche) Then
        sSQL = sSQL & vbCrLf & "And (PILOTAGE_EMPLOI_statut_fiche_id) =" & Me.comboFiche.Column(1)
    End If
    
    'Here is some automatic filters depending on who is filtering and what is he autorized to see
' This authorizations should be added to what he selected previously 
'if the person didn't select anything then these filters are the only thing that should be applied regarding that line
' -------> Here : 
    If bNonContract _
            Or bInfPO6 _
            Or bPO6etPO7 _
            Or bSupPO6 _
            Then
        'We create the SQL query depending on persons authorizations 
        sSQL = sSQL & vbCrLf & "And ((CATEGORIE_EMPLOI_categorie) ='toto'" 'uniquement pour ajouter à la requete SQL la clause And
        If bNonContract Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='NC'"
        If bInfPO6 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi PO < 6'"
        If bPO6etPO7 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi  PO 6 & 7'"
        If bSupPO6 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi PO > 7'"
        sSQL = sSQL & ")"
    End If
    

    'On termine la requête SQL
    sSQL = sSQL & ";"
    
    'On vérif s'il y a des enregistrements
    Set rst = CurrentDb.OpenRecordset(sSQL, dbReadOnly)
    If rst.EOF And rst.BOF Then
                MsgBox "Le jeu de donné est vide!" & vbCrLf _
                & "    - Soit vous n'avez pas les autorisations pour afficher le/les emploi(s) " & vbCrLf _
                & "    - Soit une erreur est survenue " & vbCrLf _
                & "Merci de contacter l'administrateur"
    Else
        'On ouvre la vue emploi avec les données de la recherche
        DoCmd.OpenForm "F_Vue_Emploi"
        With Forms("F_Vue_Emploi")
            .RecordSource = sSQL
            .Requery
        End With
    End If
    
Fin:
    'Message d'erreur si une erreur est levée
    If err.Number <> 0 Then
        MsgBox "Erreur 'btnFilter_Click'  " & err.Number & "  " & err.Description
    End If

    'On ferme la popup de filtrage
    DoCmd.Close acForm, "F_Popup_Filtre_Emploi"
    
    'On passe le focus sur la vue emploi
    If IsOpenForm("F_Vue_Emploi") Then Forms!F_Vue_Emploi.SetFocus
    
    
    'Fermeture du Recordset
    rst.Close: Set rst = Nothing

End Sub

解决方案 #

1. 修复comboPO的空值判断逻辑 #

Access下拉控件的IsNull判断可能存在失效情况，改用Len(Me.comboPO & "") = 0来精准判断控件是否为空，替换原有的comboPO判断代码：

' 替换原有的comboPO判断块
If Len(Me.comboPO & "") > 0 Then
    sSQL = sSQL & vbCrLf & "And (CODE_EMPLOI_categorie_emploi_id) =" & Me.comboPO.Column(0)
    ' 若Column(0)是文本类型，需添加单引号避免SQL语法错误：
    ' sSQL = sSQL & vbCrLf & "And (CODE_EMPLOI_categorie_emploi_id) ='" & Me.comboPO.Column(0) & "'"
End If

2. 避免权限过滤与comboPO的逻辑冲突 #

当用户手动选择了comboPO时，应该跳过权限模块中的PO类别过滤，防止双重过滤导致逻辑矛盾。修改权限判断代码块：

' 修改权限过滤部分的代码
If (bNonContract _
        Or bInfPO6 _
        Or bPO6etPO7 _
        Or bSupPO6) _
        And Len(Me.comboPO & "") = 0 Then ' 仅当comboPO未选择时才应用权限过滤
    ' 创建权限过滤的SQL语句
    sSQL = sSQL & vbCrLf & "And ((CATEGORIE_EMPLOI_categorie) ='toto'"
    If bNonContract Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='NC'"
    If bInfPO6 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi PO < 6'"
    If bPO6etPO7 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi  PO 6 & 7'"
    If bSupPO6 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi PO > 7'"
    sSQL = sSQL & ")"
End If

最终核心修改代码片段 #

' 替换原有的comboPO判断
If Len(Me.comboPO & "") > 0 Then
    sSQL = sSQL & vbCrLf & "And (CODE_EMPLOI_categorie_emploi_id) =" & Me.comboPO.Column(0)
    ' 若Column(0)是文本类型，改用下面这行
    ' sSQL = sSQL & vbCrLf & "And (CODE_EMPLOI_categorie_emploi_id) ='" & Me.comboPO.Column(0) & "'"
End If

' 修改后的权限过滤逻辑
If (bNonContract _
        Or bInfPO6 _
        Or bPO6etPO7 _
        Or bSupPO6) _
        And Len(Me.comboPO & "") = 0 Then
    sSQL = sSQL & vbCrLf & "And ((CATEGORIE_EMPLOI_categorie) ='toto'"
    If bNonContract Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='NC'"
    If bInfPO6 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi PO < 6'"
    If bPO6etPO7 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi  PO 6 & 7'"
    If bSupPO6 Then sSQL = sSQL & vbCrLf & "Or (CATEGORIE_EMPLOI_categorie) ='Emploi PO > 7'"
    sSQL = sSQL & ")"
End If

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