Pop-up Search Tool will not pull in data (1 Viewer)

M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
Helllo,

I am creating a pop-up search tool with a where and a how option group, you can search three choices (Account Num, Name or both) and it will have a list box to display data.

However, I am missing something because every time I try to search it brings back blank results.

My list box has a row source of a query which is populating the list box until I search.

VBA for the search button:


Private Sub cmdSearch_Click()
On Error Resume Next
Dim strSearch As String
Dim strSQL As String
strSearch = Me.txtSearch.Value
Select Case Me.grpWhere.Value
Case 1 'Customer Number
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = "[Account Num] Like " & Chr(34) & strSearch & "*" & Chr(34)
Case 2 'Ends With
strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & Chr(34)
Case 3 'Contains
strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
End Select

Case 2 'Customer Name
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = "[Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
Case 2 'Ends With
strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
Case 3 'Contains
strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
End Select

Case 3 'Both
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = "[Account Num] Like " & Chr(34) & strSearch & "*" & Chr(34) & _
" OR [Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
Case 2 'Ends With
strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & Chr(34) & _
" OR [Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
Case 3 'Contains
strSearch = "[AccountNum] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34) & _
" OR [Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
End Select

End Select
strSQL = "SELECTtblChain.[Account Num], tblChain.Name, tblChain.Address, tblChain.City FROM tblChain ORDER BY tblChain.Name;"

With Me.lstSearch
.RowSource = strSQL
.Requery

Any help would be greatly appreciated.
 

Attachments

  • frmSearch.PNG
    frmSearch.PNG
    24.3 KB · Views: 232
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:42
Joined
Aug 30, 2003
Messages
36,125
Oh, and I assume that's not all the code, as you never close the With block. Also, the requery is not necessary in my experience.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 15:42
Joined
Oct 29, 2018
Messages
21,469
Hi. Welcome to AWF! First of all, I would recommend removing the line:
Code: 
On Error Resume Next
It could be masking out something you need to know/see. Second, you could do a Debug.Print strSQL or MsgBox strSQL to see what your code is actually building for the Listbox. The result should give you a clue to what could be happening wrong.
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
pbaldy said:
Oh, and I assume that's not all the code, as you never close the With block. Also, the requery is not necessary in my experience.
Click to expand...

pbaldy,

For some reason, I had a brain fart. I believe the strSQL should be:

strSQL = "SELECT tblChain.[Account Num], [tblChain.Name], " & "tblChain.Address, tblChain.City " & "FROM tblChain " "WHERE " & strSearch & " " & "ORDER BY tblChain.Name;"

With Me.lstSearch
.RowSource = strSQL
.Requery
End With
End Sub


I might have to remove the requery if I cant get this to work.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:42
Joined
Aug 30, 2003
Messages
36,125
The requery won't hurt, just isn't necessary. Try the debug method in the link and post the finished SQL here if you don't spot the problem.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:42
Joined
May 7, 2009
Messages
19,237
Code: 
Private Sub cmdSearch_Click()
On Error Resume Next
    Dim strSearch As String
    Dim strSQL As String
    strSearch = Me.txtSearch.value
    
    Select Case True
    Case Me.grpWhere.value = 1 'Customer Number
        Select Case True
            Case Me.grpHow.value = 1 'Starts With
            strSearch = "[Account Num] Like " & Chr(34) & strSearch & "*" & Chr(34)
            Case Me.grpHow.value = 2 'Ends With
            strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & Chr(34)
            Case Is = 3 'Contains
            strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
        End Select
    
    Case Me.grpWhere.value = 2 'Customer Name
        Select Case True
            Case Me.grpHow.value = 1 'Starts With
            strSearch = "[Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
            Case Me.grpHow.value = 2 'Ends With
            strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
            Case Me.grpHow.value = 3 'Contains
            strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
        End Select
    
    Case Me.grpWhere.value = 3 'Both
        Select Case True
        Case Me.grpHow.value = 1 'Starts With
        strSearch = "[Account Num] Like " & Chr(34) & strSearch & "*" & Chr(34) & _
        " OR [Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
        Case Me.grpHow.value = 2 'Ends With
        strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & Chr(34) & _
        " OR [Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
        Case Me.grpHow.value = 3 'Contains
        strSearch = "[AccountNum] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34) & _
        " OR [Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
        End Select
    
    End Select
    strSQL = "SELECTtblChain.[Account Num], tblChain.Name, tblChain.Address, tblChain.City FROM tblChain " & _
                " Where " & strSearch & " ORDER BY tblChain.Name;"
    
    With Me.lstSearch
        .RowSource = strSQL
        .Requery
    End With
