Opening a Form based on information from another form

[caconklin]

Access 2003, Windows XP.

Currently I am able to open a form - case form - based upon information input by the user in another form - search form. I would like to be able to open the case form based on infromation from the search form or a click from another form. I can get the query to execute, however it only works if both conditions are met. Is there a way to get around this?

SELECT [Case Data].[Case Number]
FROM [Case Data]
GROUP BY [Case Data].[Case Number]
HAVING ((([Case Data].[Case Number])=[forms]![Start]![Search]))
OR
((([Case Data].[Case Number])=[forms]![Part Information]![Contract]![Case Number]));
 

Any help would be appreciated!

Thanks,
Cady

[NeoPa]

I understand what you're doing. I don't understand what you're trying to do :S

"based on (...) a click from another form"??

[caconklin]

Let me see if I can explain this better without being too verbose! There are 2 main forms in the database and there needs to be multiple ways to enter those forms. The forms are Part Information and Case Information. The user can enter either a part number or a case number in the Search form and display either the Part information form or the Case information form. The user should then be able to click on the Case Number located in the Part Form and open the Case Information form or vice versa. So I added the query from my first post. This should open the Case Information form based on either a case number being entered from the search form, or the click on the case number from the Parts Information form. The way I have written it, the query needs both pieces of information before it will execute and then it returns the first number encountered. Long and short of it, Instead of having multiple copies of the same form that use information - as the result of a click, or entering into a search field - to open the proper record, I want one form and multiple ways to pass it that information. I may be approaching this from the entire wrong direction so any direction will be helpful!

[caconklin]

Or possibly better stated enter a case number into the search field of the search form, or click on the case number field in a subform in the form Parts Information. Hope this clarifies things.

[DonRayner]

Create yourself a public variable in a standard module

Public MyVar as string

Create a public function in either the same or another standard module

Public Function MyFunction()
MyFunction = MyVar
End Function

Use this function as the critera for your query then all you have to do is set the Variable wherever you need in your forms

[NeoPa]

Ah. I think I get you now.

I suggest what you do is design your case form to show all records (remove the HAVING clause from the query it is bound to).

Now you can use a filter (WhereCriteria parameter of the DoCmd.OpenForm line) to specify which record to include.

You can also pass a value to the form itself, essentially allowing the OnOpen code to reset the filter (or RecordSource) on the fly.

The typical way to limit records on a form is to apply a filter like this rather than to design the criteria into the SQL (or query). It's easier in the long run.