Search based on alphabet

[FranS37]

I've seen examples of this, but now that I want it I can't find it.

A form with each letter of the alphabet represented on a button. When I click on the "A" button, for example, I retrieve all people whose last name begins with the letter "A".

Can anyone point me to an example?

Thanks.

 

[hymn]

I have an old contact db that has that in

Hope this helps
Hymn

 

[PHV]

Something like this ?
Private Sub CommandeA_Click()
Me.Filter = "[last name] Like 'A*'"
Me.FilterOn = True
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Griff389]

I just implemented the above code into 26 buttons, which works fine, apart from when I press a button that has no surname beginning with that letter.

It filters on the letter button pressed (i.e. for Z) and then it shows no records as you'd expect. Then when you try to filter on something else (press another button), access claims I can't have a record with no primary key. As far as I was aware, I hadn't created a new record.

Is there a way, after pressing a letter button, to check for the existence of a record, and if there aren't any, to remove the filter. I've done a search but not found anything.

Regards

Griff

 

[hymn]

Just convert a macro and below is the code

'------------------------------------------------------------
' Customer_phone_List_Alpha_Buttons
'
'------------------------------------------------------------
Function Customer_phone_List_Alpha_Buttons()
On Error GoTo Customer_phone_List_Alpha_Buttons_Err

    With CodeContextObject
        ' Attached to the Customer Phone List form.
        ' Attached to AfterUpdate event ofSurnameFilter option group.
        If (.SurNameFilters = 1) Then
            ' Filter for Surnames that start with A
            DoCmd.ApplyFilter "", "[Surname] Like ""[A]*"""
        End If
        If (.SurNameFilters = 2) Then
            ' B
            DoCmd.ApplyFilter "", "[Surname] Like ""B*"""
        End If
        If (.SurNameFilters = 3) Then
            ' C
            DoCmd.ApplyFilter "", "[Surname] Like ""C*"""
        End If
        If (.SurNameFilters = 4) Then
            ' D
            DoCmd.ApplyFilter "", "[Surname] Like ""D*"""
        End If
        If (.SurNameFilters = 5) Then
            ' E
            DoCmd.ApplyFilter "", "[Surname] Like ""E*"""
        End If
        If (.SurNameFilters = 6) Then
            ' F
            DoCmd.ApplyFilter "", "[Surname] Like ""F*"""
        End If
        If (.SurNameFilters = 7) Then
            ' G
            DoCmd.ApplyFilter "", "[Surname] Like ""G*"""
        End If
        If (.SurNameFilters = 8) Then
            ' H
            DoCmd.ApplyFilter "", "[Surname] Like ""H*"""
        End If
        If (.SurNameFilters = 9) Then
            ' I
            DoCmd.ApplyFilter "", "[Surname] Like ""I*"""
        End If
        If (.SurNameFilters = 10) Then
            ' J
            DoCmd.ApplyFilter "", "[Surname] Like ""J*"""
        End If
        If (.SurNameFilters = 11) Then
            ' K
            DoCmd.ApplyFilter "", "[Surname] Like ""K*"""
        End If
        If (.SurNameFilters = 12) Then
            ' L
            DoCmd.ApplyFilter "", "[Surname] Like ""L*"""
        End If
        If (.SurNameFilters = 13) Then
            ' M
            DoCmd.ApplyFilter "", "[Surname] Like ""M*"""
        End If
        If (.SurNameFilters = 14) Then
            ' N
            DoCmd.ApplyFilter "", "[Surname] Like ""N*"""
        End If
        If (.SurNameFilters = 15) Then
            ' O
            DoCmd.ApplyFilter "", "[Surname] Like ""O*"""
        End If
        If (.SurNameFilters = 16) Then
            ' P
            DoCmd.ApplyFilter "", "[Surname] Like ""P*"""
        End If
        If (.SurNameFilters = 17) Then
            ' Q
            DoCmd.ApplyFilter "", "[Surname] Like ""Q*"""
        End If
        If (.SurNameFilters = 18) Then
            ' R
            DoCmd.ApplyFilter "", "[Surname] Like ""R*"""
        End If
        If (.SurNameFilters = 19) Then
            ' S
            DoCmd.ApplyFilter "", "[Surname] Like ""S*"""
        End If
        If (.SurNameFilters = 20) Then
            ' T
            DoCmd.ApplyFilter "", "[Surname] Like ""T*"""
        End If
        If (.SurNameFilters = 21) Then
            ' U
            DoCmd.ApplyFilter "", "[Surname] Like ""U*"""
        End If
        If (.SurNameFilters = 22) Then
            ' V
            DoCmd.ApplyFilter "", "[Surname] Like ""V*"""
        End If
        If (.SurNameFilters = 23) Then
            ' W
            DoCmd.ApplyFilter "", "[Surname] Like ""W*"""
        End If
        If (.SurNameFilters = 24) Then
            ' X
            DoCmd.ApplyFilter "", "[Surname] Like ""X*"""
        End If
        If (.SurNameFilters = 25) Then
            ' Y
            DoCmd.ApplyFilter "", "[Surname] Like ""Y*"""
        End If
        If (.SurNameFilters = 26) Then
            ' Z
            DoCmd.ApplyFilter "", "[Surname] Like ""Z*"""
        End If
        If (.SurNameFilters = 27) Then
            ' Show all records.
            DoCmd.ShowAllRecords
        End If
        If (.RecordsetClone.RecordCount > 0) Then
            ' If records are returned for the selected letter, go to the CompanyName control.
            DoCmd.GoToControl "Surname"
            ' Stop the macro.
            Exit Function
        End If
        If (.RecordsetClone.RecordCount = 0) Then
            ' If no records are returned for the selected letter, display a message.
            Beep
            MsgBox "There are no records for that letter.", vbInformation, "No Records Returned"
            ' Show all records.
            DoCmd.ShowAllRecords
            ' Press in the All button.
            .SurNameFilters = 27
        End If
    End With


Customer_phone_List_Alpha_Buttons_Exit:
    Exit Function

Customer_phone_List_Alpha_Buttons_Err:
    MsgBox Error$
    Resume Customer_phone_List_Alpha_Buttons_Exit

End Function

Hope this helps
Hymn

 

[randy700]

Maybe this?

Private Sub CommandeA_Click()
If IsNull(DLookup("[last name]","[table name]", "Left([last name],1) = 'A'")) Then
MsgBox "No records found."
Else
Me.Filter = "[last name] Like 'A*'"
Me.FilterOn = True
End Sub


Randy