Multiple Search criteria for 1 query

[browny]

Hi all
I have a query linked to a report that prints a worksheet specific to a individual work item. This report/query picks up the Work_ID value on a form. I have 2 other forms displaying the same work with different amounts of detail. Rather than create a new report/query to run from each form, I am trying to use the one query/report from each form.
The problem is that I cannot get Access to recognise the Work_ID value from the other forms. I have tried the following:

• In the Work_ID criteria field building an SQL statement as below
  [Forms]![frmVCRUpdate]![Work_ID] Or [Forms]![frmVCRShort]![Work_ID] Or [Forms]![frmVCRLong]![Work_ID] - This does not work, it keeps asking for the frmVCRUpdate Work_ID value when I try to run the query from the other forms
• Adding 2 extra Work_ID Values to the query and on the 2nd and 3rd criteria lines specifying that it look for the Work_ID value from the other forms but I get the error above.

Any suggestions on how I can make this work would be appreciated, I'm not sure what else to do.
Craig

 

[Banana]

Access is asking you for "which" Work_ID you want to use since there are no criteria explicitly supplied.

from the form, you need to input the criteria, so by inserting the code below on the button where you run the query,

Dim str as String
str= (**Insert your SQL here** WHERE Work_ID=" & Me.ControlWithWork_ID.Work_ID & ");)"

Docmd.RunSQL str

This will pass the criteria without having to input it yourself.

 

[browny]

Thanks Banana, works a treat. Has taken a while to get back to this problem but its all better thanks to your advice.
Craig