CODEWITHSUNDEEP
ms-accessms-access-2013
Ethan
Ethan

Reputation: 51

Filter subform table with textbox

I am trying to filter a subform with a textbox.

I have a query to show table records in the subform and I have the criteria to filter the table, but when I type in the textbox to filter the sub form it only shows me one record with that name. I need it to show me all the names.

The criteria for my query is below.

Like "*" & [Forms]![frmPlanningForecast]![FETextbox].[Text] & "*"

I then have an OnChange event on the textbox to requery the subform.

As mentioned above I need it to show me all the records matching what i have typed, not just one.

When I use the filter option within the table itself(Dropdown box on the field header) and select the filter from there, it works great. But I need it to be typed into a textbox.

The picture attached will show you what I mean, I have "EQ" typed in the text box but it has only returned 1 record when their are 15 called "EQ" on the table. Example

Upvotes: 0

Views: 2117

Answers (3)

PeterW488
PeterW488

Reputation: 1

In older versions of Access, didn't there used to be an option in the query to use the sum field something like this: [Formnane].[TextField]

I know that's very simplistic and I don't fully recall how to use it but it was something like that, simple and straight forward if you're not a VB user. Forgive my lack of conviction, I've used it before but it was 20 years ago.

Upvotes: -1

Ethan
Ethan

Reputation: 51

I found an easier method and just wanted to let others know.

Instead of using criteria I used the following vba code.

DoCmd.ApplyFilter , (FETextbox = qryPlannedHours.LeadFE), SubFormPF

Upvotes: 1

Sergey S.
Sergey S.

Reputation: 6336

First of all add single quotes around Like parameter: 

Like "'*" & [Forms]![frmPlanningForecast]![FETextbox] & "*'"

and second - Access has an old bug: if you refer in the query to form field like you did, it doesn't renew the value, used for criteria during Requery, you always will receive the same results. Workaround - replace reference to field by global function, which returns textbox value or use dynamic generating of RecordSource for subform.

Global function example:

Public Function GetFE() As Variable
    GetFE = [Forms]![frmPlanningForecast]![FETextbox]
End Function

Place it in any standard VBA module. Then your Like will look like this:

Like "'*" & GetFE() & "*'"

Upvotes: 1

Related Questions