Unbound text box to filter sub form datasheet

Question

I have a Microsoft Access (2010) form with a sub-form datasheet embedded. My goal is to have an unbound text box above each datasheet column that allows the user to type text to filter that column by.

One of the datasheet columns, VENDOR, displays a list of vendor names based on the tblVendor table. It is bound to the datasheet by a VendorID column that is invisible to the user.

I use the following code, but it only seems to query the datasheet on the VendorID column. I'd like to query the text. For example, I want the user to type in "Microsoft", as opposed to "21" (Microsoft's VendorID).

Private Sub TxtVendorSearch_Change()

Dim str1 As String

str1 = "[VendorID] LIKE '*" & Me.TxtVendorSearch & "*'"


Me!subOrderDS1.Form.Filter = str1
Me!subOrderDS1.Form.FilterOn = True

End Sub

KekuSemau KekuSemau · Accepted Answer · 2012-10-26T22:15:20

If have not done so already, just include (by join if necessary) the [Vendor]-Name in the query for the Subform. You can then just query [Vendor] LIKE ... instead of [VendorID].
To make it real-time, you will also have to use Me.TxtVendorSearch.Text, because the textbox's value is not immediately in sync with the visible text.

Unbound text box to filter sub form datasheet

1 Answers