How to make navigation buttons move the record pointer by alphabetic order

Posted on 2011-10-19
Last Modified: 2013-11-27
Great Day, I have a form where I created navigation buttons - "Delete", "First", "Next", etc. The form has text boxes for customer data and a list box that can go directly to a customer's record; consequently, when the customer's name on the list box is clicked, the data in the text boxes changes to that customer's data. The issue that I would like to correct is that when I click one of the navigation buttons; let's say I click "Next", the record pointer moves to the next record by alphabetic on FirstName. Consequently, if I am on Jimmy Baldwin's record, if I click the "Next" button, the record pointer will go to Karen Russell's record. I would like to be able to click the "Next" button and go to Mary Brockingham's record, who is alphabetically after Baldwin by LastName. Here is the code for the "Next" button:

    DoCmd.GoToRecord , , acNext
    Dim recClone As DAO.Recordset
    Set recClone = Me.Recordset.Clone

I hope I am not making the incorrect assumption that I will be able to correct the other buttons once I am given the solution to the "Next" button :-).

Thanks in advance for your assistance.

Question by:yddadsjd95
    LVL 119

    Accepted Solution

    you have to change the order by of the form's record source to sort on last names
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Another way to do this would be something like:

    Private Sub cmd_Next_Click

        Dim strCriteria as string

        strCriteria = "[LastName] => " & chr$(34) & me.txt_LastName & chr$(34)

        With me.recordsetclone
            .findfirst strCriteria
            if .nomatch then
                msgbox "You are on the last alphabetic recordme.bookmark = .bookmark
        end with

    End Sub

     If you have more than one person with the same last name, this will take you to the next last name.  So you will have to play with the criteria to get the next lastname, firstname combination.    

    Author Closing Comment

    Thank you capricorn1. I had it reversed: I had it sorting on FirstName and then Last, which didn't make sense after looking at it based on your solution. Once I placed LastName ahead of FirstName in the Record Source, it works perfectly.

    Thanks again, and have a great day today and an even finer one tomorrow.


    LVL 56

    Expert Comment

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    Change the order by or apply a filter to the form with an order by clause.

    Attached is a screen shot of my "control Panel" at the top of each form.  On the far left is the Lookup type follwed by the lookup:

     control panel lookup type
     and here's what the lookup looks like now:

     lookup box
     But if you switch the lookup type, it changes to:

     lookup box changed

      and the navigation buttons follow the lookup.

      Here's the code for when the lookup changes:

    2980      Case "AULookupControl"

    2990        DoCmd.Echo False, "Changing lookup mode..."
    3000        frm![ctlLookup].ColumnCount = frm!ctlLookupControl.Column(4)
    3010        frm![ctlLookup].BoundColumn = frm!ctlLookupControl.Column(5)
    3020        frm![ctlLookup].ColumnWidths = frm!ctlLookupControl.Column(6)
    3030        frm![ctlLookup].ListWidth = frm!ctlLookupControl.Column(7) * 1440
    3040        frm![ctlLookup].RowSource = frm!ctlLookupControl.Column(3)  'Use current filter for lookup box
    3050        frm.FilterOn = False
    3060        frm.OrderByOn = False
    3070        frm.OrderBy = ""
    3080        DoCmd.ApplyFilter frm!ctlLookupControl.Column(3)          'Apply current filter causes on current
                'frm![ctlLookup] = frm![ctlLookup].ItemData(1)             'Set lookup box to first item

    3090        intRet = GotoControl("ctlLookup")
    3100        DoCmd.Echo True
    3110        StdFormCt = True

     and after you select something in the lookup:

    2740      Case "AULookup"
    2750        DoCmd.Echo False, "Performing record lookup..."
                'After Update from Lookup Box. No need to trap errors - this won't be called
                '  if the box is not there.
    2760        If Nz(frm.ctlLookup, "") <> "" Then   'If the box is not empty...
                  'Make sure were on page 1 if it's a multi page form.
    2770          If (FControlExists((frm.FormName), "grpPgButtons")) Then
    2780            frm!grpPgButtons = 1
    2790            DoCmd.GoToPage 1
    2800          End If

    2810          Set rstFormClone = frm.RecordsetClone
    2820          strFind = strParam2 & " = " & frm.ctlLookup
    2830          rstFormClone.FindFirst strFind
    2840          If rstFormClone.NoMatch Then
    2850            Beep
    2860          Else
    2870            frm.Bookmark = rstFormClone.Bookmark
    2880            If (FControlExists((frm.FormName), "ctlEditBtn")) Then
    2890              frm.ctlEditBtn.SetFocus
    2900            End If
    2910            frm.ctlLookup = ""
    2920          End If
    2930          rstFormClone.Close
    2940          Set rstFormClone = Nothing

    2950        End If
    2960        DoCmd.Echo True
    2970        StdFormCt = True

      Note that you can't drop this code in because it relies on several things, but it shows you how to apply a filter, which then sets the order by property.  Reason for it done that way is that the code was originally written in the A2 days and the orderby property didn't exist back then.

      Important thing to walk away with here is to try and come up with a generic solution to lookup type and lookup control which is possible and what you should shoot for.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    758 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

    7 Experts available now in Live!

    Get 1:1 Help Now