即使我提供VBA,VBA也会要求参数值?
问题描述:
Private Sub Command2_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim LArray() As String
Dim strCriteria As String
Dim strSQL As String
Dim TestArray() As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("QBF_Query")
For Each varItem In Me!Command2.ItemsSelected
strCriteria = strCriteria & ",'" & Me!Command2.ItemData(varItem) & "'"
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
LArray = Split(strCriteria, ",")
Dim txt As String
Dim i As Long
For i = LBound(LArray) To UBound(LArray)
QueryAns (LArray(i))
Next i
End Sub
Sub QueryAns(valX As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("QBF_Query")
strSQL = "SELECT * FROM Contacts WHERE ActiveSource LIKE'*'&valX&'*'"
qdf.SQL = strSQL
DoCmd.OpenQuery "QBF_Query"
Set db = Nothing
Set qdf = Nothing
End Sub
在下面的程序中,我试图创建一个Multiselect ListBox,用于过滤查询(即,如果某个工作表具有在MultiSelect中选择的值,则显示它)。为此,我将这些值作为一个String并将其解析为一个数组。然后我将这些值传递给名为QueryAns的函数,该函数实际上执行查询。当我运行它时,程序会创建一个对话框并要求我设置ValX参数(即使我明确地设置了它)。我怎样才能防止这种情况发生?即使我提供VBA,VBA也会要求参数值?
答
valX
是一个VBA变量;数据库引擎对此一无所知。因此,当它看到SELECT
声明的这部分... ActiveSource LIKE'*'&valX&'*'
...它假定valX
是您未提供值的参数的名称。
调整报价,当你建立SELECT
,使其包含变量而不是变量名的值...
strSQL = "SELECT * FROM Contacts WHERE ActiveSource LIKE '*'" & valX & "'*'"
不要在包裹()的参数。尝试QueryAns LArray(i) – sous2817
我这样做了,但我得到了相同的结果 – svsav
当我这样做时,调试返回'SELECT * FROM Contacts WHERE ActiveSource LIKE'*'&valX&'*'' – svsav