Solved Trouble creating a filter on a form displaying records built in a query (1 Viewer)

[p_bij]

I'm trying to do a simple filter on a form using a combo box. The form is continuous and the data is from a query (Q_ActiveFiles). The Detail section of the form has text boxes for each field type (i.e. ID, FileNumber, FileName, etc.). I have a combobox in the Form Header that is build from another query (Q_ActiveFileNumbers) and I have an event procedure on after update with the following code:

Option Compare Database
Option Explicit

Private Sub F_Num_AfterUpdate()

Me.Filter = "[Forms]![F_ActiveFilesSearch]![D_Num]=""" & [Forms]![F_ActiveFilesSearch]![F_Num].Value & """"
Me.FilterOn = True

End Sub

I'm trying to filter the records by the number (textbox name is D_Num) which I search for in the combobox (named F_Num).

When I run this anything I bring up in the search brings up only the New Record, i.e. nothing.

Any help would be appreciated!

 

[mikenyby]

What is the name of the field that D_Num is bound to? Ensure that the field is in the form's source query.

Assuming the name of the form that you are working on is F_ActiveFilesSearch, try

Me.Filter = "[Name of field D_Num is bound to] =" & Me.F_Num

 

[p_bij]

Thanks, that worked!

 

[mikenyby]

My pleasure. When setting a filter in VBA, what you're writing out in the module is parsed as a string. You only need to enclose the parts of the string that don't change in quotes. You must connect any variables with an ampersand.

Additionally, using me.[ObjectName] instead of writing out the entire form name saves time and makes a code that is easier to read with human eyes when you have to revisit it to work out the kinks.

 

[p_bij]

My pleasure. When setting a filter in VBA, what you're writing out in the module is parsed as a string. You only need to enclose the parts of the string that don't change in quotes. You must connect any variables with an ampersand.

Additionally, using me.[ObjectName] instead of writing out the entire form name saves time and makes a code that is easier to read with human eyes when you have to revisit it to work out the kinks.

Thanks, this is good to know! Very helpful!