Opening Report via Parameter

[Treefarn]

So I have a report that opens via Parameter. The SQL behind the query that runs the report is (I took out alot of lines that aren't necessary to answer the question)

PARAMETERS [Enter Your Box Id] Short;
SELECT DocumentsTable.OrganizationalID,
.
.
.
DocumentsTable.Status
FROM DepartmentsTable INNER JOIN (Year1 INNER JOIN DocumentsTable ON Year1.ID = DocumentsTable.RecordDateYearID) ON DepartmentsTable.ID = DocumentsTable.DepartmentID
WHERE (((DocumentsTable.Voided)<>'Y' Or (DocumentsTable.Voided) Is Null) AND ((DocumentsTable.ID)=[Enter Your Box ID]));



So if I click on the report, I get a little popup that says "Enter Your Box ID", and when I do, the report works just as expected.

However, I also want to be able to open this report via link from another report, and pass the Box ID #. I just can't get the syntax right. I would have thought it was this:


Private Sub ID_Click()
DoCmd.OpenReport "Find A Box", acViewReport, , "[Enter Your Box Id]=" & Me.ID
End Sub


However when that execute, I still get the pop up asking for "Enter Your Box ID"

 

[Minty]

Private Sub ID_Click()
DoCmd.OpenReport "Find A Box", acViewReport, , "[Enter Your Box Id]=" & Me.ID
End Sub

However when that execute, I still get the pop up asking for "Enter Your Box ID"

Your aren't passing the parameter in correctly. Your Print report Where should be

DoCmd.OpenReport "Find A Box", acViewReport, ,"[DocumentsTable.ID]= " & me.id

Then remove the AND ((DocumentsTable.ID)=[Enter Your Box ID]))
from you SELECT statement

 

[Treefarn]

If I remove the parameter, then click on the 'Find a Box' report and getting a popup to enter a Box ID will no longer work.

 

[Treefarn]

The report is meant to serve two purposes. One where the user will be prompted to enter a box #, and one where I will link to it and pass a box #. I think your suggestion will cause the first purpose to no longer work, as nothing will prompt the user for a box #.

 

[Minty]

There are a number of ways to do this. If your user is opening the report from a command button you can prompt them to enter the ID number and pass it it the same way.

You could also use the OpenArgs method on the report - pass in the ID and if there isn't an OpenArgs Value you could prompt for it from the report.

There are lots of examples on here - if you get stuck on a method please ask for more help.