Pop-up Search Tool will not pull in data

M

Mr. Southern

Registered User.
Local time
Today, 14:51
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: 289
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.
 
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.
 
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.
 
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.
 
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
 
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.
 
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:
 
test it with different variant of options.
 
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
 
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
 
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.
 
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.
 
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.
 
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
 
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.
 
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...
 
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.
 
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

Uncle Gizmo
How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation
2
Replies
25
Views
1,459
MarkK
MarkK
S
Solved Query in form not populating field
2
Replies
20
Views
1,609
Sh8dyDan
S
S
Modifications to code in Access to modify Word doc
Replies
17
Views
1,860
Pat Hartman
P
K
Solved MS Access Query of remote Database pulling partial data
2
Replies
26
Views
3,819
poliadisa
P
C
How to make editable multi-column subforms based on many-to-many relationships?
Replies
4
Views
1,455
CFMayr
C

Users who are viewing this thread

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