MS Access下拉列表以通过表进行筛选

Question

我有一个大表，并创建了一个过滤数据的表单。然而，目前，这些过滤器不起作用。有一些与列相对应的下拉筛选器，其想法是从特定列中选择一个值，然后只获得具有所选值的行，如果必要，还可以像这样一次筛选几列-每个框都有基本相同的代码，只是名称的更改。以下是一个盒子代码的例子：

Private Sub klk_AfterUpdate()
Dim klkxas As String
klkxas = "Select * from daca where ([wdwqd] = '" & Me.cboLiefName & "') and ([sdcxsadc] = '" & Me.cboSRat & "') and ([Verantwortlicher] = '" & Me.cboVerant & "') and ([cxsacx] = '" & Me.cboLiefKat & "') and ([qxqxsa] = '" & Me.cboliefart & "') and ([sxsa] = '" & Me.cboLand & "') and ([sdxs] = '" & Me.cboAudErg & "')"
Me.Form.RecordSource = klkxas
Me.Form.Requery

End Sub

有人能帮我修改代码使其正常工作吗？提前感谢

Answer 1

如果我猜测，您的问题是，即使选择一个或多个而不是所有组合框(即，将一些组合框留空(，也不会返回行。根据您尝试的查询，这不是错误，而是SQL逻辑的有效结果。您的用户需要在所有七个组合框中正确选择值，才能返回任何行。

这个动态过滤问题的一个解决方案是使用NZ(或IIF(将未选择的组合框的每个字段与其自身相匹配。此外，还可以使用保存的SQL查询，该查询指向表单控件，而不使用VBA值的任何串联和引号的标点符号。

如果选择了任何一个或所有控件，则以下将返回值。但请注意，在选择组合框后，将只返回所有字段中非NULL的行。请确保将myForm调整为查询中的实际表单名称：

SQL (另存为单独的查询和要形成的记录源(

SELECT *
FROM Lieferant
WHERE [Bezeichner] = NZ(Forms!myForm!cboLiefName, [Bezeichner])
AND [S_Rating] = NZ(Forms!myForm!cboSRat, [S_Rating]) 
AND [Verantwortlicher] = NZ(Forms!myForm!cboVerant, [Verantwortlicher])
AND [Lieferantenkategorie] = NZ(Forms!myForm!cboLiefKat, [Lieferantenkategorie])
AND [Lieferantenart] = NZ(Forms!myForm!cboliefart, [Lieferantenart])
AND [Land] = NZ(Forms!myForm!cboLand, [Land])
AND [Audit_Ergebnis] = NZ(Forms!myForm!cboAudErg, [Audit_Ergebnis])

VBA(单行！(

Private Sub cboSRat_AfterUpdate()
Me.Form.Requery    
End Sub

---

如果需要捕获具有潜在NULLs的行，则集成OR条件：

SQL

SELECT *
FROM Lieferant
WHERE ([Bezeichner] = NZ(Forms!myForm!cboLiefName, [Bezeichner])
OR (Forms!myForm!cboLiefName IS NULL AND [Bezeichner] IS NULL)
)
AND ([S_Rating] = NZ(Forms!myForm!cboSRat, [S_Rating]) 
OR (Forms!myForm!cboSRat IS NULL AND [S_Rating] IS NULL)
)
AND ([Verantwortlicher] = NZ(Forms!myForm!cboVerant, [Verantwortlicher])
OR (Forms!myForm!cboVerant IS NULL AND [Verantwortlicher] IS NULL)
)
AND ([Lieferantenkategorie] = NZ(Forms!myForm!cboLiefKat, [Lieferantenkategorie])
OR (Forms!myForm!cboLiefKat IS NULL AND [Lieferantenkategorie]IS NULL)
)
AND ([Lieferantenart] = NZ(Forms!myForm!cboliefart, [Lieferantenart])
OR (Forms!myForm!cboliefart IS NULL AND [Lieferantenart] IS NULL)
)
AND ([Land] = NZ(Forms!myForm!cboLand, [Land])
OR (Forms!myForm!cboLand IS NULL AND [Land] IS NULL)
)
AND ([Audit_Ergebnis] = NZ(Forms!myForm!cboAudErg, [Audit_Ergebnis])
OR (Forms!myForm!cboAudErg AND [Audit_Ergebnis] IS NULL)
)

Answer 2

不知何故，您(您的代码(必须扩展where子句，例如：

myLieferantName = "Select * from Lieferant where [Bezeichner] = '" & Me.cboLiefName & "' And [Land] = '" & Me.cboLand & "'"

附带说明一下，调整记录源后不需要重新查询：

Me.RecordSource = myLieferantName
' Me.Form.Requery