GOTO current record based on variable name of field

I have a form that contains an option group, When the user selects an option I would like the code to navigate to that fields on the datasheet(subform), especially if the field is currently off the screen - to the right.

Here is my current code - what is the correct declaration or code to navigate to the correct field.

Private Sub optSortChange_AfterUpdate()

Dim strWHERE As String
Dim nVar  As Variant
   On Error GoTo optSortChange_AfterUpdate_Error

    If Me.sub5.Visible = False Then Me.sub5.Visible = True
    Me.sub5.Form.Filter = ""
    
    Select Case optSortChange
        Case 1
            strWHERE = ""
        Case 2
            strWHERE = "Remove_Active = 'X'"
        Case 3
             strWHERE = "OrgChanged = 'X'"
             nVar = "Current_Org"
       Case 4
              strWHERE = "Bldg_Chng = 'X'"
              nVar = "Current_Bldg"
       Case 5
            strWHERE = "Location_Chg = 'X'"
            nVar = "Current_loc"
       Case 6
            strWHERE = "MS_Chg = 'X'"
            nVar = "Current_MS"
        Case 7
            strWHERE = "Active = 0"
        Case 8
            strWHERE = "Active = -1"
 End Select

    Me.sub5.Form.Filter = strWHERE
    Me.sub5.Form.FilterOn = (Len(strWHERE) > 0)
    DoCmd.GoToControl (nVar)
   On Error GoTo 0
   Exit Sub

optSortChange_AfterUpdate_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure optSortChange_AfterUpdate of VBA Document Form_frmEmpMain"

End Sub

Open in new window


My current code returns the following error msg: "There is not field named "Current_Loc" in the current record....

Thanks,

karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
IrogSintaConnect With a Mentor Commented:
Try this in place of line 35:
Me.sub5.SetFocus
Me.sub5.Form(nVar).SetFocus

Open in new window

0
 
Karen SchaeferBI ANALYSTAuthor Commented:
thanks that did the trick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.