VBA Code creating Enter Parameter Value

Question

I created a search form with a subform in the footer to show the query results when clicking the Search button. 

The Detail section contains two combo box fields to use as search criteria.  I can get one combo box (ProvID) to work.  When I search using the other combo box (cboState), i receive the Enter Parameter Value with the search criteria listed.  When I enter the criteria in the message box, the subform retrieves the data.  I am not sure why I am rceiving the Enter Parameter Value.  When I run qryMain, there is no message.  The subform DataSource is qryMain and contains the fields Prov ID, Name, Status, State

Private Sub Search_Click()

    Dim strWhere As String

    Dim strError As String

    strWhere = "1=1"

    ' If Prov ID

    If Not IsNull(Me.[ProvID]) Then

        'Create Predicate

        strWhere = strWhere & " AND " & "qryMain.[Dr No] = " & Me.[ProvID] & ""

    End If

    ' If State

    If Not IsNull(Me.cboState) Then

        'Add it to the predicate

        strWhere = strWhere & " AND " & "qryMain.[STATE] = " & Me.[cboState] & ""

    End If

    If strError <> "" Then

        MsgBox strError

    Else

        'DoCmd.OpenForm "Browse Providers", acFormDS, , strWhere, acFormEdit, acWindowNormal

        If Not Me.FormFooter.Visible Then

            Me.FormFooter.Visible = True

            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height

        End If

        Me.Browse_Providers.Form.Filter = strWhere

        Me.Browse_Providers.Form.FilterOn = True

    End If

End Sub

Answer 1

Use Debug.Print to see what access is getting for strWhere.

Below is your code with the debug.print statement added.

---

Private Sub Search_Click()

  

    Dim strWhere As String

    Dim strError As String

  

    strWhere = "1=1"

   

    ' If Prov ID

    If Not IsNull(Me.[ProvID]) Then

        'Create Predicate

        strWhere = strWhere & " AND " & "qryMain.[Dr No] = " & Me.[ProvID] & ""

    End If

  

    ' If State

    If Not IsNull(Me.cboState) Then

        'Add it to the predicate

        strWhere = strWhere & " AND " & "qryMain.[STATE] = " & Me.[cboState] & ""

    End If

    Debug.Print strWhere

  

    If strError <> "" Then

        MsgBox strError

    Else

        'DoCmd.OpenForm "Browse Providers", acFormDS, , strWhere, acFormEdit, acWindowNormal

        If Not Me.FormFooter.Visible Then

            Me.FormFooter.Visible = True

            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height

        End If

      

        Me.Browse_Providers.Form.Filter = strWhere

        Me.Browse_Providers.Form.FilterOn = True

    End If

End Sub

---

After changing the code, open the form normally and Choose a state and then open the immediate window to see strWhere.

The quotes used in strWhere for State - a text field - don't look quite right in your post.

Try

strWhere = strWhere & " AND " & "qryMain.[STATE] = '" & Me.[cboState] & ""

Thanks - that did it.  I forgot about the single quotes!!

Answer 2

I suspect that State is a text field, which means its value must be enclosed in quotes:

    If Not IsNull(Me.cboState) Then

        'Add it to the predicate

        strWhere = strWhere & " AND " & "qryMain.[STATE] = '" & Me.[cboState] & "'"

    End If

Since it's probably not obvious, exagerated for clarity, that's

        strWhere = strWhere & " AND " & "qryMain.[STATE] = ' " & Me.[cboState] & " ' "

Thanks - that did it.  I forgot about the single quotes!!

Answer 3

I suspect that State is a text field, which means its value must be enclosed in quotes:

    If Not IsNull(Me.cboState) Then

        'Add it to the predicate

        strWhere = strWhere & " AND " & "qryMain.[STATE] = '" & Me.[cboState] & "'"

    End If

Since it's probably not obvious, exagerated for clarity, that's

        strWhere = strWhere & " AND " & "qryMain.[STATE] = ' " & Me.[cboState] & " ' "

Answer 4

Use Debug.Print to see what access is getting for strWhere.

Below is your code with the debug.print statement added.

---

Private Sub Search_Click()

    Dim strWhere As String

    Dim strError As String

    strWhere = "1=1"

    ' If Prov ID

    If Not IsNull(Me.[ProvID]) Then

        'Create Predicate

        strWhere = strWhere & " AND " & "qryMain.[Dr No] = " & Me.[ProvID] & ""

    End If

    ' If State

    If Not IsNull(Me.cboState) Then

        'Add it to the predicate

        strWhere = strWhere & " AND " & "qryMain.[STATE] = " & Me.[cboState] & ""

    End If

    Debug.Print strWhere

    If strError <> "" Then

        MsgBox strError

    Else

        'DoCmd.OpenForm "Browse Providers", acFormDS, , strWhere, acFormEdit, acWindowNormal

        If Not Me.FormFooter.Visible Then

            Me.FormFooter.Visible = True

            DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height

        End If

        Me.Browse_Providers.Form.Filter = strWhere

        Me.Browse_Providers.Form.FilterOn = True

    End If

End Sub

---

After changing the code, open the form normally and Choose a state and then open the immediate window to see strWhere.

The quotes used in strWhere for State - a text field - don't look quite right in your post.

Try

strWhere = strWhere & " AND " & "qryMain.[STATE] = '" & Me.[cboState] & ""