Form based on query which takes parameters from text boxes on that form

Ultimately the purpose is to allow the form to be filtered, (there's also a Refresh button on form). It is working well for me now, but I haven't seen this use of parameter queries before.开发者_如何学JAVA I know I could use other methods (access standard functionality, use Filter/FilterOn properties with vba), but technically, is there a reason not to do this?

---

I'm not sure I understand your question, but parameterizing the SQL of your form to refer to the filter criteria controls on the form means you have to jump through hoops to display anything other than data that is filtered on all the parameterized fields.

The requery objection makes no sense to me, since you'd have to do a requery to filter the form based on the values in your controls, unless you're writing the recordsource instead of requerying it. If you're changing the form's recordsource, then I see no reason to use parameters referrning to the form controls that are used for typing in criteria.

Setting the form's Filter property is by far the easiest way to do that, but it assumes you've started with a recordsource that displays all the records you'd be selecting from, and that may be inefficient, or just a bad design.

So, basically, my recommendation would be to not use parameters at all, but to write the SQL dynamically and set the form's recordsource each time.

Parenthetically, in order to avoid the display of a blank form, you can use the trick of a TOP 1 query like this:

  SELECT TOP 1 Null As Field1, Null As Field2
  FROM MySmallestTable

That would have one record with Nulls in all the fields so your bound controls won't display error messages. It also makes the form uneditable without having to change the form's editing properties.

---

Well, I'd imagine you could write a VBA function to requery from the database based on an unbound text box in your form, though filter is a bit better suited for performance reasons. The only reason I can think of that you might not want to requery is if you are pulling a LOT of data from a MDB file on a network drive on a slow network with a lot of concurrent users; simply because the performance would take a severe hit. I could be wrong though