Buttons on Reports 2
By Richard Rost
3 years ago
Adding Clickable Buttons to Reports, Part 2
This is Part 2 of 2. In this Microsoft Access tutorial, I'm going to teach you how to put a button on a report. While it might seem like you should never need to do this, it has some surprisingly good uses. We'll use that button to open a form to go to the related record for the data shown on the report. I'll also show you how to use a hyperlink with an event instead of a button.
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Recommended Courses
Keywords
TechHelp Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Adding Clickable Buttons to Reports, Button on Report, Open Form, Hyperlink with Event, Docmd.openform, Can't find the field referred to in your expression, transparent button, detail section on click event, hyperlink finger
Intro In this video, you will learn more techniques for adding clickable buttons to reports to edit records in Microsoft Access. I will show you how to create transparent buttons that cover entire records, discuss using On Click events for report sections and fields, and demonstrate how to use the hyperlink pointer to make interactive elements more intuitive for users. You will also see how to adjust formatting options like underline display to refine your report's usability. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
Today is part two of my Adding Clickable Buttons to Reports to Edit Records in Microsoft Access. If you haven't watched part one, go watch part one first. You'll find a link down below you can click on—go watch it and come on back.
We now join the tutorial already in progress.
That sounds a lot like the "you never have a second cup of my coffee." Well, you can with a single form with some VBA coding. That's a topic for another video, but with a continuous form you cannot—if you change one text box, you change them all.
Now that that's working, if I go into this form here, you can see we go to our Reading tab. You can make this the default now if you want to and just get rid of this. In here you can just go click and there's your form to open it. Edit it—that's pretty cool. See, buttons on reports have their place.
I should also mention that this only works in Report View. It does not work in Print Preview. If you take this and try to open it in Print Preview, you're not even going to see the buttons. This is only for printing it or converting it into a PDF. It has to be in Report View, which is very similar to Form View, so you can't add the data.
Now, you say you don't want this button here—it's kind of ugly. Can we do something else? Yes, there are all kinds of things you can do. For example, you could make this a transparent button and put it over the whole record.
Remember this from a couple of weeks ago—put transparent buttons over images on your forms to make them do cool stuff? The same thing works in reports. Take this button, put it over whatever you want to be clickable. Let's say the whole thing—click anywhere in that area.
Now open up its properties. Double-click on the border around it. By the way, don't double-click in here because then you get the caption. Right-click on the button and then double-click on the border.
Go to "Visible"—don't change that. Visible is different from transparent. Transparent is down here. I used to make this mistake all the time myself. Visible makes it so it's not even there—you can't click on it. Transparent is what you want. See, it goes away, but it's still there; it's just transparent.
Save it, close it, and open it. Switch to Reading mode and now click anywhere over this and boom—it figures that button. You're scrolling, you're scrolling—come here, scroll, click—boom, there you go. That's another option.
Another thing some people will try to do—let's say you don't want to use the button at all. I'm going to delete the button. See, I just selected it, delete—boom, button gone.
Now you could use a Click event on something. We've put click events on fields before, we've put them on sections. If you put the click event in the Detail section, let's try that. Right-click, Build Event.
We want to go to the Detail On Click event, not Detail Format—that happens when the section gets drawn. But since we don't have that button anymore, let's move this down here. We can delete this. We don't have that button. Same code, but it's in the Detail Click event.
Now save it, close it, and let's open up the report. If I click here, I'm clicking, I'm clicking—nothing's happening. Oh, there it goes, it fired—what happened?
The On Click event for the Detail section only fires if you click on the background. If you look closely, you have to click anywhere there isn't a control. So like this spot right here is where I clicked, that is actually the detail section of the report or form. Works the same way—if you click on a control, it doesn't fire the event in the section.
I don't like using that as a way to trigger that. If I want them to be able to click on the whole thing, you have to either put an event in all of these controls or do something different. That's why I like the transparent button—it goes over everything nicely.
Now, how about instead of the Detail section, let's put it in the On Click event for the date. Let's actually format this. Let's make it blue. We can take advantage of the fact that we've got others, so I'm going to use it.
Let's get that event out of here. We'll put it in here, so we'll open up this guy, go to your On Click event, move this code into there. You can leave it in both spots if you want to, that's fine, I don't care. They're like my Legos, but you can put them together however you want.
Now the event is just in the date. If we open this up now, you can see if I click here, if I click here—I have to click on the date and it fires it. If you want to make this more intuitive, how about we use the hyperlink finger.
Remember the hyperlink finger I showed you before? Watch my AccessLearningZone look—we get the hyperlink finger, and if you click on that it takes you to my website. You can use the same hyperlink finger in your database.
How do you do that?
All you have to do is come in here, open up this guy's properties, go to Format, and you're going to find hyperlink stuff down here.
Now, leave Is Hyperlink to "No." It's not actually a hyperlink, it's just a date field. Hyperlink target—leave it blank. You're not going to do anything. You just want to change the Display as Hyperlink to "Always." You always want to see that finger—that's what that triggers.
The event that you wrote in the On Click is going to take care of opening up that form, so you don't need a hyperlink target—unless you're trying to send them to a website or something. So do that, just set Display as Hyperlink.
Save it, close it, let's open it up again and now look at that. See—the little finger. Click—there you go, and it works in here. Beautiful.
If you don't want the underline—if you don't want that hyperlink underline, that's just a formatting change. Just come in here, click, go to Format, turn the underline off, and save it.
There you go.
Sometimes I like the underlines, sometimes I don't. On my website I've got it turned off in most places. I think the underline is annoying. The underline was cool back in, you know, 1997 when people didn't know what hyperlinks were, but now if it's blue pretty much you can click on it. That's why I do that thing in my forms.
Which one do I have—like the customer list? I make this blue to indicate you can click on it or double-click on it in this case.
That's pretty neat.
Do you like this stuff? Do you like learning this stuff? Come to my website and check out my developer lessons. I've got tons and tons of lessons to teach you how to be a better programmer. You want to learn VBA and how to build awesome databases? There's a link—come check it out.
That's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends.
I'll see you next time.Quiz Q1. Why do buttons on reports in Access only work in Report View and not in Print Preview?
A. Report View allows interactive elements, while Print Preview is for printing or PDF export only
B. Print Preview runs VBA code, disabling buttons
C. Buttons are visible in both views but only clickable in Report View
D. Buttons always print regardless of view chosen
Q2. What is the main benefit of making a button transparent in a report?
A. It hides the button from view while keeping it clickable
B. It deletes the button from the report
C. It makes the button clickable only in Print Preview
D. It disables the button's click event
Q3. What is the difference between the Visible and Transparent properties for a button in Access?
A. Visible hides the button completely, while Transparent makes it invisible but still functional
B. Transparent disables the button, Visible makes it clickable
C. Visible only works for text boxes, not buttons
D. Transparent removes the button from the report
Q4. What happens if you place a Click event on the Detail section of a report?
A. The event only fires if you click the background, not on any controls
B. The event fires whenever the report opens
C. The event fires every time you click any control in the section
D. The event cannot be assigned to the Detail section
Q5. How can you make an entire area of a report record clickable using a button?
A. Place a transparent button over the whole area you want clickable
B. Add a click event to each control individually
C. Place a visible button only in the corner of the area
D. Use the Print Preview mode
Q6. What is the recommended way to indicate that a date field in a report is clickable?
A. Change the field color to blue and set Display as Hyperlink to Always
B. Remove the field underline and set Is Hyperlink to Yes
C. Add a visible button next to the date field
D. Use Print Preview mode to show the hyperlink finger
Q7. If you do not want the underline typically shown on a hyperlink, what should you do?
A. Change the field's formatting to remove the underline
B. Set Is Hyperlink to No
C. Set Display as Hyperlink to Never
D. Delete the field and add a new one
Q8. Why might using a transparent button be preferred over setting Click events on multiple controls?
A. It allows you to trigger actions for an entire area with one control
B. It prevents accidental activation of events
C. It is required for Print Preview to display correctly
D. Transparent buttons are easier to print
Q9. What is the purpose of setting Display as Hyperlink to Always on a field?
A. To show the hyperlink cursor (finger) when hovering over the field
B. To convert the field value to a clickable web address
C. To hide the field from the report
D. To disable all click events on the field
Q10. When does the On Click event of a control fire in an Access report?
A. Only when you click directly on that control in Report View
B. When you open the report
C. When you switch to Print Preview
D. When you scroll through records
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone explores more ways to add clickable buttons to reports to let you edit records in Microsoft Access. This is part two of my lesson on this topic, so if you have not seen the first part, I recommend going through that before you continue.
Let me pick up where we left off last time. I previously compared this to that old commercial line, "you never have a second cup of my coffee." In contrast, with a single form and some VBA coding, you can allow certain functionality, but with a continuous form, changes made to one text box will affect them all. That limitation only allows so much flexibility.
Now that we have the basics working, let me show you how it looks in action. When entering the desired form, you can head to the Reading tab and set it as the default if you wish. This setup allows access to your edit form directly from the report, which I find pretty convenient. As you can see, adding buttons to reports can definitely be useful.
One important detail is that this technique works exclusively in Report View. It will not function in Print Preview mode. When opening the report in Print Preview, the buttons will not even appear. Print Preview is intended for printing or PDF conversion, and does not allow for interactive elements like buttons. In Report View, which is similar to Form View, interactivity is possible, although you cannot add data with this technique.
Now, you might think the standard button does not offer the look and feel you want. Maybe you find it unattractive and want to change things up. There are several different approaches you can try. For example, you can create a transparent button and position it such that it covers the entire record. This allows you to click anywhere in the area and trigger your action. I have shown a similar method recently for placing transparent buttons over images on forms, and this same idea works in reports. Just place your button where you want on the report and make it transparent to the user.
To change the button's property, access its properties and look for the "Transparent" setting. Remember, setting "Visible" to "No" will make the button unclickable because it will not be there at all. Instead, use the "Transparent" choice. This will make the button invisible to the user while still retaining functionality. Once done, you can save the report, close it, open it up again, and now clicking anywhere that the button covers will trigger the same event as before. It is a versatile method that helps give the appearance of a more interactive interface.
If you want to avoid buttons altogether, you can also use events assigned directly to a field or a section. For instance, you can put a click event on the Detail section of your report. You have probably seen that fields and even sections can have their own events. If you assign your VBA code to the Detail section's On Click event, that code will run when you click the background of that section. However, there is a limitation. The On Click event for the Detail section triggers only when you click somewhere that does not contain a control, meaning you have to click the blank parts of the background. If you click directly on a text box or other control, nothing will happen. Because of this, I do not recommend depending on section click events if you want users to be able to click anywhere conveniently. This is one reason why I prefer placing an invisible button over the entire record area instead.
Another option is to assign the click event directly to a specific control, like a date field. You could take advantage of color formatting by making the date field blue. Move your event code into the On Click event of the date field, and you can leave it in other places as well if you want some flexibility. Now, when users open the report, clicking that date field will fire your code, opening forms or making edits as needed.
To make this even more intuitive, you can give the field the appearance of a clickable hyperlink. Users are accustomed to seeing the "hyperlink finger" when something is clickable, just like the icons used on my website to link to further resources. You can accomplish this by setting the control's properties so that it "Displays as Hyperlink." Even if it is not a true hyperlink, you just want to show that visual cue for users. Leave "Is Hyperlink" as "No" and keep the "Hyperlink Target" blank. By setting "Display as Hyperlink" to "Always," users get that familiar finger pointer when they hover over the field. Your On Click event will handle actually performing the action, such as opening a related form.
If you don't like the typical hyperlink underline, you can turn it off in the formatting options. Sometimes I use underlining, but many times I find it distracting or unnecessary, especially now that clickable items are commonly indicated by color alone. You may have seen on my customer list that I simply use blue to show that you can click or double-click for more details.
If you enjoy learning how to make your databases more interactive and user-friendly, I encourage you to visit my website and explore my developer lessons. I have a wide range of tutorials to help you improve your Access skills, learn VBA, and create powerful databases.
You can find a complete video tutorial with step-by-step instructions covering everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Making buttons transparent on reports
Using transparent buttons to make whole areas clickable
Setting button properties for transparency
Difference between Visible and Transparent properties
Placing transparent buttons over report sections
Using the On Click event of the Detail section
Limitations of section-level On Click events
Assigning On Click events to individual controls
Formatting a control to look like a hyperlink
Using the hyperlink finger cursor on report controls
Setting Display as Hyperlink for controls
Removing underline from hyperlink-styled fields
Formatting controls to indicate clickability
Opening a form to edit a record from a report
Button functionality limited to Report View |
