filter name by letter

[kbremner]

I'm sure this is a fairly simple problem but I'm having quite some difficulty.

I originally had a continuous form with a list of client names and phone numbers. I used the alpha macro from the access sample database to filter the list by letter. I am changing that form, basically for aesthetic reasons - bringing its design in line with all of the other forms in the database. Originally I was going to have the actual list of names as a subform and use an unbound main form to contain all of the command buttons for user input. It seems, now that a better method would probably be to use one form and utilize a list box to display the names and numbers. I want to still use command buttons for each letter, but I'm at a loss for what I should use in the onclick event for each of those buttons. Any help?

[ssanfu]

There is an example on "Rogers Access Library" site at

http://www.rogersaccesslibrary.com/forum/topic337.html


Maybe this is what you are looking for or will give you an idea on how to proceed. If not, post back with more info on what you have tried and/or attach a sample mdb.

[kbremner]

It sort of works! Here is the modified code that I have:

Private Sub grpLastNameFilter_Click()
Select Case grpLastNameFilter
Case 1
Me.txtLastNameFilter = "A"
Me.lstEmployee.Requery
Case 2
......
End Select
Me.RecordSource = "Select * FROM tblContacts WHERE FileName Like """ & txtLastNameFilter & "*"" Order by FileName"
Me.Requery

Private Sub lstEmployee_Click()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Me![lstEmployee]
Me.Bookmark = rs.Bookmark
Me.lstEmployee = Me.ContactID
End Sub

So here is how it actually works on my form. When the form first opens, I can click on any record and it highlights. If I click on any of the buttons, the list does not change, but if I click on a record, it jumps to the first record that corresponds with the button letter. If I press a button for which there is no records corresponding to the letter and I click on a record, I get an error message that says Runtime Error 3021: No Current Record and it highlights Me.Bookmark = rs.Bookmark in the code.

[ssanfu]

rs.FindFirst "[ContactID] = " & Me![lstEmployee]

Regarding FindFirst (from VBA help):

Always check the value of the NoMatch property to determine whether the Find operation has succeeded. If the search succeeds, NoMatch is False. If it fails, NoMatch is True and the current record isn't defined. In this case, you must position the current record pointer back to a valid record.

This should help with the "No Current Record" error.


As far as the rest of the problems with the form, any chance you could attach a copy (no sensitive info) of your database?