VBA: Set cursor to end of MS Access field

This came about because I have a form that periodically saves the form record using the OnTimer event. When a save is initiated, the Microsoft Access default behavior is to place the cursor at the front of the current field.

This default behavior would interrupt the user if they are typing, and it would require them to scroll down if the text is much larger than the scroll box. Either way, it is an annoyance.

 Access has an option to change the default behavior and these settings apply to all of the databases opened by that users' instance of Access.

•  File - Options - Client Settings - "Behavior Entering Field" (Access 2010)
•  Office Button - Access Options - Advanced - "Behavior Entering Field" (Access 2007) 

The options are:

• Select Entire Field 
• Go to Start of Field 
• Go to End of Field

Changing the defaults for everything may be a solution for you, but it is not for me. I simply want to change certain fields on certain forms so that when a user is typing and the OnTimer event is triggered, their cursor is not pushed back to the top of the field causing them to enter text in the wrong place.

There is one thing do to to duplicate the behavior for an individual field. Set the "SelStart" property to the end of the field. I do this in the OnEnter event so that clicking, tabbing, or other means of entering the field will automatically place the cursor at the end of the field value. Of course you have to do this for each field that needs it, but the benefit is that it allows you to pick and choose which fields it applies to.

Private Sub  txtFieldName_Enter()

'SelStart = end of the field. Add the ("" &) to ensure it works with a null field value.
me.txtFieldName.SelStart = Len("" & me.txtFieldName)

End Sub 

This solves the default behavior so that the need to scroll to end is removed. The second part of the fix is to ensure that when saving the form record, the cursor is still in current field and appears at the end.

This requires a global variable within the module to keep track of which field is current.

Dim strCurrField as String

And the Timer event:

Private Sub Form_Timer()

If Me.Dirty then

DoCmd.RunCommand acCmdSaveRecord 'save
Me.Controls(strCurrField).SetFocus ' cannot use the SelStart property without focus
Me.Controls(strCurrField).SelStart = Len("" & Me.Controls(strCurrField).Value)

End if

End Sub

So now when the form record is saved while a user is typing, the field blinks and the cursor continues at the end, which is the most likely place it belongs.

This does not help if the user is typing in the middle of a block of text, but in my instance, this is not a common scenario.