Filter bound to Textbox for table not filtering

[BrainExplodingFromCoffee]

Good Afternoon,

These forums have been great for helping me learn about VBA and Access. I'm very new to access, but I'm a seasoned excel/VBA user. So, I just need a little bit of guidance.

Access Version: 2010

My Goal: Use a text box to specify the filter parameter for a table embedded within the form.


My Problem: The value entered is not matched against the primary ID.
Things I noticed: If I use the "Filter Selection" button in the "Home" section of the Access ribbon, it finds the record with the primary key. And I choose the option that reads, " Equals: 'X.1234.123456' "

Below is the code: The first bit just calls a module. The second one is where all of the action is happening.

Private Sub txtFlt_PrimaryKey_AfterUpdate()

Dim strSource As String

Util.TableFilter

End Sub

Public Sub TableFilter()

Dim i As Integer
Dim strList As String

If IsNull(Form_Create.txtFlt_PrimaryKey.Value) Then
    strList = ""
Else
    strList = Form_Create.txtFlt_PrimaryKey
    strList = Format(strList, Text)
End If

If strList <> "" Then
    Form_Create.qry_QueryTable.Form.Filter = "[Primary Key] " & "IN (""'" & strList & "'"")"
    Form_Create.qry_QueryTable.Form.FilterOn = True
Else
    Form_Create.qry_QueryTable.Form.Filter = "[Primary Key] " & "IN " & "(""*"")" & ""
    Form_Create.qry_QueryTable.Form.FilterOn = True
End If

End Sub

An example of the primary key is X.1234.123456.

At first I thought it was the "." that might be throwing off the VBA commands, but if I use the "Selection/Filter" button; then it works. The primary key is stored, passed and processed as a text value.

Can someone help me out?

Thanks,
CoffeeExplodingFromBrain

[Perceptus]

Is [Primary Key] a field name? Or are you looking to do this with a primary key from that datatable?

[BrainExplodingFromCoffee]

Hi Perceptus,

[Primary Key] is the field name. I just want to query something like

SELECT * FROM Create WHERE [Create.Primary Key] IN ("x.1234.123456")

Thanks,
CoffeeMakesBrainExplode

[BrainExplodingFromCoffee]

Hi Perceptus,

I found the problem. My structure, binding, and all of the other special stuff that makes this stuff work was good. The only problem was with the way I was declaring the criteria parameter for the IN statement. I changed the code to look like this:

Form_Create.qry_QueryTable.Form.Filter = "[Primary Key] " & "IN (""" & strList & """)"

Appreciate your attention. Thanks for working with me on this. Your questions helped me pin point the answer. rep++

Thanks!
Coffee+Brain = Brain Goo(Everywhere)