In this month’s issue,  Peter tackles handling of quotes,  using the RecordsetClone on a form, violating Windows user interface standards, data entry forms, and how to find the First Monday and Last Friday of a month.

Here’s my problem: The screen display shows a list of records. At the top of the form, the users enter a primary key and I find the record for them. But they don’t want to see the record they asked for at the top of the list — they want to display the three or four records before the record they want and then see the record they asked for. Of course, the record they asked for should still be highlighted. Is there some way to scroll the list without using the dreaded SendKeys?
I want you to know that, until I got to your last sentence, I had a whole answer prepared around using SendKeys. But you’re right — mimicking keystrokes to accomplish anything on the screen is a risky business at best. Here’s a solution that uses recordsets instead.

Every form has a recordset of all the records that the form is displaying associated with it. Manipulating the form’s recordset’s current record controls which record is being displayed.

Like every other recordset, the form recordset has a bookmark property, which identifies the current record. One of the ways of making a record current is to use the Bookmark property: Setting the form’s recordset bookmark property to the bookmark for a record in the recordset causes that record to become the current record. If the form is displaying in Single Form view, then that record is displayed. If the form is displaying in Continuous Form view, and the record is visible, the record will be highlighted on the screen. If the record isn’t currently being displayed (still in Continuous Form view), the form will scroll to move that record to the top of the list.

The problem, then, becomes determining the bookmark of the record that you want to display (I’m assuming that you already have the code to find the record your users asked for).

The first thing to do is get a clone of the form’s recordset. A clone of a recordset contains the same records as the original (including the same bookmarks) but has its own current record. As a result, you can manipulate the clone without affecting your current position in the original Recordset. Because the bookmarks for the recordset and its clone are the same (not true of any other two recordsets) the bookmarks from the clone can be used on the original Recordset. The RecordsetClone property of a form returns a clone of the form’s recordset, which you can use to solve your problem.

This Access 95 code begins by retrieving the recordset clone and then uses it to find the specified record. Once the record is found, the record’s bookmark is saved to be used later. The code then backs up three records to retrieve the record to display at the top of the form. The next line sets the form’s bookmark to this record’s bookmark, forcing the redisplay. Finally, the code sets the form’s bookmark to the record the user asked for to highlight it on the screen:

Sub PositionRecords()

Dim rec As Recordset

Dim varBookMark as Variant



Set rec = Me.RecordsetClone

'code to find record

varBookmark = Me.Bookmark

If rec.AbsolutePosition > 2 Then

    rec.Move -3

Else

    rec.MoveFirst

End If

Me.Bookmark = rec.Bookmark

Me.BookMark = varBookMark

End Sub

   rec.Bookmark = Me.Bookmark

The code uses the AbsolutePosition property of the recordset to check that there are three records prior to the found record. If there aren’t, the code simply moves to the first record in the recordset.

One last warning: The recordset that you get from the RecordSetClone property has its cursor pointed at the first record in the recordset. Don’t expect your cloned recordset to have the record currently being displayed on the form as its current record. To synchronize your clone with the displayed record, use this code:

While I tested this code in Access 95, it should work in all versions of Access from 2.0 on up. The AbsolutePosition property is supported in Access 2.0, though it isn’t documented.

Is there any way to create a shortcut key to a function? I want my users to be able to use <ALT_S> to start some processing from a form, but I don’t want to have to put another button on my form (it’s a long story).

This is an interesting question, and I have two answers for you. I should point out, though, that while you can do this, it’s not necessarily a good idea. This doesn’t really fit in the Win95 standard user interface conventions (<ALT> key combinations are normally shortcuts to buttons or menus). Without some compelling reason, it’s not a good idea to set up your own interface standards. That said, here are my suggestions.

The first one is the solution that I liked, but it only works by putting a button on the form that doesn’t take up any screen real estate. I added a button to the form and gave it a caption of “&S.” Setting the caption to this value had two effects — the button displayed “S” on its face, and the button will now generate a click event whenever <ALT_S> is pressed. In order to prevent the button taking up any real estate, I made it very small and set its Transparent property to True — which makes the button invisible on the form. You can now put the code to be called by the <ALT_S> key in the button’s Click event. At the end of the button’s click event, make sure to add a line of code to move the focus from this button to some other control on the form (otherwise, pressing the <Enter> key after doing an <ALT_S> will press the hidden button again).

I liked this solution for two reasons. First, the <ALT> plus keystroke combinations are normally associated with shortcuts to Command buttons, so I haven’t broken too many rules. Second, the button was still visible on the form in design mode as an outline, making it a little clearer to whoever has to maintain this program that there’s functionality there and it’s reached through the button.

However, I also recognized that I might not have handled every situation that might cause this button to be activated without using the <ALT_S> combination. If the user were to click randomly on the screen and hit this invisible button, for instance, the code would execute. This led me to my second suggestion.

For this solution, I set the form’s KeyPreview property to True. This causes all KeyPress, KeyDown, and KeyUp events to be handled by the form before they’re handled by the individual controls. Because the KeyPreview property isn’t available in Access 2.0, you can’t use this solution in versions of Access earlier than Access 95.

With the form now handling all of the KeyPress events, I added code to check for the <ALT_S> combination:

Private Sub Form_KeyDown(KeyCode As Integer, _

   Shift As Integer)



If KeyCode = 83 And Shift = 4 Then

    ...do processing...

End If



End Sub

And Yet There is More

I’ll freely admit that I don’t have the KeyCodes and Shift numbers memorized. To determine the appropriate values, I put Debug.Print statements in this procedure to display the KeyCode and Shift passed into the routine. I then loaded the form, keyed an <ALT_S>, checked to see what numbers were generated, and wrote the If statement to check for them. Because other people probably won’t recognize those keycodes either, you should probably add a comment to the code indicating what it’s doing.

Whenever I have to add comments to explain what my code is doing (rather than why) I get nervous about whether this is a good thing to do. There’s an old saying: “Doctors bury their mistakes, architects cover them with ivy, and programmers comment them.” My misgivings aside, this solution has at least one other drawback — Access will generate an audible click when the <ALT_S> is pressed, indicating that no control has accepted the keystroke.