Filter subform table with textbox

Question

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.

It doesn't only show one record. It shows 173, so your filter cannot be operational. Also, use the Value property, not the Text property in the filter. — Gustav

Sergey S. Sergey S. · Accepted Answer · 2017-05-30T10:38:30

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() & "*'"

Filter subform table with textbox

2 Answers