Running a report from a button in a form Access 2003

Topic

New Reply

I am using Access 2003.

I have a form to enter information from customer orders. There is a sub form in the main form where you enter each row of the order. The main form refers to a table called tblPurchaseOrder, the sub form displays information is a column format, data entered in this sub form is linked to a table called tblJobs.

I have a report that I need to run for each job (rptMouldingRequet). When I open the report it asks for a job number and then populates the report from this. Someone else created this report.

What I would like to do however is have a button on the form that allows me to select a job number row from the subform and print the report from this. This is easier than having to firstly find the job number and then go into the report etc.

I can create buttons without a problem – only trouble is they don’t do anything!

Can anyone please help me? I’m not sure where to start.

Thank you.

Reply | Quote

Replies

Open the form in design view.
Create a new command button, or if you have already created one, select it.
If the Command Button Wizard starts, cancel it.
Activate the Properties window.
Activate the Other tab of the Properties window.
Change the Name property to cmdReport.
Activate the Event tab of the Properties window.
Click in the box labeled “On Click”.
Select [Event Procedure] from the dropdown menu.
Click the … button to the right of the dropdown arrow.
This will open the Visual Basic Editor and show something like this:

Private Sub cndReport_Click()

End Sub

with the insertion point in the emppty line in between.
Press the Tab key once, then copy/paste or type the following line

DoCmd.OpenReport “rptMouldingRequest”, acViewPreview, , “[JobNumber]=” & Me![SubformName]![JobNumber]

where JobNumber is the name of the job number field, and SubformName is the name of the subform as a control on the main form (this is not necessarily the same as the name of the subform in the database window).

The above assumes that JobNumber is a number field. If it is a text field, change it to

…, “[JobNumber]=” & Chr(34) & Me![SubformName]![JobNumber] & Chr(34)

Chr(34) is the double quote character “.

Reply | Quote