Mr. Southern
Registered User.
- Local time
- Today, 02: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.
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.