Search Button

[MatthewB]

I am coding a search button on a form based on a Query. If I want my search to return on the value of one of the table fields does the code need to refer to the Query or the table?

The on click code
Me.SearchText.SetFocus
Me.FilterOn = False
Me.Filter = " [Calendar_Strata_1Q].[StrataPlanNr] like '% " & SearchText & "%' "
Me.FilterOn = True
Me.Requery

The StrataPlanNr originates in the StrataPlan_T. The Query references Calendar_T and StrataPlan_T
SearchText is an unbound text box.

The event produces zero results.

Thanks

 

[arnelgp]

you don't need to include the Table name/query name in your filter if
StrataPlanNr belongs to just one table.
and use "*" instead of "%"

Me.Filter = "[StrataPlanNr] like '*" & SearchText & "*'"

 

[CJ_London]

you also have a space after your first %

'% "

as Arnel says' it should be a *

 

[MatthewB]

This doesn't work. The form is a join table Calendar_T and StrataPlan_T. The StrataPlanNr comes through a foreign key. The code says something like StrataPlanNr could be from more than one table in SQL. What should I do? Strip out my data to protect the privacy of my db and then share it? Thanks

StrataPlanNr belongs to just one table.
and use "*" instead of "%"

Me.Filter = "[StrataPlanNr] like '*" & SearchText & "*'"s

 

[arnelgp]

can you show the SQL string of your query?
you can rename the other StrataPlanNr in your query using Alias, eg:

Select StrataPlan_T.StrataPlanNr, Calendar_T.StrataPlanNr As PlanNr ...

then you can use the Filter, since there is only 1 StrataPlanNr.

 

[MatthewB]

SELECT Calendar_T.kp_Calendar_ID, Calendar_T.kf_StrataPlan_ID, StrataPlan_T.StrataPlanNr, Calendar_T.CalendarMonth, StrataPlan_T.SearchItem, Calendar_T.Service_Project, Calendar_T.Cycle, Calendar_T.CalendarSchedule, Calendar_T.LastTimeDate, Calendar_T.NextTimeDate, Calendar_T.[Last Contractor], Calendar_T.Notes, Calendar_T.Current, Calendar_T.RemindDate, StrataPlan_T.StrataPlanName
FROM StrataPlan_T INNER JOIN Calendar_T ON StrataPlan_T.kp_StrataPlan_ID = Calendar_T.kf_StrataPlan_ID
ORDER BY StrataPlan_T.StrataPlanNr, Calendar_T.CalendarMonth, Calendar_T.Service_Project;

 

[CJ_London]

you could try


Me.Filter = "[StrataPlan_T.].[StrataPlanNr] like '*" & SearchText & "*'"

 

[arnelgp]

It should work?
I can only see 1 strataplannr field.

 

[CarlettoFed]

After "StrataPlan_T" there is an extra point the correct code is the following

Me.Filter = "[StrataPlan_T].[StrataPlanNr] like '*" & SearchText & "*'"
Me.FilterOn = True

 

[MatthewB]

Thanks. Just as a general query on this problem, when does one use a ! Instead of a . In creating a path

 

[Pat Hartman]

Dot is used to prefix Access Objects or properties.
Bang is used to prefix User defined objects.

The exception is that with forms and reports, all the columns of the RecordSource are added to be properties of the form/report so Me!somename and Me.somename are equivalent with ONE big difference. Objects prefixed with a bang are not resolved until execution time whereas objects prefixed with dot are resolved at compile time. So, I NEVER use Me!somename when referring to controls of the form/report or RecordSource because I always want them resolved at compile time so I get the error rather than the user getting it later and wondering why I missed the error That's why you'll never get an error for Forms!myform!mycontrol until you try to execute the code.

That means I would use Me.SearchText. That does two things.
1. it gives you intellisense
2. it clarifies that you are referring to a control or field from the RecordSource rather than a variable defined in VBA which can make the logic easier to follow for someone unfamiliar with the application.

 

[MatthewB]

SELECT Calendar_T.kp_Calendar_ID, Calendar_T.kf_StrataPlan_ID, StrataPlan_T.StrataPlanNr, Calendar_T.CalendarMonth, Calendar_T.Service_Project, Calendar_T.Cycle, Calendar_T.CalendarSchedule, Calendar_T.LastTimeDate, Calendar_T.NextTimeDate, Calendar_T.[Last Contractor], Calendar_T.Notes, Calendar_T.Current, Calendar_T.RemindDate, StrataPlan_T.StrataPlanName
FROM StrataPlan_T INNER JOIN Calendar_T ON StrataPlan_T.kp_StrataPlan_ID = Calendar_T.kf_StrataPlan_ID
ORDER BY StrataPlan_T.StrataPlanNr, Calendar_T.CalendarMonth, Calendar_T.Service_Project;

As this is an Inner Join and the StrataPlanNr comes through a kp /kf relationship does the pathway need to be qualified differently?
The StrataPlanNr populates the kf_StrataPlan_ID field through a LookUp.

