Solved: Filter By Form with VBA

[Eric58132]

Hi All,

I originally posted a question about this in this thread:
http://www.vbaexpress.com/forum/showthread.php?t=35008

which I later closed out because I thought everything was good. Well, a problem has risen...

I was able to get my code to work as long as I'm searching within text fields, but when it is a number field I'm searching on, I am asked to enter a parameter. A portion of my code looks as follows:

Dim SearchVariable As String
SearchVariable = Me.SearchCriteria.Value
If SearchVariable = "Street Name" Then
Me.Filter = "[Street Name] Like '*" & Me.Searchbox & "*'"
Me.FilterOn = True
ElseIf SearchVariable = "Job Number" Then
Me.Filter = "[Job Number] Like '*" & Me.Searchbox & "*'"
Me.FilterOn = True


In this instance, the "Street Name" option works, but the "Job Number" one does not.
Any ideas?

Thank you!

[HiTechCoach]

What is the name of the parameter that it is asking for?

Is the field in the form's record source?

[Eric58132]

your post got me thinking...and I then found my very noobish mistake. While the object in the form is called "Job Number", the field in the table is called "Job ID".

/sigh.


learned something though, the name "filter by form" threw me off. They should really call it "filter table fields through a form", haha.

[HiTechCoach]

Thanks for the update.

Glad to hear that you got it figured out.

TIP: I prefer to name/rename the controls on forms/reports so that do not match the name of fields in the form/report's record source. Just like you got confused, at times so will Access.

See: Access Naming Conventions (http://www.hitechcoach.com/index.php?option=com_docman&task=doc_details&gid=36&Itemid=28) ** I have been using this since it was first published. I do use a different method for naming tables and their fields that I started using before the first IBM PC was even made. The key is consistency.