Allow user to Filter on Form with Button 2

[appelq]

There may be a better way to solve the problem and I am open to any ideas.
In short the problem:
I have a form that tracks "Containers" when they come to our yard.
A container may come and go many times or the course of time.
My user would like to enter the Container Number and have the form show him all of the Instances of that Container Number.

So I added a button on the form [Search] with this code:
Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strName

strName = InputBox("Enter Container No to Find: ")

strSQL = "SELECT ContID FROM dbo_tblContainers " & _
" WHERE ContNo = '" & strName & "';"

Me!Filter = strSQL
Me!FilterOn = True

End Sub

I get an error:
Runtime error 2465 Can't find the field "Filter" referred to in your expression.
Debug drops me on the line : Me!Filer = strSQL

Will also need a way to easily remove the filter once they are done.

Thanks,
Chuck

 

[dhookom]

The filter property should only be the stuff that immediately follows the "WHERE ". I would provide a combo box to allow the user to select a container number.

Sub cmdSearch_Click()
 Dim strSQL As String
 Dim strName
 
 strName = InputBox("Enter Container No to Find: ")
 
 strSQL = "ContNo = '" & strName & "';"

 Me!Filter = strSQL
 Me!FilterOn = True
 
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[appelq]

Thanks! That did the trick.
Chuck

 

[MajP]

Thanks! That did the trick

That is not completely true. You had to fix something else.

The original problem was here
Me!Filter = strSQL
Me!FilterOn = True

The bang operator can be used with collections or field names, but not properties. Filter and FilterOn are properties.
That is exactly why the error says you cannot find a field named "filter", because Me!filter would mean you are referring to a field named filter. In order to make this work you would have also had to change it to
Me.Filter = strSQL
Me.FilterOn = True

 

[Andrzejek]

appelq,
Now you need to give 2 stars for the help received.

Click on "Great Post? Star it" to award the stars

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[appelq]

MajP,
You are correct. I noticed that as an aside and changed the ! to . on both lines.

Thanks,
Chuck