Data Management

Return to the record you were working on when you reopen an Access form

If you get interrupted while working on a record in an Access form, finding your way back to it can be time-consuming. Here are two ways to bookmark the current record so that you can hop straight to it when you reopen the form.

 

 

When you have to take a break from work you're doing in Access, you might be tempted to leave the database open so you can quickly pick up where you left off. However, that's not always a good idea, especially if the current form displays sensitive data. Other times, you might need to access another form or report in the same database and then return to the current task later. Either way, you have to find the way back to a specific record to continue working — and that takes time. Here's an easy way to return to a specific record, which can benefit you as well as your users.

Note: This information is also available as a PDF download.

The simplest (but somewhat limited) solution

Bookmarking a record for later use isn't difficult, and there's more than one way to get the job done. The easiest way is to declare an application-life variable to store the current record's primary key, or some other identifying value, and then grab that variable when you reopen the form. It takes almost no code and it works great — until you close the database.

Figure A shows the Orders form in Northwind (the sample database that comes with Access) with an additional command button in the footer section. However, you can use almost any form. The only restriction is that each record needs some unique identifying value. In most cases, you can use a primary key.

Figure A: The command button in the footer bookmarks the current record.

record bookmark

To add the command button, open the form in Design view and choose Form Header/Footer from the View menu. Insert a command button and name it cmdReturn. To make the button work, click the Code button to launch the form's module and enter the code in Listing A and Listing B. (If you're working with the Orders form in Northwind, don't alter the existing code.) Save the form.

Listing A: Command button's Click procedure

Private Sub cmdReturn_Click()   'Set module-level variable   'to identify current record.   If IsNull(varID) Then     MsgBox "Can't bookmark this record; " _      & "There's no OrderID value.", vbOKOnly, "Error"     Exit Sub   End If   varID = Me.OrderID   Exit Sub errHandler:   MsgBox Err.Number & ": " & Err.Description, _    vbOKOnly, "Error" End Sub

Listing B: Form's Open event procedure

Private Sub Form_Open(Cancel As Integer)   'Open form to saved position.   Dim strBookmark As String   If Not IsEmpty(varID) Then     strBookmark = "OrderID = " & varID     Debug.Print strBookmark     With Me.RecordsetClone       .FindFirst strBookmark       If Not .NoMatch Then         Me.Bookmark = .Bookmark       End If     End With   End If   Exit Sub errHandler:   MsgBox Err.Number & ": " & Err.Description, _    vbOKOnly, "Error" End SubDon't forget to delimit the filtering string component correctly. In this case, that's the following statement:

strBookmark = "OrderID = " & varIDThis time, the uniquely identifying value, OrderID, is a Numeric value, so the statement requires no delimiters.

Insert a standard procedure (choose Module from the Visual Basic Editor's Insert menu) and enter the following declaration:

Public varID As VariantSave the module and close it. Then, return to Access and open the form in Form view. Use the Navigation toolbar to move to any record other than the first one. Note the record's OrderID value. For instance, that value for the second record is 10692. Click the button you added to the Orders form (see Figure A). Doing so passes the OrderID value for the current record to the application-life variable varID. This step might seem extraneous, but you can't create an application-life variable in a form module. You need a standard module for that.

Now, close the form and reopen it. The form's Open event concatenates the application-life variable, varID, with the field's name to create a filtering string that the FindFirst method uses to identify the record in question. Then, the code sets the form's Bookmark property to that of the matching record and the form jumps to the bookmarked record when you open it. If you close the database, the application-life variable, varID, loses its value, which negates the technique.

A session-to-session solution

If you need Access to remember the marked record from session to session, use a table to store the record's identifying value instead of using an application-life variable. Then, use VBA to retrieve that value and open the form accordingly. This method requires a little more code and a special table that does nothing but store the current record's identifying value. If someone accidentally deletes the table, the technique won't work. Hiding the table is a good way to prevent that type of error, but you must remember to document your choice well, so you'll remember what you did later.

First, you'll need a table, so create a single-field table with a Numeric field named OrderIDSaved. Name the table tblOrderIDSaved and close it. Remember, when applying this to your own database, you might need more than one field to uniquely identify each record. If that's the case, you must store the multiple values in the table.

Open the example form's module and comment out or delete the code from the previous example (cmdReturn and the form's Open procedure). Next, enter the procedures in Listing C and Listing D and save your changes. Then, save the module and return to the form in Form view. Navigate to a record other than the first one and click the Record To This Record button. Close and reopen the form.

Listing C: Command button's click procedure

Private Sub cmdReturn_Click()    'Store record's bookmark value in tblOrderIDSaved.   Dim rst As ADODB.Recordset   Set rst = New ADODB.Recordset   On Error GoTo errHandler   rst.Open "tblOrderIDSaved", CurrentProject.Connection, _     adOpenDynamic, adLockPessimistic   With rst     .AddNew       !OrderIDSaved = Me.OrderID.Value     .Update   End With   rst.Close   Set rst = Nothing   Exit Sub errHandler:   MsgBox Err.Number & ": " & Err.Description, _    vbOKOnly, "Error" End Sub

Listing D: Form's Open event procedure

Private Sub Form_Open(Cancel As Integer)   'Set form to saved position.   Dim rst As ADODB.Recordset   Dim strBookmark As String   Set rst = New ADODB.Recordset   On Error GoTo errHandler   rst.Open "tblOrderIDSaved", CurrentProject.Connection, _    adOpenDynamic, adLockPessimistic   'Handle empty table to avoid error.   If rst.BOF Then     Exit Sub   End If   strBookmark = "OrderID = " & rst(0)   Debug.Print strBookmark   With Me.RecordsetClone     .FindFirst strBookmark     If Not .NoMatch Then       Me.Bookmark = .Bookmark     End If   End With   rst.Delete   rst.Close   Set rst = Nothing   Exit Sub errHandler:   MsgBox Err.Number & ": " & Err.Description, _    vbOKOnly, "Error" End SubSimilarly to the first example, Access remembers the marked record when you reopen the form. However, this time, Access can remember the marked record even if you close the database. You can use the same table to bookmark numerous forms. Simply add the appropriate fields and update the following statements accordingly:

cmdReturn_Click(): !OrderIDSaved = Me.OrderID.Value Form_Open(): strBookmark = "OrderID = " & rst(0)

Worth noting

If you want the form to mark the current record without user input, forget the command button and move the code to the form's Unload event. In addition, this technique is a one-time marking task. Once you reopen the form to the marked record, Access deletes the bookmark. You must remark the record if you want to bookmark it again. All of the listings contain minimal error handling. Be sure to test this code thoroughly in your own database and add the necessary error handling routines.


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks