Unanswered: Go back through controls on a form in the order they were given the focus?
I am using Access 2000 and have a query regarding the possibility of moving back through a series of controls on a form in the order they were given the focus and was wondering if any one could help?
I have a form with 5 text boxes, 4 combo boxes and 2 command buttons (save and cancel). The user is able to navigate through the controls freely before saving the record using the command button.
For data tracking purposes I am looking to obtain the name of the control (either the text box or combo box & not command button) that last had the focus when a change was cancelled. When the user cancels changes to an existing record there is code (below) that cancels the change, checks to see if the user is modifying an existing record (for data tracking purposes) and if they are moves the focus to the previous control and attempts to get the name of the control (either the text box or combo box & not command button).
Dim ctrlType as Integer
Dim ctrlName is String
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
If Not Me.NewRecord Then
If ctrlType = 109 Or ctrlType = 111 Then ' 109 textbox and 111 combobox
ctrlName = Screen.ActiveControl.Name
ctrlType = Screen.PreviousControl.ControlType
Loop Until ctrlType = 109 Or ctrlType = 111
The code seems to work providing the user selects the cancel button immediately after being in the control that they want to cancel the changes for instead of saving it first. However, say the user saves a change to an existing record then decides they want to cancel the change. The code that would run cancels the change, sets the focus to the previous control (the save button), this isn't a text or combo box so the code then sets the previous control again (the cancel button), the same happens again with the code setting the previous control back and forth between the save and cancel buttons.
Ideally I would love a way of moving back through each control in the order that it got the focus originally. Say the controls were accessed in the order txt1, txt4, txt2 and txt3, the user saved a change, then realised they wanted to cancel. The code would go back through each control starting with the cancel button, save button then txt3, here it would know that txt3 was a text box so get the control name.
I hope this makes sense and I would really appreciate any support,
Here's a solution to store a reference to every determined control that received the focus into a Collection object.
1. In the Properties window and for each control that must be "recorded" in the collection, set the "OnGotFocus" property to (see attached picture):
2. Add this code in the class module of the form:
Option Compare Database
Private m_colControls As Collection
Private Function HadFocus()
If m_colControls Is Nothing Then Set m_colControls = New Collection
m_colControls.Add Me.ActiveControl, CStr(m_colControls.Count)
Private Sub Form_Current()
Set m_colControls = New Collection
Private Sub Command_EnumControlSequence_Click()
Dim i As Long
For i = 1 To m_colControls.Count
Debug.Print i, m_colControls(i).Name
I use a command button ("Command_EnumControlSequence") to demonstrate how you can enumerate in sequence the names of every control that received the focus and is then stored in the collection "m_colControls". Notice that a reference to the control is stored in the collection, so you can address any property of this control through the collection, not only its name.
When the "Form_Current" event occurs (i.e. when the current record changes) the collection is reset.