GOTO current record based on variable name of field

Posted on 2012-09-18
Last Modified: 2012-09-21
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


    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....


Question by:Karen Schaefer
    LVL 29

    Accepted Solution

    Try this in place of line 35:

    Open in new window


    Author Closing Comment

    by:Karen Schaefer
    thanks that did the trick

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now