Open a single form using different queries/parameters

[scottdg]

I currently have a form that is set to open with the results from a query that searches for a unique ID number. I want to add additional parameters to the form the query is launched from such as date or name. Ideally I would like to do this from a single query is possible. If not I can set up multiple queries with a different parameter for each. But what I really want is to open the same form regardless of which parameter is being used for the search. What is the best way for a form to open using a different record sources dependent on where the query is launched from?



Thanks in advance.

Scott

[rpeare]

There are a few things you can do.

1. Create your form based on a query that has intrinsic criteria that will not change (your unique ID number), when you open the form based on the query, apply a FILTER to the form to only show a subset of the records that you're interested in.
2. Use an unbound form, then you can use the same fields to request a search that you'd use for data entry and perform the search on a button click rather than when the form opens.
3. Hard code your query based on selected items on a form prior to opening your data entry form i.e. if you're search for a first name or last name in the query driving your form you'd have a criteria of:

like forms!formname!fieldname & "*"

This would find any records where the name started with the characters entered or all records if left blank.

[JoeM]

Here is a little trick that I use.

I will create a query that has all the fields and calculations that I may want on my Form, but with no criteria at all. Let's call it "BaseQuery".
Create another query based on "BaseQuery" which simply returns all fields. Let's call this "SelectionQuery".
Use this "SelectionQuery" as the Record Source for your final Form. Let's call this "FinalForm".

I then create an Unbound Form where I allow my user to specify all the criteria that they may possibly want (so I think things like TextBoxes, ComboBoxes, CheckBoxes, etc). This is my Criteria Selection Form, so let's call it "CriteriaForm".
Then, I take the user selections and build the SQL Code for the criteria that I need using VBA (it is easy to see what the criteria needs to look like - just create a manual query that has that criteria and switch to "SQL View" to see what the SQL Code needs to look like.
Then, I add that SQL Code for the criteria to SQL code which returns all the fields from my "BaseQuery", i.e.

Dim mySQL as String
mySQL = "SELECT [BaseQuery].* FROM [BaseQuery] WHERE " & myCriteria

Then, in my VBA code, I will assign this new SQL code to the "SelectionQuery" and open my Form, i.e.

CurrentDb.QueryDefs(SelectionQuery").SQL = mySQL
DoCmd.OpenForm ("FinalForm")

So everything is run from your "CriteriaForm" where they will enter there criteria and click a Command Button which will return the records they want. So you are not changing the Record Source name each time, you are simply changing the SQL code that the "SelectionQuery" query runs.