Command Button - Printing Form

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
Hi,

If someone could help me with two procedures regarding forms and command buttons, I'd be most appreciative. Currently have a form to retrieve comparison data retrieved by a combo box. I'd like to be able to print this form and data as well as a formal report. How would I program command buttons to:

Button #1) Print current form and data without printing all records?

Button #2) Open a report and carry current form data to report.

I appreciate your help.

Thank you.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Nancy

I suspect buttons 1 and 2 would end up doing the same thing - unless you can specificy exactly what would be different.

In any case, I think you would need to use VBA to do this. The basic code to achieve this would look something like this :

Code:
Private Sub Button2_Click()

On Error GoTo ErrHandler

Dim tmp As Integer

'Assumed the unique ID is an integer, use your actual field name!
'MyUniqueID is the unique record identifier (i.e. key field) from the form
tmp = Me.MyUniqueID
    
If tmp = 0 Then
    'Nothing selected 
    Exit Sub
Else
    'MyUniqueField is the same identifier within the report
    DoCmd.OpenReport "MyReportName", acViewPreview, , "[MyUniqueField] = " & tmp
End If
        
ExitHere:
    Exit Sub

ErrHandler:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume ExitHere
    
End Sub

This code would be attached to the OnClick event of the 2nd button. Make sure you use your actual button, field and reports names.

HTH, Andrew :)
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
Thank you Andrew. Actually both buttons would be doing different functions.

Button #1 would be labeled "Print Current Form" and would print the current record displayed on the current form only and not all records in the db. Currently if I select the printer icon when I have data displayed on a form, all records in the db print. I would like it to be limited to the current view only.

Button #2 would be labeled "Open Report" and would open a totally different layout in a formal report appropriate for board of directors to review. I'm assuming this is what the VBA you provided is for. I'll give it a shot in the next few days and let you know my results.

Thanks so much for the input. I would appreciate any direction you could give to the printing issue for button #1 if possible.

Thanks again.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Nancy
The code I supplied was for printing a single record. You could use this for button 1. Is button 2 all records or just the selected record?
Andrew
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,061
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Andrew

I think Nancy actually wants to print the form with a single record.

Personally I wouldn't do that, printing forms isn't particularly pretty.

What I would do would have 2 reports, one that simulates the format of the form and another for the formal report.

I would use code like Andrew posted to print a single record for each report.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi Norie
I agree 100% and I guess that's where I was heading without actually stating it. Printing forms raises other issues such as layout, length of the form / information versus the page etc. As you say, it's not pretty....

I should have been a little more explicit.
Andrew :)
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
Hi guys,

Yes, Norie, exactly right, I want to print one record only. Andrew/Norie, I agree with the idea of printing a report that mimics the form, and actually suggested it to my boss, but I wasn't sure how to bring the information forward to the report. I over thought this process and didn't reason that I only needed one button and not two.

I know what I want in a db and I can be very creative, but I lack the code knowledge and ability to reason things out without getting carried away. I don't know the power of Access yet or terminology to be able to look things up in reference manuals, but I'm learning thanks to this site and guys like you who are generous enough to share your knowledge. Thank you.

I won't know for a few days yet, but I can't wait to see it in action.

This site is great and it's really great to talk to experts that actually produce results. Thanks so very much.
 

nancybrown

Well-known Member
Joined
Apr 7, 2005
Messages
868
Hi Norie and Andrew.

I tried your suggestion . . . it worked perfectly!

Thank you so much. One resolution down and a million more to go.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,817
Messages
5,574,499
Members
412,598
Latest member
Haven1234
Top