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

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.

Who is Participating?
Rey Obrero (Capricorn1)Commented:
you have to change the order by of the form's record source to sort on last names
Dale FyeCommented:
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.    
yddadsjd95Author Commented:
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.


Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

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.