CODEWITHSUNDEEP
vbams-access
vsasv
vsasv

Reputation: 870

VBA asks for param value even though I supply it?

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

In the following program, I am trying to create a Multiselect ListBox, which I use to filter a query (i.e if some sheet has the values selected in MultiSelect, show it). To do so, I take in the values as a String and parse them in an array. Then I pass the values to a function called QueryAns that actually does the query. When I run it though, the program creates a dialogue and asks me for the ValX param to be set (even though I explicitly set it). How can I prevent this from happening?

Upvotes: 2

Views: 78

Answers (1)

HansUp
HansUp

Reputation: 97101

valX is a VBA variable; the db engine knows nothing about it. So when it sees this part of the SELECT statement ... ActiveSource LIKE'*'&valX&'*' ... it assumes valX is the name of a parameter for which you have not supplied a value.

Adjust the quotes when you build the SELECT so that it includes the value of that variable instead of the variable name ...

strSQL = "SELECT * FROM Contacts WHERE ActiveSource LIKE '*'" & valX & "'*'"

Upvotes: 2

Related Questions