Join Curt Frye for an in-depth discussion in this video Setting the caption and background picture, part of Up and Running with VBA in Access.
The basic forms and reports access creates are just that--basic. If you want to customize them you can use two of the Me Objects properties to do so. The Caption property controls the text that appears on the form or report's title bar, while the Picture property contains a reference to the object's background picture. In this movie, I'll show you how to use those two properties to control your form or report's appearance. For this exercise, we'll use the Orders report, so I'll right-click it in the Navigation pane and then click Design View.
Doing so opens the report in Design view. Now I'm going to right-click anywhere in the body of the report, then click Build Event. Then in the Choose Builder dialog box click, Code Builder, and then click OK. When I do, Access creates a new code module for the Detail_Format event, in other words, if we were to change the format of the detail section of the report. That's not actually the event that I want to trigger this code. Instead, I want this code to run when the report is opened.
So I'm going to go up to the Object list, which is just below the title bar, click the down arrow, and then click Report. Report is the object from which I want to choose my event. And the event that appears by default is the Report_Open event, and that is the one I want to use. Now I'll press Enter twice and the up arrow key once to give myself a little bit of room to work. Now I'm going to type in the code that will set a value for the caption, and that is Me. and then the Caption property, Caption =, then double quotes, and whatever you want to appear on the caption or appear on the title bar.
In this case, I want that to be orders in the last year, so it would be Orders in the Last Year and then double quotes again. I'll press Ctrl+S to save my work and then press Alt+F11 to go back to the main database window. Now with the Order Report still there, I'll press Ctrl+W to close it. And now in the Navigation pane, I'm going to double-click Orders Report to open it.
When I do, the code that I just assigned to the Report_Open event should run, and we should see the new value on the title bar. So I'm going to double-click Orders Report, and there we see it, Orders in the Last Year. The other change that I'd like show you is how to add a background image, and the background image uses the Picture property. I'm going to right-click the tab for the Report and then click Design View. Doing so opens the report in Design view.
Now I'll press Alt+F11 to go back to the Report_Open event module and then press Enter. The code to set a background image for a form or a report is Me.Picture =, then double quotes, and then you're going to need the full path, or directory file structure, that leads to the image you want to appear in the background. In this case, I need to go to my Exercise Files folder, so I'm going to press Alt+Tab until my desktop is displayed, and then double-click Exercise Files, and then double-click Chapter09. And the file that I want to use is Confidential.png.
But I can't just type it in, and obviously I don't see the entire path anywhere on the screen-- it's not easy to get at. But what you can do is right-click the image that you want to set the link to and then in the shortcut menu, click Properties. In the Properties dialog box, you will see a Location value, and it has the path to your file. Notice that the mouse pointer is a cursor. It looks like an I bar at this point. That means that you can select that text, so Users\Curt\Desktop\Exercise Files\ Chapter09. Then press Ctrl+C to copy it, and then Alt+Tab to go back to the VBA Module.
Then press Ctrl+V to put in the path, at least to the directory that contains the image, and now you need to type the name of the image with the file extension. So that would be a backslash. That's the slash that leans with the bottom on the right and the top to the left. Then this image is called Confidential.png and then a double quote, because all of that is a string. Now I'll press Ctrl+S to save my work and then press Alt+F11 to go back to the main database window. And now I'll right- click the Report title bar and click Close.
Now when I double-click the Orders Report to open it, we see that we have Orders in the Last Year and also Confidential. Now, Confidential is somewhat obscured by the data and the row structure within the report, but the image is there, and you can play around with the formatting to make that image either stand out more or drop more to the background. Setting the caption or background image for a form or report can communicate information about the object and make it easier to use. Never underestimate the power of appearances, but be sure your background image doesn't distract the user from your data.
- Working with macros and managing macro security settings
- Creating, exporting, and deleting code modules
- Running a VBA procedure
- Debugging VBA code
- Defining variables, constants, and calculations
- Displaying a calculated result in a message box
- Repeating tasks with loops
- Creating conditional statements
- Manipulating database objects, such as forms, tables, and queries
- Adding, editing, and deleting records
- Looking up values in a table
- Requerying and repainting forms