Creating a tabbed form to filter records alphabetically

Creating a tabbed form to filter records alphabetically

If you wish to use a tab control on a form, with the tabs labelled A - Z, and only show records on the form for the selected letter, then you can do this:

Start by creating a Form, based on the Table or Query. Add a Tab Control to this Form, creating 26 pages (You will probably need to alter the Tab Fixed Width to have them all visible at the same time), and labelling these A - Z.
Add the required Fields to the Form, not onto the Tab Control (it is better in this case to only have one of each control, rather than 26 copies of it). When you have added the controls, and set them up correctly, select all of the controls, and drag them onto the Tab Control. This way, they will show through on each page of the Tab Control.

We now need to add some code to the Tab Control's OnChange event to filter the records as the selected page changes:

Private Sub TabCtl0_Change()
    Me.RecordSource = "SELECT * FROM Customers " _
        & " WHERE CompanyName LIKE '" & Chr(97 + Me!TabCtl0.Value) & "*' " _
        & " ORDER BY CompanyName ASC;"
End Sub

And also some code in the Form's onLoad event, so that we always start off with the letter 'A' displayed and filtered:

Private Sub Form_Load()
Me!TabCtl0 = 0
Call TabCtl0_Change
End Sub

You can download an Access 97 sample database showing how it all works.

Top

Last modified at 06/06/2006 14:54:30