End Sub
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
arnelgp said:
Code: 
Private Sub cmdSearch_Click()
On Error Resume Next
    Dim strSearch As String
    Dim strSQL As String
    strSearch = Me.txtSearch.value
    
    Select Case True
    Case Me.grpWhere.value = 1 'Customer Number
        Select Case True
            Case Me.grpHow.value = 1 'Starts With
            strSearch = "[Account Num] Like " & Chr(34) & strSearch & "*" & Chr(34)
            Case Me.grpHow.value = 2 'Ends With
            strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & Chr(34)
            Case Is = 3 'Contains
            strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
        End Select
    
    Case Me.grpWhere.value = 2 'Customer Name
        Select Case True
            Case Me.grpHow.value = 1 'Starts With
            strSearch = "[Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
            Case Me.grpHow.value = 2 'Ends With
            strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
            Case Me.grpHow.value = 3 'Contains
            strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
        End Select
    
    Case Me.grpWhere.value = 3 'Both
        Select Case True
        Case Me.grpHow.value = 1 'Starts With
        strSearch = "[Account Num] Like " & Chr(34) & strSearch & "*" & Chr(34) & _
        " OR [Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
        Case Me.grpHow.value = 2 'Ends With
        strSearch = "[Account Num] Like " & Chr(34) & "*" & strSearch & Chr(34) & _
        " OR [Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
        Case Me.grpHow.value = 3 'Contains
        strSearch = "[AccountNum] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34) & _
        " OR [Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
        End Select
    
    End Select
    strSQL = "SELECTtblChain.[Account Num], tblChain.Name, tblChain.Address, tblChain.City FROM tblChain " & _
                " Where " & strSearch & " ORDER BY tblChain.Name;"
    
    With Me.lstSearch
        .RowSource = strSQL
        .Requery
    End With
End Sub
Click to expand...

arnelhp,

Thank you, it looks like the strsql worked.
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
pbaldy said:
The requery won't hurt, just isn't necessary. Try the debug method in the link and post the finished SQL here if you don't spot the problem.
Click to expand...

pbaldy,

Thanks for the tips. The debug.Print helped out. I was able to get the search button to work. Now only if I could get the selecting and opening of the forum to work...

:banghead:
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:42
Joined
May 7, 2009
Messages
19,237
test it with different variant of options.
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
theDBguy said:
Hi. Welcome to AWF! First of all, I would recommend removing the line:
Code: 
On Error Resume Next
It could be masking out something you need to know/see. Second, you could do a Debug.Print strSQL or MsgBox strSQL to see what your code is actually building for the Listbox. The result should give you a clue to what could be happening wrong.
Click to expand...

theDBguy,

I removed all of the On Error Resume Next lines and was able to use Debug to see what was occurring.

Thanks
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
arnelgp said:
test it with different variant of options.
Click to expand...

arnelgp,

My where and How option groups are working but it appears that I cannot actually select the data that is coming from the lstSearch. After I select an Account and hit my cmdOK it says Run-time error '2424' and when I go in to debug my line with rst.FindFirst fails.


Private Sub cmdOK_Click()
Dim rst As Object
DoCmd.OpenForm "frmReport"
Set rst = Forms![frmReport].RecordsetClone
rst.FindFirst "[Account_Num] =" & Me.lstSearch.Value
Forms![frmReport].Bookmark = rst.Bookmark
DoCmd.Close acForm, "frmSearch"
End Sub
 
M

Mark_

Longboard on the internet
Local time
Today, 15:42
Joined
Sep 12, 2017
Messages
2,111
One suggestion from a design standpoint; If you offer to select by account number, you may want to order by account number for matching results. This should avoid users being confused by "out of order" account numbers.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 15:42
Joined
Oct 29, 2018
Messages
21,469
Mr. Southern said:
theDBguy,

