具有组合框数据的 SQL 命令"Enter Parameter Value"

Question

我目前有以下代码

Private Sub dbSearch_Click()
Dim ManfactQuery As String
Dim ModelQuery As String
Dim strSQL As String
ManfactQuery = Me.cboManfact.Column(1)
ModelQuery = Me.cboModel.Column(1)
If ManfactQuery = Null Or ManfactQuery = Null Then
strSQL = "SELECT [Solutions].SolutionText FROM [Solutions] WHERE [Solutions].ModelSolution = " & ModelQuery & ""
Else
If ModelQuery = Null Or ModelQuery = "" Then
strSQL = "SELECT [Solutions].SolutionText FROM [Solutions] WHERE [Solutions].ManufacturerSolution = " & ManfactQuery & ""
Else
strSQL = "SELECT [Solutions].SolutionText FROM [Solutions] WHERE [Solutions].ManufacturerSolution = " & ManfactQuery & " AND   [Solutions].ModelSolution = " & ModelQuery & ""
End If
End If
Me.lstSolution.RowSource = strSQL
End Sub

我试图将组合框的文本值传递到SQL语句中，它在一定程度上起作用，但我得到了"输入参数值"弹出对话框。如果我输入组合框的值并单击"确定"，它会用适当的字段填充列表框，但如果可能的话，我不需要对话框。

有什么想法吗？

Answer 1

首先你可能是指：

If ManfactQuery = Null Or ManfactQuery = Null Then

是这样的：

If ManfactQuery = Null Or ManfactQuery = "" Then

它可以更好地表示为：

If Nz(ManfactQuery)="" then

现在来谈谈实际问题。由于ManfactQuery和ModelQuery是文本值，因此它们需要在SQL文本中用单引号括起来，如下所示：

strSQL = "SELECT [Solutions].SolutionText FROM [Solutions] WHERE [Solutions].ModelSolution = '" & ModelQuery & "'"

Answer 2

解决了它。非常感谢@Charlie和@SunKnight0

代码如下：

Private Sub dbSearch_Click()
Dim ManfactQuery As String
Dim ModelQuery As String
Dim strSQL As String
ManfactQuery = Me.cboManfact.Column(1)
ModelQuery = Me.cboModel.Column(1)
If Len(ModelQuery) = 0 Then
strSQL = "SELECT [Solutions].SolutionText FROM [Solutions] WHERE [Solutions].ModelSolution = '" & ModelQuery & "'"
Else
If Len(ManfactQuery) = 0 Then
strSQL = "SELECT [Solutions].SolutionText FROM [Solutions] WHERE [Solutions].ManufacturerSolution = '" & ManfactQuery & "'"
Else
strSQL = "SELECT [Solutions].SolutionText FROM [Solutions] WHERE [Solutions].ManufacturerSolution = '" & ManfactQuery & "' AND [Solutions].ModelSolution = '" & ModelQuery & "'"
End If
End If
Me.lstSolution.RowSource = strSQL
End Sub

非常感谢大家，经过三天的努力，我明白了！