open a report with this sample #, print then close!!

[smelly]

I have a form where I am trying to open a report from a check box. The form has a sample ID and I am trying to run the report from the query for this sample ID. How can I pass this to the report so the report opens for this sample ID then prints, then closes.
Thanks!

 

[Steve101]

To do this, reference the sampleid control on the form, in the query's sql, as demonstrated below (assuming the name of the text control on the form is SampleId):

SELECT *
FROM qryYourQuery
WHERE SampleId = Forms!frmYourForm!SampleId

Replace the formname and control name as appropriate.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[smelly]

Thanks, I am now getting a compile error, it doesn't like the first line with the asterisk.

Private Sub checkbox1_Click()
DoCmd.OpenReport "InternalLab1b", acViewPreview
SELECT *
FROM Query1b
WHERE [Sample ID] = Forms!ChainOfCustody!BBCID
End Sub

 

[Steve101]

No Smelly, you've misunderstood my intent. Try the following:

(a) Remove the
SELECT *
FROM Query1b
WHERE [Sample Id] = Forms!ChainOfCustody!BBCID

lines from the above code. The Sub should then just look like:

Private Sub checkbox1_Click()
DoCmd.OpenReport "InternalLab1b", acViewPreview
End Sub

(b) Go into design view of the Query1b query, and in the query grid, make sure that you have a [Sample Id] field displayed as a column in the grid.

(c) In the criteria cell below the [Sample Id] field, enter the reference to the Form control; ie.:
Forms!ChainOfCustody!BBCID

(d) Save the query. I assume that Query1b provides the recordsource for the report.

That should now associate the form parameter to the report, via the query.

Hope this clarifies things,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[smelly]

Sorry, I am just learning. Thank you for your help. I put it in the criteria below sample ID but it wants me to input the number instead of it being passed in when I click.