I removed all of the On Error Resume Next lines and was able to use Debug to see what was occurring.

Thanks
Click to expand...
Hi. Glad to hear you got it sorted out. Good luck with your project.
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
Mark_ said:
One suggestion from a design standpoint; If you offer to select by account number, you may want to order by account number for matching results. This should avoid users being confused by "out of order" account numbers.
Click to expand...

Mark_,

I completely agree with you because a majority of the time this search will be used for the account numbers. Thanks for the idea.
 
M

Mark_

Longboard on the internet
Local time
Today, 15:42
Joined
Sep 12, 2017
Messages
2,111
To change your order you will want to do the following;

At the top, add
Code: 
DIM sqlOrder AS STRING

Before filling in strSQL add

Code: 
IF Me.grpWhere = 2 THEN 'If by NAME
	sqlOrder = " ORDER BY TblChain.Name'"
ELSE
	sqlOrder = " ORDER BY TblChain.[Account Num]"
END IF
Qhen filling strSQL, replace your text literal for your order with sqlOrder
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:42
Joined
Aug 30, 2003
Messages
36,125
Mr. Southern said:
pbaldy,

Thanks for the tips. The debug.Print helped out. I was able to get the search button to work.
Click to expand...

Happy to help. I was trying to help you learn how to fish, and that's a common debugging method.
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
I broke it again:


Private Sub cmdSearch_Click()
Dim strSearch As String
Dim strSQL As String
strSearch = Me.txtSearch.Value
Select Case Me.grpWhere.Value
Case 1 'Customer Number
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = "[Cust] Like " & Chr(34) & strSearch & "*" & Chr(34)
Case 2 'Ends With
strSearch = "[Cust] Like " & Chr(34) & "*" & strSearch & Chr(34)
Case 3 'Contains
strSearch = "[Cust] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
End Select
Case 2 'Customer Name
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = "[Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
Case 2 'Ends With
strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
Case 3 'Contains
strSearch = "[Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
End Select
Case 3 'Both
Select Case Me.grpHow.Value
Case 1 'Starts With
strSearch = "[Cust] Like " & Chr(34) & strSearch & "*" & Chr(34) & _
" OR [Name] Like " & Chr(34) & strSearch & "*" & Chr(34)
Case 2 'Ends With
strSearch = "[Cust] Like " & Chr(34) & "*" & strSearch & Chr(34) & _
" OR [Name] Like " & Chr(34) & "*" & strSearch & Chr(34)
Case 3 'Contains
strSearch = "[Cust] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34) & _
" OR [Name] Like " & Chr(34) & "*" & strSearch & "*" & Chr(34)
End Select

End Select
strSQL = "SELECTqry_ChainDetail.Cust, qry_ChainDetail.Name, qry_ChainDetail.Address, qry_ChainDetail.City FROM qry_ChainDetail WHERE & strSearch ORDER BY qry_ChainDetail.[Cust];"
With Me.lstSearch
.RowSource = strSQL
.Requery

Debug.Print strSQL

End With
End Sub



It brings back blanks results...
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:42
Joined
May 21, 2018
Messages
8,527
How about a couple helper functions to make this way easier
Code: 
Private Sub cmdSearch_Click()
Dim strSearch As String
Dim strSQL As String
Dim FieldName As String
strSearch = Me.txtSearch.Value

Select Case Me.grpWhere.Value
  Case 1 'Customer Number
     FieldName = "[Cust]"
     Select Case Me.grpHow.Value
       Case 1 'Starts With
         strSearch = GetBegin(FieldName, strSearch)
        Case 2 'Ends With
          strSearch = GetEnd(FieldName, strSearch)
        Case 3 'Contains
          strSearch = getBoth(FieldName, strSearch)
      End Select
  Case 2 'Customer Name
    FieldName = "[Name]"
    Select Case Me.grpHow.Value
       Case 1 'Starts With
         strSearch = GetBegin(FieldName, strSearch)
       Case 2 'Ends With
         strSearch = GetEnd(FieldName, strSearch)
       Case 3 'Contains
         strSearch = getBoth(FieldName, strSearch)
    End Select
  Case 3 'Both
    Select Case Me.grpHow.Value
       Case 1 'Starts With
          strSearch = GetBegin("[Cust]", strSearch) & " OR " & GetBegin("[Name]", strSearch)
       Case 2 'Ends With
          strSearch = GetEnd("[Cust]", strSearch) & " OR " & GetEnd("[Name]", strSearch)
        Case 3 'Contains
          strSearch = getBoth("[Cust]", strSearch) & " OR " & getBoth("[Name]", strSearch)
    End Select