I put the StrataPlanNr in the Query aswell - I am not sure whether the field needs to be in the query to be accessible to the code.
In the png above it suggest that StrataPlanNr is in both the StrataPlan_T and Calendar_T

Just to be clear, this is a continuous form.

 

[Pat Hartman]

I would rebuild the query. I think it is corrupted. As long as you didn't select StrataPlanNr from both tables, there should not be a conflict.

If you are using the querydef as the RecordSource of a form, you might want to delete it from there before you rebuild it. Then put it back. Don't rebuild the query from within the form Open the form, remove the RecordSource. Close the form. Rebuild the query. Open the form and put the querydef back.

 

[piano_playr]

I’m having a little difficulty visualizing how you have designed this form. You mentioned it is a continuous form. It would be very helpful to look at a screenshot of the form. Would you please forward a screenshot?

 

[piano_playr]

I like to reset the form's recordset when doing what you're suggesting. This doesn't answer your specific question. Sorry. This is a suggestion to consider as an alternative.

I think your form looks something like the below image. There is a button and a text box to hold search data; the on_click event resets the form's record source (recordset). The ADO code assumes you have a local Access table.

Private Sub btnSearch_Click()
    
    On Error GoTo My_Error_Handler
    
    Dim strSQL As String
    Dim strCountry As String
    Dim strCriteria As String
    Dim rstCustomers As ADODB.Recordset
    
    strCountry = Me.SearchText
    strCriteria = "WHERE ((CountryRegionName="
    strCriteria = strCriteria & "'" & strCountry & "'))"
    strSQL = "SELECT     Sales_vIndividualCustomer.BusinessEntityID, " & _
                        "Sales_vIndividualCustomer.FirstName, " & _
                        "Sales_vIndividualCustomer.MiddleName, " & _
                        "Sales_vIndividualCustomer.LastName, " & _
                        "Sales_vIndividualCustomer.PhoneNumber, " & _
                        "Sales_vIndividualCustomer.CountryRegionName " & _
                        "FROM Sales_vIndividualCustomer "
    strSQL = strSQL & strCriteria
    
    Set rstCustomers = New ADODB.Recordset
    rstCustomers.CursorLocation = adUseClient
    rstCustomers.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Set Forms("Sales_vIndividualCustomer").Recordset = rstCustomers
    
    rstCustomers.Close
    Set rstCustomers = Nothing
    
    Me.SearchText = ""
    Me.SearchText.SetFocus
    
Exit_Point:
    Exit Sub
    
My_Error_Handler:
    
    MsgBox _
    "An unexpected error has occurred.  The system's description " & _
    "of this error is:" & vbCr & vbCr & _
    "Error " & Err.Number & ": " & Err.Description, _
    vbExclamation, _
    "Unexpected Error"
    
    
    Resume Exit_Point
    
    
End Sub

 

[MatthewB]

I would rebuild the query. I think it is corrupted. As long as you didn't select StrataPlanNr from both tables, there should not be a conflict.

If you are using the querydef as the RecordSource of a form, you might want to delete it from there before you rebuild it. Then put it back. Don't rebuild the query from within the form Open the form, remove the RecordSource. Close the form. Rebuild the query. Open the form and put the querydef back.

Thanks.

I would rebuild the query. I think it is corrupted. As long as you didn't select StrataPlanNr from both tables, there should not be a conflict.

If you are using the querydef as the RecordSource of a form, you might want to delete it from there before you rebuild it. Then put it back. Don't rebuild the query from within the form Open the form, remove the RecordSource. Close the form. Rebuild the query. Open the form and put the querydef back.

Pat Hartman
I di as you suggested and get this... set parameter

 

[CarlettoFed]

Maybe if you attach an example file, saying what the offending mask is, you do it first.

 

[Pat Hartman]

When you get prompts like this, Access is telling you it can't find something. Always check your spelling.

 

[piano_playr]

I think I got it to work for you. You are trying to set a filter on your form. The SQL behind the form is fine. You only need to properly set the "Filter" or "where clause". Your event should look something like the code below. I copied your code and commented out what is unnecessary. It works! The Access 2016 file is also attached. You'll need to do a few other tasks like give user the ability to clear the filter at runtime and to clear the filter on form load.

Private Sub btnSearch2_Click()

    On Error GoTo My_Error_Handler
    
    Dim strSearchText2Value As String
    Dim strFilter As String
    
    strSearchText2Value = Me.SearchText2
    strFilter = "((CountryRegionName='" & strSearchText2Value & "'))"
        
    With Me
        '.SearchText.SetFocus
        '.FilterOn = False
        .Filter = strFilter
        '.FilterOn = True
        '.Requery
    End With
    
    Me.SearchText2 = ""
    
Exit_Point:
    Exit Sub
    
My_Error_Handler:
    
    MsgBox _
    "An unexpected error has occurred.  The system's description " & _
    "of this error is:" & vbCr & vbCr & _
    "Error " & Err.Number & ": " & Err.Description, _
    vbExclamation, _
    "Unexpected Error"
    
    
    Resume Exit_Point
    

End Sub

 

[Gasman]

I think you should switch the filter on and off?, you are relying with that code that filteron = True ?