How do you add text to an access report?

Microsoft Access Tips for Casual Users

Provided by Allen Browne.  Last updated: April 2010.

Report

There is a very simple way to number records sequentially on a report. It always works regardless how the report is sorted or filtered.

With your report open in Design View:

1. From the Toolbox (Access 1 - 2003) or the Controls group of the Design ribbon (Access 2007 and later), add a text box for displaying the number.
2. Select the text box, and in the Properties Window, set these properties:

That's it! This text box will automatically increment with each record.

Form

Casual users sometimes want to number records in a form as well, e.g. to save the number of a record so as to return there later. Don't do it! Although Access does show "Record xx of yy" in the lower left ofthe form, this number can change for any number of reasons, such as:

• The user clicks the "A-Z" button to change the sort order;
• The user applies a filter;
• A new record is inserted;
• An old record is deleted.

In relational database theory, the records in a table cannot have any physical order, so record numbers represent faulty thinking. In place of record numbers, Access uses the Primary Key of the table, or the Bookmark of a recordset. If you are accustomed from another database and find it difficult to conceive of life without record numbers, check out What, no record numbers?

You still want to refer to the number of a record in a form as currently filtered and sorted? There are ways to do so. In Access 97 or later, use the form's CurrentRecord property, by adding a text box with this expression in the ControlSource property:

    =[Form].[CurrentRecord]

In Access 2, open your form in Design View in design view and follow these steps:

1. From the Toolbox, add a text box for displaying the number.
2. Select the text box, and in the Properties Window, set its Name to txtPosition. Be sure to leave the Control Source property blank.
3. Select the form, and in the Properties Window set the On Current property to [Event Procedure] .
4. Click the "..." button beside this. Access opens the Code window.
5. Between the lines Sub Form_Current() and End Sub, paste these lines:

    On Error GoTo Err_Form_Current
        Dim rst As Recordset

        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        Me.txtPosition = rst.AbsolutePosition + 1

    Exit_Form_Current:
        Set rst = Nothing
        Exit Sub

    Err_Form_Current:
        If Err = 3021 Then   
            Me.txtPosition = rst.RecordCount + 1
        Else
            MsgBox Error$, 16, "Error in Form_Current()"
        End If
        Resume Exit_Form_Current

The text box will now show a number matching the one between the NavigationButtons on your form.

Query

For details of how to rank records in a query, see Ranking in a Query

Keeping track of random ideas can be challenging if you don’t write them down when they occur. If you’re updating an Access form, for example, you may wish to jot down a quick note about the form or its data. Instead of writing your idea down on a piece of paper, you can attach typewritten notes directly to the form. These notes, which don’t become part of the Access database, stay with the form and appear when you view them.

Add Note

1. Launch Microsoft Access and open a database that contains one or more forms. Form names appear in the Forms section on the left side of the user interface.

2. Double-click a form to view it in the Design window. Right-click an empty space on the form in the Design window and select “Design View.” Access displays the form in Design view.

3. Click the “Design” tab on the Access toolbar and click the “Text Box” control. Click an empty area on the form, hold down your left mouse button and draw a small rectangle on the form. A text box appears.

4. Press F4 to open the Property Sheet window. Click the window’s “Other” tab to display the Expression Builder window. Type text into the window’s text box and surround the text with quotation marks, as shown in the following example:

   “This is my note”

5. Click “OK” to close the window and return to the form. Click the text box you placed on the form and drag it to the desired location. Click one of the handles along its edges and drag the handle to resize the text box if you want to make it larger or smaller.

View Note

1. Right-click an empty area on the form and select “Form View” The new text box will appear in the location you placed it.

2. Depending on the text box’s size, it may display your entire note or only a single text character.

3. Right-click an empty part of the form and select “Design View.” Press “F4” to make the Property Sheet window visible if you cannot see it.

4. Click the text box you created to select it and move to the Property Sheet window. Click the button next to the Default Value label to view the Expression Builder window. It will display all the text in your note.