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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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,343
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,449
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,437
Messages
5,831,625
Members
430,077
Latest member
CoulterM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top