Access分割窗体组合框相同名称不同ID的客户位置记录分组查询问题

1. 修改组合框cbo_CustomerLocations的行来源SQL #

把原来的SQL替换为去重查询，仅返回不重复的位置名称，避免同位置出现多个选项：

SELECT DISTINCT LocationCompanyName
FROM tbl_CustomerLocations
ORDER BY LocationCompanyName;

修改后需要调整组合框属性：列数设为1，绑定列设为1，列宽调整为适合显示名称的宽度即可。

2. 修改VBA筛选逻辑 #

修改SearchCriteria函数中CustomerLocation相关的判断条件，将原来按单条ID筛选的逻辑，改为匹配所有对应位置名称的CustomerLocationID，同时修正原代码的SQL语法隐患：

Function SearchCriteria()
    ' 修正变量定义：VBA逗号分隔声明时每个变量需单独指定类型，否则默认是Variant类型
    Dim Customer As String, CustomerLocation As String
    Dim task As String, strCriteria As String

    If IsNull(Me.Cbo_Customers) Then
        Customer = "[CustomerID] like '*'"
    Else
        Customer = "[CustomerID] = " & Me.Cbo_Customers
    End If

    If IsNull(Me.cbo_CustomerLocations) Then
        CustomerLocation = "[CustomerLocationID] like '*'"
    Else
        ' 用子查询匹配所有选中位置名称对应的ID，Replace方法处理名称中含单引号的异常情况
        CustomerLocation = "[CustomerLocationID] IN (SELECT CustomerLocationID FROM tbl_CustomerLocations WHERE LocationCompanyName = '" & Replace(Me.cbo_CustomerLocations, "'", "''") & "')"
    End If

    ' And前后添加空格，避免拼接后出现SQL语法错误
    strCriteria = Customer & " And " & CustomerLocation

    task = "Select * from qry_Administration where (" & strCriteria & ")"

    Me.Form.RecordSource = task
    Me.Form.Requery
End Function

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