1 Form with 3 print (report) buttons... Please help.

[dmon2010]

Hi,

I'm still pretty new to Access. Using 2007. I need to print 3 different reports. My form has 3 print report buttons. When I click each button, it allows me to print all records in the table. I only want to print the current record. I was able to find the following code that allows me to print the first report properly (only the current record).

Private Sub Command101_Click()
On Error GoTo ErrHandler
DoCmd.OpenReport ReportName:="BoL", View:=acViewPreview, _
WhereCondition:="ID=" & Me.ID
Exit Sub


ErrHandler:
' Don't show error message if report was canceled
If Err <> 2501 Then
MsgBox Err.Description, vbCritical
End If


End Sub

Where "BoL" is the name of the 1st report I need printed.




However when I tried using the same code on the other 2 buttons, it doesn't work and prompts me to enter the parameter value (ID). I guess I can only use the code once?

Also thought about using a single button for all 3 reports, but not all records need all 3 reports. Some need 1 or 2. So a button for each is ideal.

Any help would be REALLY appreciated here... thanks in advance...

[pbaldy]

You're not limited that way. Is ID a field in the other reports? The parameter prompt is Access telling you it can't find something.

[dmon2010]

ID is a field in the table, but it's not actually on any of the reports. It's just the autonumber primary key field. I found the code itself after searching online. Here's what I found:

Let's say the record source of your form and of the report(s) has a numeric primary key field ID (for example an AutoNumber field).
Open the form in design view.
Select a command button.
Activate the Event tab of the Property Sheet.
Click in the On Click event.
Select [Event Procedure] from the dropdown menu.
Click the builder dots ... to the right of the dropdown arrow.
Make the code look like this, substituting the actual names:
PrivateSub cmdOpenReport1_Click()
OnErrorGoTo ErrHandler
DoCmd.OpenReport ReportName:="rptMyReport1", View:=acViewPreview, _
WhereCondition:="ID="&Me.ID
ExitSub

ErrHandler:
' Don't show error message if report was canceled
If Err <>2501Then
MsgBox Err.Description, vbCritical
EndIf
EndSubwhere:
cmdOpenReport1 is the name of the command button.
rptMyReport1 is the name of the report to be opened.
ID is the name of the field that uniquely identifies the record.
If this field is a text field instead of a number field, use
...
WhereCondition:="ID="& Chr(34)&Me.ID & Chr(34)
...

---

Regards, Hans Vogelaar

I don't really know if that's the code I should be using, but it seems to work. Problem is that it just won't work for the other 2 reports. All 3 reports are based on the same table.

[pbaldy]

But is that field included in the record source of the other 2 reports? Can you post the db here?

[dmon2010]

Sure... But I don't know how... Can you tell me how to post my DB here? It'd be great if you could take a look at it for me.

[dmon2010]

I think I found it but it says my file's too large. 3.37MB

[pbaldy]

Usually if you do a compact/repair then zip it will be small enough.

[dmon2010]

Database2_Backup.zip Maybe this works...

[dmon2010]

I'm on the east coast and have to split for lunch but will be back in about an hour. I really really appreciate your time and your help... see you soon.

[pbaldy]

The first report is based directly on the table, so it obviously includes the ID field. The other two reports on both based on SQL statements, neither of which includes the ID field. Just add it to both and you should be good to go.

[rpeare]

If you put the ID on the two non functioning reports (set the visible property to FALSE) you'll be fine, just like pbaldy said.

[pbaldy]

If you put the ID on the two non functioning reports (set the visible property to FALSE) you'll be fine, just like pbaldy said.

Did I not say it well enough? It doesn't need to be on the report at all, just in the record source.

[rpeare]

I posted that before your follow up pbaldy, and I was strictly going by going to the design view> field list, ID shows up there so I added it and got the expected result. Two ways to accomplish the same thing.

[dmon2010]

That worked perfectly!!! Thank you so much for your help!!! I seriously can't thank you enough...

[dmon2010]

How can I mark this as solved?