Letters of the alphabet buttons to filter form

S

Shelley

Does anyone remember seeing a sample form with a button
for each letter of the alphabet that would filter the
form, i.e. if you click on 'A', you would only see
customers whose name starts with 'A'? I have been
searching for it to copy and paste the code (rather than
having to re-write it myself) but can't find it.

Thanks in advance,
Shelley
 
A

Al Camp

Shelley,
Might I suggest another solution, that's very easy to build, and takes up
very little room on the form.
Create an unbound Combobox with RowSourceType = Value List, and enter
these values for RowSource...
A; B; C; D; .... etc.... ;Z
On the AfterUpdate of the Combobox use it's value for filtering your
records.
 
L

Larry Linson

Does anyone remember seeing a sample
form with a button for each letter of the
alphabet that would filter the form, i.e. if
you click on 'A', you would only see
customers whose name starts with 'A'? I
have been searching for it to copy and
paste the code (rather than having to
re-write it myself) but can't find it.
Click to expand...

Do you _want_ to see all the "A" customers, or are you using this as a
method to "drill down" to a particular one? If the latter, one of the
options in the Combo Box Wizard is to use it to locate a record for your
form. You might take a look at that -- it not only scrolls down its list to
the first match, but continues to do so on the second, third, etc. letters
you type.

The only form I remember seeing, BTW, that had a command button for every
letter was a very old (Access 1.0 or 1.1) sample for an address book.

Larry Linson
Microsoft Access MVP
 
M

Marshall Barton

Shelley said:
Does anyone remember seeing a sample form with a button
for each letter of the alphabet that would filter the
form, i.e. if you click on 'A', you would only see
customers whose name starts with 'A'? I have been
searching for it to copy and paste the code (rather than
having to re-write it myself) but can't find it.
Click to expand...

The code at http://www.lebans.com/selectalpha.htm doesn't
bother with that many buttons, but it just might do what you
want.
 
J

Jeff Conrad

Hi Shelley,

You probably saw it in the Northwind sample database that
ships with Access (at least 97 and 2000). Look for a form
called "Customer Phone List." It uses an option group on
the form and a saved macro. I've used it successfully
several times.

Hope that helps,
Jeff Conrad
Bend, Oregon
 
R

Ronald W. Roberts

Shelley said:
Does anyone remember seeing a sample form with a button
for each letter of the alphabet that would filter the
form, i.e. if you click on 'A', you would only see
customers whose name starts with 'A'? I have been
searching for it to copy and paste the code (rather than
having to re-write it myself) but can't find it.

Thanks in advance,
Shelley
Click to expand...

The control is an option group with 27 buttons.
A thru Z and All.
The option values are 1 thru 27.

Private Sub FilterButtons_Click()
Select Case Forms![Contract Master]![FilterButtons]
Case 1
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"A*" & "'"
Case 2
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"B*" & "'"
Case 3
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"C*" & "'"
Case 4
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"D*" & "'"
Case 5
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"E*" & "'"
Case 6
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"F*" & "'"
Case 7
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"G*" & "'"
Case 8
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"H*" & "'"
Case 9
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"I*" & "'"
Case 10
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"J*" & "'"
Case 11
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"K*" & "'"
Case 12
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"L*" & "'"
Case 13
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"M*" & "'"
Case 14
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"N*" & "'"
Case 15
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"O*" & "'"
Case 16
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"P*" & "'"
Case 17
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"Q*" & "'"
Case 18
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"R*" & "'"
Case 19
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"S*" & "'"
Case 20
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"T*" & "'"
Case 21
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"U*" & "'"
Case 22
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"V*" & "'"
Case 23
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"W*" & "'"
Case 24
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"X*" & "'"
Case 25
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"Y*" & "'"
Case 26
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"Z*" & "'"
Case 27
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"*" & "'"
End Select
Forms![Contract Master].Requery
Forms![Contract Master].Refresh

HTH
Ron
 
M

Max Smart

Here is a short version if you want it. It converts the option value to
a character and can be used in combination with other filters to build
the WHERE clause.

Select Case fraFilterLetter.Value
Case 0 'Show All is selected.
strWhere = ""
Case 1 To 26 'Show only last names that begin with the selected
letter.
strWhere = "UCASE(LEFT(Contacts.[Last Name], 1)) = '" & Chr$(64 +
Val(fraFilterLetter.Value)) & "'"
End Select

Max


Ronald W. Roberts said:
Shelley said:
Does anyone remember seeing a sample form with a button
for each letter of the alphabet that would filter the
form, i.e. if you click on 'A', you would only see
customers whose name starts with 'A'? I have been
searching for it to copy and paste the code (rather than
having to re-write it myself) but can't find it.

Thanks in advance,
Shelley
Click to expand...

The control is an option group with 27 buttons.
A thru Z and All.
The option values are 1 thru 27.

Private Sub FilterButtons_Click()
Select Case Forms![Contract Master]![FilterButtons]
Case 1
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"A*" & "'"
Case 2
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"B*" & "'"
Case 3
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"C*" & "'"
Case 4
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"D*" & "'"
Case 5
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"E*" & "'"
Case 6
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"F*" & "'"
Case 7
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"G*" & "'"
Case 8
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"H*" & "'"
Case 9
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"I*" & "'"
Case 10
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"J*" & "'"
Case 11
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"K*" & "'"
Case 12
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"L*" & "'"
Case 13
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"M*" & "'"
Case 14
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"N*" & "'"
Case 15
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"O*" & "'"
Case 16
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"P*" & "'"
Case 17
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"Q*" & "'"
Case 18
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"R*" & "'"
Case 19
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"S*" & "'"
Case 20
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"T*" & "'"
Case 21
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"U*" & "'"
Case 22
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"V*" & "'"
Case 23
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"W*" & "'"
Case 24
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"X*" & "'"
Case 25
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"Y*" & "'"
Case 26
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"Z*" & "'"
Case 27
Forms![Contract Master].RecordSource = "Select *
From [Employer Contract Query] Where [EMID] Like " & "'" &
"*" & "'"
End Select
Forms![Contract Master].Requery
Forms![Contract Master].Refresh

HTH
Ron
--
Ronald W. Roberts
Roberts Communication
(e-mail address removed)
To reply remove "_at_robcom_dot_com"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to disable Small alphabet letters on the form 6
Alphabetical Buttons 3
Search form via filter 5
filter wild card 2
Filter button 3
Rolodex 2
Filter form based on value not in subform 4
How to hide the "Filtered button" 1

Top