[MS_AccessPros] Using a combo in a search string in vba

 

I have the following code that is used to search for records:

Private Sub cmdSearch_Click()

Dim varWhere As Variant

    ' Initialize to Null

    varWhere = Null

 

   

   

    ' Do Issue Subject next

    If Not IsNothing(Me.txtIssueSubject) Then

        ' .. build the predicate

        ' Note: taking advantage of Null propogation

        '  so we don't have to test for any previous predicate

        varWhere = (varWhere + " AND ") & "([IssueSubject] LIKE '" & Me.txtIssueSubject & "*')"

    End If

   

    ' Do Issue Description next

    If Not IsNothing(Me.txtIssueDesc) Then

        ' .. build the predicate

        varWhere = (varWhere + " AND ") & "([IssueDescription] LIKE '" & Me.txtIssueDesc & "*')"

    End If

   

     If Not IsNothing(Me.cmbIssuer) Then

        ' .. build the predicate

        varWhere = "(UserID.Value = " & Me.cmbIssuer & ")"

    End If

   

    ' Check to see that we built a filter

    If IsNothing(varWhere) Then

        MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle

        Exit Sub

    End If

      ' See if any rows with a quick DLookup

    If IsNothing(DLookup("ServiceRequestID", "tblSystemServiceRequest", varWhere)) Then

        MsgBox "No Service Request meet your criteria.", vbInformation, gstrAppTitle

        Exit Sub

    End If

   

    ' Open Companies filtered

    ' Note: if form already open, this just applies the filter

    DoCmd.OpenForm "frmSystem_ServiecRequest", WhereCondition:=varWhere

    ' Done

    DoCmd.Close acForm, Me.Name

 

  

End Sub

Almost everything works except for my combo box value. the cmbobox is called cmbIssuer and its records source is SELECT tblSystemUsers.UserID, tblSystemUsers.UserName FROM tblSystemUsers; But when I run it the search and include the combo box in the search criteria it errors: 

Run-time error '2471'

The expression you enter as query parameter produced this error: 'UserID.Value' 

The string for varWhere contains: (UserID.Value = 2)

What am I doing wrong?

Thank you,

Art Lorenzini

Sioux Falls, SD

__._,_.___

---

Posted by: dbalorenzini@yahoo.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___