help with filtering results in a search form

[didacticone]

I created a search form that works well except when i am searching for a record that has multiple entries in the corresponding table. i would to filter the results so that they will display all the information associated with that record if there is more than one. here is my code that i am using. thanks for your help!

Private Sub cmdSearch_Click()
    Dim strStudentRef As String
    Dim strSearch As String
 
'Check txtSearch for Null value or Nill Entry first.
 
    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtsearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
 
'Performs the search using value entered into txtSearch
'and evaluates this against values in Address
 
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Address")
    DoCmd.FindRecord Me!txtsearch
 
    Address.SetFocus
    strStudentRef = Address.Text
    txtsearch.SetFocus
    strSearch = txtsearch.Text
 
'If matching record found sets focus in Address and shows msgbox
'and clears search control
 
    If strStudentRef = strSearch Then
        Address.SetFocus
 
 
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtsearch.SetFocus
    End If
End Sub

[puppydogbuddy]

Try replacing lines 18 thru 23 of your code with the following:

    Address.SetFocus 
    strStudentRef = Address.Text 
    txtsearch.SetFocus
    DoCmd.RunCommand acCmdFilterBySelection

[didacticone]

Try replacing lines 18 thru 23 of your code with the following:

    Address.SetFocus 
    strStudentRef = Address.Text 
    txtsearch.SetFocus
    DoCmd.RunCommand acCmdFilterBySelection

i got an error "the command or action "filterbyselect ion" isnt available now"
here is what my code looks like... thanks for your help!

Private Sub cmdSearch_Click()
    Dim strStudentRef As String
    Dim strSearch As String
 
'Check txtSearch for Null value or Nill Entry first.
 
    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtsearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
 
'Performs the search using value entered into txtSearch
'and evaluates this against values in Address
 
    DoCmd.ShowAllRecords
    Address.SetFocus
    strStudentRef = Address.Text
 
    txtsearch.SetFocus
    DoCmd.RunCommand acCmdFilterBySelection
 
    strSearch = txtsearch.Text
 
'If matching record found sets focus in Address and shows msgbox
'and clears search control
 
    If strStudentRef = strSearch Then
        Address.SetFocus
 
 
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtsearch.SetFocus
    End If
End Sub

[puppydogbuddy]

Sorry, move line 22 to be between lines 18 and 19.

" DoCmd.RunComman d acCmdFilterBySe lection" needs to have focus on the Address field.

[didacticone]

Sorry, move line 22 to be between lines 18 and 19.

" DoCmd.RunComman d acCmdFilterBySe lection" needs to have focus on the Address field.

ok i did what you said, but when i search it comes up as having found no matches. here is my code... thanks again for your help

Private Sub cmdSearch_Click()
    Dim strStudentRef As String
    Dim strSearch As String
 
'Check txtSearch for Null value or Nill Entry first.
 
    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtsearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
 
'Performs the search using value entered into txtSearch
'and evaluates this against values in Address
 
    DoCmd.ShowAllRecords
    Address.SetFocus
    DoCmd.RunCommand acCmdFilterBySelection
    strStudentRef = Address.Text
 
    txtsearch.SetFocus
 
    strSearch = txtsearch.Text
 
'If matching record found sets focus in Address and shows msgbox
'and clears search control
 
    If strStudentRef = strSearch Then
        Address.SetFocus
 
 
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtsearch.SetFocus
    End If
End Sub

[puppydogbuddy]

try this. If this does not work, I will have to give you another method because this method requires that the search value be in the address field, not your search box.

Private Sub cmdSearch_Click() 
    Dim strStudentRef As String 
    Dim strSearch As String 
 
'Check txtSearch for Null value or Nill Entry first. 
 
    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then 
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" 
        Me![txtsearch].SetFocus 
    Exit Sub 
End If 
'--------------------------------------------------------------- 
 
'Performs the search using value entered into txtSearch 
'and evaluates this against values in Address 
 
    DoCmd.ShowAllRecords 
    DoCmd.GoToControl ("Address")
    DoCmd.RunCommand acCmdFilterBySelection
 
    Address.SetFocus 
    strStudentRef = Address.Text 
    txtsearch.SetFocus 
    strSearch = txtsearch.Text 
 
