using VBA to set criteria in a query and refresh a corresponding subform

[ironj32]

I have a Main Form, "frmBuildingSearch", which has a subform "frmBuildingSearchSub". frmBuildingSearch has a couple of input text boxes & 1 command button: txtCity, txtSite, txtBuilding, cmdSearch.
I want the command button to refresh my subform (which is run from a query, "qryBuildingSearch") based upon the text that is in the text boxes.

I would like to use VBA to do this.

If there is any value in txtCity, then I would like to set the criteria of my City field in the query to:
Like "*" & [Forms]![frmBuildingInfo]![txtCity] & "*"

The same would go for any value in txtSite and txtBuilding.

I'm not quite sure how to go about doing this. Your help is greatly appreciated!

[mattj]

See this post: http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=176367#post176367

And here too: http://mdbmakers.com/forums/archive/index.php?t-6293.html

It shows how to edit the querydef in VBA to change the parameters. I think it would actually be easier to just change the recordsource in VBA, then try to use a stored query and change the params.

HTH
Matt

[ironj32]

Thanks Matt!

Actually, I was just thinking about it and I think that I may be better off just filtering the form.

Private Sub FilterSearch()
Me.Filter = "City =" & Me.textSearch
Me.FilterOn = True
End Sub

The problem with this is that I am getting an Enter Parameter Pop Up box that says whatever value I have typed into textSearch. I need it to filter for whatever value is typed in there. And also I would like it to filter records based on all fields, not just the City field.

Any thoughts?

[mattj]

If you want to filter on whatever fields they type in, then you will have to build the filter string dynamically. It would involve checking each field to see if it contains a value, and then concatenating the different choices together with the appropriate AND/OR keywords and punctuation.
The obvious problem with the string you have posted is probably the punctuation. It appears that city is a text field, if so, you need something like
Me.Filter = "City ='" & Me.textSearch & "'"

[ironj32]

Thanks for your help Matt.
Okay I have that. Now I need it to search for any part of the text. Right now they have to spell it exactly right. If they wanted to find Minneapolis, I would like for them to just be able to type "min" and then in would show every city that has "min" in it.