Letters of the alphabet buttons to filter form

  • Thread starter Thread starter Shelley
  • Start date Start date
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
 
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.
 
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
 
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.
 
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
 
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
 
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...
 
Back
Top