We help IT Professionals succeed at work.

Run-Time Error  2465 in Filter of form

ggodwin
ggodwin asked
on
I've been working on this function all day and finally getting it close. ( i think )

I have a form with a subform that is hosting a query. I'm trying to filter the query
based on a selection from my Combo box.

I'm getting a Run-Time Error 2465
Microsoft Access Can't find '|1referred to in your expression



Option Explicit
Dim sPartsListFilter As String
-------------------------------------------------------------------------------------------
Sub ApplyFilter()
sPartsListFilter = ""

If Me.cboAccronym.ListIndex <> -1 Then
    If sPartsListFilter = "" Then
    sPartsListFilter = "[Accronym]='" & Me.cboAccronym & "'"
    Else
    sPartsListFilter = sPartsListFilter & " And [Accronym]='" & Me.cboAccronym & "'"
    End If
End If
--------------------------------------------------------------------------------------------
Me.[qMasterPartsList].Form.Filter = sPartsListFilter
Me.[qMasterPartsList].Form.FilterOn = True
End Sub
--------------------------------------------------------------------------------------------
Private Sub btnClearFilter_Click()
Me.cboAccronym = ""
ApplyFilter
End Sub
--------------------------------------------------------------------------------------------
Private Sub cboAccronym_AfterUpdate()
ApplyFilter
End Sub
--------------------------------------------------------------------------------------------
Comment
Watch Question

Ryan ChongSoftware Team Lead

Commented:
try this instead:

Sub ApplyFilter()
    sPartsListFilter = ""
    
    If Me.cboAccronym.ListIndex <> -1 Then
        sPartsListFilter = "[Accronym]='" & Replace(Me.cboAccronym, "'", "''") & "'"
    End If
    
    Me.[qMasterPartsList].Form.Filter = sPartsListFilter
    Me.[qMasterPartsList].Form.FilterOn = True
End Sub

Open in new window

ggodwinQuality Engineer

Author

Commented:
Well...

The error went away for the it didn't filter by the selection.
Ryan ChongSoftware Team Lead

Commented:
what's the data type for Accronym?
ggodwinQuality Engineer

Author

Commented:
Acronym is Short Text

I'm pulling the data via a query.

So I'm trying to filter a query. I don't know if that's the problem or what.
John TsioumprisSoftware & Systems Engineer

Commented:
Just put  a breakpoint here :
Me.[qMasterPartsList].Form.Filter = sPartsListFilter

Open in new window

and examine what values the sPartsListfilter carries (immediate window --> ? sPartsListfilter )
If you are unable to "see" it just take the output from immediate window and use it on the RecordSource of the form in question are Criteria
ggodwinQuality Engineer

Author

Commented:
Thank you for your support.

Can you explain a little more deeply what you are recommending?
Software & Systems Engineer
Commented:
I recommend to actually get the output of sPartsListFilter and use it as criteria on your query
Ryan ChongSoftware Team Lead

Commented:
The problem could probably at the form's RecordSource which John has highlighted.

@ggodwin

Are you able to share the value of RecordSource property with us?
ggodwinQuality Engineer

Author

Commented:
Just to update everyone. I believe my problem was what John outlined.

However, i actually went a different direction.

Ultimately, I removed the query and built the data with a table.

By using the table, I was actually able to use the built in filter controls from the table. There was NO need to do via the combo box.