"Alphabetic" go to record

[psuplat]

Hi everyone

I have a simple form conencted to table that displays the suppliers. The records are being sorted alphabeticaly by the company's name.

What I would like to achieve is create an A to Z "letter bar" on top of the form. When user click i.e. "S" it takes him to first record where company name is begining with "S", if he clicks "C" it take him to first od C's, and so on.

Is there a way to achieve this?

Thanks for any kind of help.

 

[waterstar]

Hi
Create a main and sub form - the sub form looking at the table. On the main form create a groupbox with buttons for each letter of the alphabet (including an All button). Set Option Value for button A to 1, B to 2 etc, Set All button to 0. On the group box create an AfterUpdate event with code similar to:

Me![reflist].SetFocus ' move focus to reference list
Select Case Me![selalpha]
Case 0 ' all entries
Me![reflist].Form.Filter = "[Staff Surname] like '*'"
Case 27
Me![reflist].Form.Filter = "[Staff Surname] < 'A'"
Case Else ' selected entries
Me![reflist].Form.Filter = "[Staff Surname] like '" & Chr$(Me![selalpha] + 64) & "*'"
End Select
Me![reflist].Requery ' requery the sub form
Me.FilterOn = True ' enable filtering
Me.OrderByOn = True ' enable sort

Hope this helps

 

[JANR]

Or you can try Lebans simple label solution: http://www.lebans.com/selectalpha.htm

JR

 

[vbaInet]

I think the OP is talking about going to the record and not filtering. Here's what I would do.

For your labels, use a naming convention like lblA, lblB, lblC...

The function to use is:

Public Sub GoToAlpha(strAlpha As String)
    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone
    
    rs.FindFirst "Left([ActivityName], 1) = '" & Right(strAlpha, 1) & "'"
    
    If Not rs.NoMatch Then
        Me.Bookmark = rs.Bookmark
    End If
End Sub

Call the function using GoToAlpha lblA.Name or as you can see already, you can simply pass the right character in the parameter using GoToAlpha "A". If you're going to use the latter then take off the Right() function.