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

pbaldy said:

What is the result of the debug.print? Does that SQL work in a new query?

Click to expand...

pbaldy said:

It won't open by itself when that code runs, you have to open it (sorry if that's a stupid comment, couldn't tell if that's what you expected).

Click to expand...

pbaldy said:

Alt-F11 should open the VBA editor. Then Ctl-G or View/Immediate Window should show the Immediate window if it isn't already showing. If that doesn't work, I've seen it open where it was so tight to the bottom of the window it was hard to see. If that's the case, hovering your mouse around there may show the double arrow that will let you drag it up.

Click to expand...

SELECT qry_ChainDetail.Cust, qry_ChainDetail.Name, qry_ChainDetail.Address, qry_ChainDetail.City FROM qry_ChainDetail WHERE & strSearch ORDER BY qry_ChainDetail.[Cust];
SELECT qry_ChainDetail.Cust, qry_ChainDetail.Name, qry_ChainDetail.Address, qry_ChainDetail.City FROM qry_ChainDetail WHERE & strSearch ORDER BY qry_ChainDetail.[Cust];

SELECT Cust, [Name], Address, City FROM qry_ChainDetail WHERE & strSearch ORDER BY [Cust]
SELECT Cust, [Name], Address, City FROM qry_ChainDetail WHERE & strSearch ORDER BY [Cust]
SELECT Cust, [Name], Address, City FROM qry_ChainDetail WHERE & strSearch ORDER BY [Cust]

MajP said:

How about a couple helper functions to make this way easier

Code:Copy to clipboard

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...

pbaldy said:

You can see that strSearch isn't getting interpreted into its value. You need to terminate your string before concatenating the variable.

"...WHERE " & strSearch & "..."

Click to expand...

FROM qry_ChainDetail" WHERE " & strSearch & "ORDER BY qry_ChainDetail.[Cust]"

pbaldy said:

Delete the quotes before WHERE. The ellipsis replaced the rest of the string.

Click to expand...