End Select
strSQL = "SELECT Cust, [Name], Address, City FROM qry_ChainDetail WHERE & strSearch ORDER BY [Cust]"
With Me.lstSearch
  .RowSource = strSQL
  .Requery
  Debug.Print strSQL
End With
End Sub

Public Function GetEnd(FieldName, strSearch As String) As String
  GetEnd = FieldName & " like '" & strSearch & "#'"
End Function

Public Function GetBegin(FieldName, strSearch As String) As String
  GetBegin = FieldName & " like '*" & strSearch & "'"
End Function

Public Function getBoth(FieldName, strSearch As String) As String
  getBoth = FieldName & " like '*" & strSearch & "#'"
End Function

when you post code, select the code then hit the # side in the message box to format nicely.
 
M

Mr. Southern

Registered User.
Local time
Today, 17:42
Joined
Aug 29, 2019
Messages
90
MajP said:
How about a couple helper functions to make this way easier
Code: 
Private Sub cmdSearch_Click()
Dim strSearch As String
Dim strSQL As String
Dim FieldName As String
strSearch = Me.txtSearch.Value

Select Case Me.grpWhere.Value
  Case 1 'Customer Number
     FieldName = "[Cust]"
     Select Case Me.grpHow.Value
       Case 1 'Starts With
         strSearch = GetBegin(FieldName, strSearch)
        Case 2 'Ends With
          strSearch = GetEnd(FieldName, strSearch)
        Case 3 'Contains
          strSearch = getBoth(FieldName, strSearch)
      End Select
  Case 2 'Customer Name
    FieldName = "[Name]"
    Select Case Me.grpHow.Value
       Case 1 'Starts With
         strSearch = GetBegin(FieldName, strSearch)
       Case 2 'Ends With
         strSearch = GetEnd(FieldName, strSearch)
       Case 3 'Contains
         strSearch = getBoth(FieldName, strSearch)
    End Select
  Case 3 'Both
    Select Case Me.grpHow.Value
       Case 1 'Starts With
          strSearch = GetBegin("[Cust]", strSearch) & " OR " & GetBegin("[Name]", strSearch)
       Case 2 'Ends With
          strSearch = GetEnd("[Cust]", strSearch) & " OR " & GetEnd("[Name]", strSearch)
        Case 3 'Contains
          strSearch = getBoth("[Cust]", strSearch) & " OR " & getBoth("[Name]", strSearch)
    End Select
End Select
strSQL = "SELECT Cust, [Name], Address, City FROM qry_ChainDetail WHERE & strSearch ORDER BY [Cust]"
With Me.lstSearch
  .RowSource = strSQL
  .Requery
  Debug.Print strSQL
End With
End Sub

Public Function GetEnd(FieldName, strSearch As String) As String
  GetEnd = FieldName & " like '" & strSearch & "#'"
End Function

Public Function GetBegin(FieldName, strSearch As String) As String
  GetBegin = FieldName & " like '*" & strSearch & "'"
End Function

Public Function getBoth(FieldName, strSearch As String) As String
  getBoth = FieldName & " like '*" & strSearch & "#'"
End Function

when you post code, select the code then hit the # side in the message box to format nicely.
Click to expand...

MajP,

hmmm I am still getting the same results.
 
You must log in or register to reply here.

Similar threads

S
Solved Query in form not populating field
2
Replies
20
Views
1,129
Sh8dyDan
S
S
Modifications to code in Access to modify Word doc
Replies
17
Views
1,372
Pat Hartman
P
K
Solved MS Access Query of remote Database pulling partial data
2
Replies
26
Views
2,718
poliadisa
P
C
How to make editable multi-column subforms based on many-to-many relationships?
Replies
4
Views
1,262
CFMayr
C
M
Use form text box search to pull up query recordset
2
Replies
22
Views
1,893
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom