Results 1 to 8 of 8
  1. #1
    SandB is offline Novice
    Windows 7 64bit Office 365
    Join Date
    May 2020
    Posts
    2

    Navigating quickly through a large alphabetized customer list

    I created a basic Form for a customer list of addresses, phone #s, and related attributes, (name field is alphabetized by last name) that is about 3,600 records long. To access a record I can scroll down the right side, but I would like to be able to get directly to a record line (or close to it) by typing in the first few letters of the customer's last name - similar to getting to a folder in Windows from a long list by typing in a few letters. I am pretty new to Access, but I assume there must be a fairly simple macro or something for that but I can't find it in any of my books. Thanks for any help.  Steve

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Wouldn't say it's simple but is doable.

    Review http://allenbrowne.com/AppFindAsUType.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Look at this link on Allen Browne's website http://www.allenbrowne.com/ser-03.html

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Also, if you simply turn on the Navigation buttons on the form (you'll find them on the bottom left of the form) and start typing the name in that textbox it will start searching and move to that record as you type. Btw make sure you have an index on the name field.

  5. #5
    SandB is offline Novice
    Windows 7 64bit Office 365
    Join Date
    May 2020
    Posts
    2
    Thanks very much - now to try those ideas out!

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Or maybe add an unbound combo to the forms header and set it to "Find a record...." (it is an option in the built in wizard when adding the combo to the form in design view).

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Mickjav is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    123
    I use this for some of my larger lists My brothers company has the most with over 1100 customers a few years back ,The image is an older version

  8. #8
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Hey SandB

    If you are interested, there is a course on Udemy that walks you through exactly how to do what you are looking for.
    the course is called "Microsoft Access VBA for non programmers - You can do this"

    just click in the field you want and start typing the name and the form will automatically start sorting the records as you type

    Code:
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    
    Dim ctl As Control
    Dim fldName As String
    Dim rst As Recordset
    
    
    On Error GoTo ErrHandler
    'MsgBox ("the keycode is " & KeyCode)
    Select Case KeyCode
    
    
       Case 122
          If Shift <> 4 Then
             KeyCode = 0
          End If
    
    
       Case vbKeyEnd
          KeyCode = 0
          DoCmd.RunCommand acCmdRecordsGoToLast
       Case vbKeyHome
          KeyCode = 0
          DoCmd.RunCommand acCmdRecordsGoToFirst
       Case vbKeyUp
          KeyCode = 0
          DoCmd.RunCommand acCmdRecordsGoToPrevious
       Case vbKeyDown
          KeyCode = 0
          DoCmd.RunCommand acCmdRecordsGoToNext
       Case vbKeyRight, vbKeyLeft
    
    
       Case 9, 13  'Tab or enter key
    
    
       Case 8 'Backspace Key
    
    
    
    
    
    
       Case 48 To 57, 65 To 90
          Set ctl = Screen.ActiveControl
          fldName = ctl.Name
          If fldName <> LastFld Then
             srchval = ""
          End If
          LastFld = fldName
          Select Case UCase(fldName)
             Case "CityFilt"  ' City Filt Fix
                Exit Sub
          End Select
          srchval = srchval & Chr(KeyCode)
          KeyCode = 0
          If fldName = "Address" Then
             srchCrit = "[" & fldName & "] Like '*" & srchval & "*'"
          Else
             srchCrit = "[" & fldName & "] Like '" & srchval & "*'"
          End If
          Set rst = Me.RecordsetClone
          rst.FindFirst srchCrit
          If rst.NoMatch Then
             MsgBox (" Record not found! ")
          Else
             Me.Bookmark = rst.Bookmark
          End If
          rst.Close
    
    
       Case 107, 187
          If srchval = "" Then
             KeyCode = 0
             Exit Sub
          End If
          Set ctl = Screen.ActiveControl
          fldName = ctl.Name
          KeyCode = 0
          Set rst = Me.RecordsetClone
          rst.Bookmark = Me.Bookmark
          rst.FindNext srchCrit
          If rst.NoMatch Then
             MsgBox (" Record not found! ")
          Else
             Me.Bookmark = rst.Bookmark
          End If
          rst.Close
    
    
       Case 109, 189
          If srchval = "" Then
             KeyCode = 0
             Exit Sub
          End If
          Set ctl = Screen.ActiveControl
          fldName = ctl.Name
          KeyCode = 0
          Set rst = Me.RecordsetClone
          rst.Bookmark = Me.Bookmark
          rst.FindPrevious srchCrit
          If rst.NoMatch Then
             MsgBox (" Record not found! ")
          Else
             Me.Bookmark = rst.Bookmark
          End If
          rst.Close
    
    
       Case 27
          KeyCode = 0
          srchval = ""
    
    
       Case Else
          KeyCode = 0
    End Select
    
    
    Exit Sub
    
    
    
    
    ErrHandler:
    Select Case Err.Number
    
    
       Case 2046
    
    
       Case Else
          MsgBox Err.Number & " " & Err.Description
    
    
    End Select
    Resume Next
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-14-2017, 09:57 AM
  2. Replies: 7
    Last Post: 08-06-2016, 07:05 PM
  3. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  4. Replies: 1
    Last Post: 02-24-2013, 09:30 PM
  5. Customer ID from drop down list
    By darrellx in forum Forms
    Replies: 1
    Last Post: 08-21-2011, 09:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums