Address list - filter by alphabet tabs

  • Thread starter Thread starter Allan
  • Start date Start date
A

Allan

I'm relatively new to Access so I apologise if this is a silly question
.......... but .......

I have an address table and I want to be able to filter them in a multi tab
form. i.e. click on tab "A" and see details of all records with surname
beginning with A etc.

I have a solution ... by defining 26 queries (qryA - qryZ)and subforms
(sfrmA - sfrmZ) but I can't help feeling this is a very inefficient method.
I'm sure there must be a more elegant way of doing this.
Thanks ... Allan
 
The easy way:

Assuming you have an option group called Frame0 on your form. Your option
group will have a different option for each letter (A, B, C, etc). The value
of each letter is equal the the Ascii value (A=65, B=66, c=67, etc).Also,
add one more for ALL and set the value to 0, and one for Numbers and set the
value to 1

Also on your form you have a list box. List1.

Put this on your Option Group's on click
Basically what it does, it takes the current value (which ever option you
clicked on) and converts this value with chr into a its corresponding
character (if you click on B, the value would be 66 and this gets changed to
B by Chr). Then the query is made with the filter set on Left(Field1,1) = B
(the first letter of the field you want to filter is B, etc).

All is left to do is add one for the All values, and for numeric values

Private Sub Frame0_Click()
Dim strSql As String
' if all was clicked
if me.frame0.value = 0 then
strSql = "SELECT tmp.* FROM tmp ; "
Me.List1.RowSource = strSql
Me.List1.Requery
exit sub
end if
' if number were clicked
if me.frame0.value = 1 then
strSql = "SELECT tmp.* FROM tmp WHERE IsNumeric(left(Field1,1 )) = True
; "
Me.List1.RowSource = strSql
Me.List1.Requery
exit sub
end if
' anything else was clicked
strSql = "SELECT tmp.* FROM tmp WHERE left(Field1,1 ) = '" &
Chr(Me.Frame0.Value) & "' ; "
Me.List1.RowSource = strSql
Me.List1.Requery
End Sub

Rodrigo.
 
Hi Allan,

In addition to the other suggestion, here are a few other sample files you
can look at for study on this subject:

1. Take a look at the "Customer Phone List" form in the
sample Northwind database that ships with Access. It does
what you want with an option group and associated macro.
You might be able to use that form as a guide to learn
from. I've used this before.

2. You might like to try Sensei Stephen Lebans' SelectAlpha:

http://www.lebans.com/selectalpha.htm

3. There is a similar solution that allows for any font on a command button
in the Corp Tech Demos written by MVP Duane Hookom. The form in question is
the "Data Menu Demo" in the download file:

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

Hope that gives you some ideas.
Jeff Conrad
Bend, Oregon
 
In addition to the other suggestions, just use the right-click menu. Put
the cursor in the field you want to filter on, right-click One of the items
is 'Filter'. Just manually enter A* in the dialog box to filter on all
items beginning with A.


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Thanks everyone, I will investigate all your suggestions.
There's certainly a lot to learn.
Allan
 
Back
Top