Search Button (1 Viewer)

MatthewB

Member
Local time
Today, 04:47
Joined
Mar 30, 2022
Messages
85
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:47
Joined
May 7, 2009
Messages
19,169
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

Super Moderator
Staff member
Local time
Today, 11:47
Joined
Feb 19, 2013
Messages
16,553
you also have a space after your first %

'% "

as Arnel says' it should be a *
 

MatthewB

Member
Local time
Today, 04:47
Joined
Mar 30, 2022
Messages
85
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:47
Joined
May 7, 2009
Messages
19,169
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

Member
Local time
Today, 04:47
Joined
Mar 30, 2022
Messages
85
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

Super Moderator
Staff member
Local time
Today, 11:47
Joined
Feb 19, 2013
Messages
16,553
you could try


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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:47
Joined
May 7, 2009
Messages
19,169
It should work?
I can only see 1 strataplannr field.
 

CarlettoFed

Member
Local time
Today, 12:47
Joined
Jun 10, 2020
Messages
119
After "StrataPlan_T" there is an extra point the correct code is the following

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

MatthewB

Member
Local time
Today, 04:47
Joined
Mar 30, 2022
Messages
85
Thanks. Just as a general query on this problem, when does one use a ! Instead of a . In creating a path
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 19, 2002
Messages
42,970
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

Member
Local time
Today, 04:47
Joined
Mar 30, 2022
Messages
85
Screenshot 2022-06-18 141525.png

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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 19, 2002
Messages
42,970
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

Member
Local time
Today, 04:47
Joined
Oct 31, 2014
Messages
30
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

Member
Local time
Today, 04:47
Joined
Oct 31, 2014
Messages
30
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.

Code:
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




customer-form.JPG
 

MatthewB

Member
Local time
Today, 04:47
Joined
Mar 30, 2022
Messages
85
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
Screenshot 2022-06-19 163706.jpg
 

CarlettoFed

Member
Local time
Today, 12:47
Joined
Jun 10, 2020
Messages
119
Maybe if you attach an example file, saying what the offending mask is, you do it first.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 19, 2002
Messages
42,970
When you get prompts like this, Access is telling you it can't find something. Always check your spelling.
 

piano_playr

Member
Local time
Today, 04:47
Joined
Oct 31, 2014
Messages
30
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.

Code:
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
 

Attachments

  • Search_Button.accdb
    896 KB · Views: 156

Gasman

Enthusiastic Amateur
Local time
Today, 11:47
Joined
Sep 21, 2011
Messages
14,038
I think you should switch the filter on and off?, you are relying with that code that filteron = True ?
 

Users who are viewing this thread

Top Bottom