Referencing A Control in Subform within a Tab Control

Question

I'm struggling with a form and a macro.  Allow me to provide some background information in case there's an altogether better way to do what I'm trying to do.

I have two primary forms:

1. frm_EmployeeMain
2. frm_EmployeeDetails

The database starts by opening "frm_EmployeeMain" which has two tab controls with subforms in each.

The first subform references "frm_EmployeeMain_Subform".  This tab shows a list of all current and former employees.  If you click on an employee ID number, it will execute a macro which opens the "frm_EmployeeDetails" and automatically move to the appropriate record based on which employee ID number you click on.  This form allows detailed information to be viewed, added, or edited.

The second tab on the main form, "frm_EmployeeMain", has another subform which is "frm_PayrollMain_SubForm".  This is another subform that lists all the employees, except that this tab shows different fields that are centered around what payroll needs to see.  It uses a similar macro like the first subform, "frm_EmployeeMain_Subform", in that you can click on the employee ID field for any employee in the list and it will open the "frm_EmployeeDetails" form and automatically navigate to the employee record for whichever ID you clicked on.

The problem:  When you close the "frm_EmployeeDetails" form and drop back to the "frm_EmployeeMain" form, a refresh is performed to ensure that any changes that were made are reflected immediately in the list-view primary form.  As a result, the selected employee ID is moved back up to the top of the list.  This only happens in whichever tab was open, so it could be "frm_EmployeeMain_Subform" or "frm_PayrollMain_SubForm".

What I'm trying to do is use a macro to automatically select (highlight) the last record that was open in "frm_EmployeeDetails" so that my end users don't have to constantly scroll back down through the form if they are going through employees and making changes or verifying information.

I've created a macro on the "frm_EmployeeDetails" form that stores the employee ID number of whatever the active record is when the form is closed.  I'm using the "SetTempVar" option.  The variable name is "EmpID".  This part is working as it should.

Then, back on the "frm_EmployeeMain" form I've created a macro on the "txt_EmployeeID" field which is the field that is automatically highlighted in whichever tab control was opened previously.  For the sake of this question, let's say we're on the "frm_PayrollMain_SubForm" control.  The control in this subform is "txt_EmployeeID" and that field is always automatically selected at the top of the list when the form refresh takes place.

Here's what my macro contains (this is placed in the "On Enter" event for the control I mentioned above):

If IsNull([TempVars]![EmpID]) Then

     StopMacro

End If

GoToRecord

     Object Type:  Form

     Object Name:  [frm_PayrollMain_Subform].Form

          Record:  Go To

          Offset:  =[TempVars]![EmpID]

RemoveAllTempVars

I have used the standard "MessageBox" macro command to verify at all stages that I am getting the correct information through each step of the process.  What I believe is that my syntax is wrong when I'm trying to specify the "GoToRecord" object name.

I'm hoping a fresh set of eyes on this will maybe see what I'm missing.  If there's a better way to do this, I'm open to ideas in that arena as well.

Thanks in advance for any help or advice anyone can offer.

- Dave -

Answer 1

Hi Dave,

Here we mainly focus on Office client related question and feedback. Since your question is macro related, I'm moving it to the dedicated forum below, there you should get more professional responses:

https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev

Regards,

Ethan Hua

---

Please remember to mark the replies as answers if they helped.
If you have feedback for TechNet Subscriber Support, contact [e-mail].

Answer 2

It sounds like you are requerying the form, not refreshing it.  The Requery method reloads the form's recordset, so the record pointer is positioned at the first row in the sort order.  The Refresh method, on the other hand, does not reload the recordset but updates it to reflect any changes to existing rows in the recordset, and does not move the record pointer.  Calling the Requery method is only necessary therefore if one or more rows have been deleted from or inserted.  If existing data only has been changed, the Refresh method should be used.

If it is necessary to call the Requery method and to stay on the current record the following steps are necessary:

1.  Grab the value of the primary key, or any candidate key, to a variable.
2.  Requery the form.
3.  Navigate back to the record by means of the value of the variable.

The following is an example of code which does this to position a new or amended row in its correct position in a form's sort order and move back to the row in question:

Private Sub Form_AfterUpdate()

    Dim lngID As Long
    
    lngID = Me.TransactionID
    Me.Requery
        
    With Me.RecordsetClone
        .FindFirst "TransactionID = " & lngID
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
    
End Sub

---

Ken Sheridan, Stafford, England