'If matching record found sets focus in Address and shows msgbox 
'and clears search control 
 
    If strStudentRef = strSearch Then 
        Address.SetFocus 
 
 
    'If value not found sets focus back to txtSearch and shows msgbox 
        Else 
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ 
            , "Invalid Search Criterion!" 
            txtsearch.SetFocus 
    End If 
End Sub

[didacticone]

try this. If this does not work, I will have to give you another method because this method requires that the search value be in the address field, not your search box.

Private Sub cmdSearch_Click() 
    Dim strStudentRef As String 
    Dim strSearch As String 
 
'Check txtSearch for Null value or Nill Entry first. 
 
    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then 
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" 
        Me![txtsearch].SetFocus 
    Exit Sub 
End If 
'--------------------------------------------------------------- 
 
'Performs the search using value entered into txtSearch 
'and evaluates this against values in Address 
 
    DoCmd.ShowAllRecords 
    DoCmd.GoToControl ("Address")
    DoCmd.RunCommand acCmdFilterBySelection
 
    Address.SetFocus 
    strStudentRef = Address.Text 
    txtsearch.SetFocus 
    strSearch = txtsearch.Text 
 
'If matching record found sets focus in Address and shows msgbox 
'and clears search control 
 
    If strStudentRef = strSearch Then 
        Address.SetFocus 
 
 
    'If value not found sets focus back to txtSearch and shows msgbox 
        Else 
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ 
            , "Invalid Search Criterion!" 
            txtsearch.SetFocus 
    End If 
End Sub

still no luck... so what do you think the next step would be?

[puppydogbuddy]

I just have to analyze alternative methods, and use one that would not require focus in the field to be searched [Address]....one that will do the search based on what is entered in the textbox. i will get back to you asap, no later than tomorrow.

[didacticone]

I just have to analyze alternative methods, and use one that would not require focus in the field to be searched [Address]....one that will do the search based on what is entered in the textbox. i will get back to you asap, no later than tomorrow.

ok great! thanks again!

[puppydogbuddy]

I forgot to ask you whether you are using the single Form view or continuous form view. If you use the single form view and get several matches on your search, you are only going to be able to display the one at a time. There are loads of options to deal with this.

For example .....You can have 2 forms associated with the same table, one for single matches and another for multiple matches. With code, you can count the number of records returned and based on returns > 1, switch to a continuous form view.....blah, blah.

With the code changes included below, you will have the capability of getting partial matches...for example if you were to type 124 in the test box, you should get every address that has 124 embedded in it. Keep in mind that the code is untested and probably will have to be debugged. You need to substitute your actual table name or query name where I used an illustrative object name.

Let me know how it goes.

Private Sub cmdSearch_Click()  
    Dim strStudentRef As String  
    Dim strSearch As String  
    Dim  strSql As String   
 
'Check txtSearch for Null value or Nill Entry first.  
 
    If IsNull(Me![txtsearch]) Or (Me![txtsearch]) = "" Then  
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"  
        Me![txtsearch].SetFocus  
    Exit Sub  
End If  
'---------------------------------------------------------------  
 
'Performs the search using value entered into txtSearch  
'and evaluates this against values in Address  
 
    DoCmd.ShowAllRecords  
    strSql  = "Select Address from [YourTable]  Where [Address] Like '" & "*" & Me!txtSearch.Value & "*" & "';"
    DoCmd.RunSQL strSql
 
    Address.SetFocus  
    strStudentRef = Address.Text  
    txtsearch.SetFocus  
    strSearch = txtsearch.Text  
 
'If matching record found sets focus in Address and shows msgbox  
'and clears search control  
 
    If strStudentRef = strSearch Then  
        Address.SetFocus  
 
 
    'If value not found sets focus back to txtSearch and shows msgbox  
        Else  
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _  
            , "Invalid Search Criterion!"  
            txtsearch.SetFocus  
    End If  
End Sub