Senin, 19 Februari 2018

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

 

What do you see in your debug.print results?

Duane



On February 19, 2018, at 6:09 PM, "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:



That was the issue. Thank you. Along the same lines:

  ' Do Issue Description next
    If Not IsNothing(Me.txtIssueDesc) Then
        ' .. build the predicate
        varWhere = (varWhere + " AND ") & "([IssueDescription] LIKE '" & Me.txtIssueDesc & "*')"
    End If

If I type the word 'the' it does not find it but I know its there.

Any ideas?

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Valar Dohaeris"







From: "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups com" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, February 19, 2018 6:00 PM
Subject: Re: [MS_AccessPros] Using a combo in a search string in vba

 
The first thing I would try is get rid of the ".value". Then add some debug.print statements which should always be included when writing code.
Regards,
Duane


On February 19, 2018, at 5:34 PM, "dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:



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: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)

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.


.

__,_._,___

Tidak ada komentar:

Posting Komentar