Alphabetical Index on Form

  • Thread starter Thread starter Jaycee
  • Start date Start date
J

Jaycee

Recently I saw a form in a sample database that had
buttons across the bottom, one for each letter of the
alphabet. I assume that clicking on "C" jumped to the
first record where the Last Name, for instance, began
with "C."

Can anyone tell me how to create these buttons? I made a
couple of attempts at figuring it out, but without
success.

If the process is complex, please be specific. I've had
no training in SQL/VB.

Thanks!
 
It was probably the Customer Phone List form in the Northwind sample
database.

These are toggle buttons in an option group.

The AfterUpdate event of the group calls the macro:
Customer Phone List.Alpha Buttons

The maco uses the ApplyFilter action to filter the form.

Hopefully that's enough for you to see how it works, and copy the strategy.
 
You have something on your form named "LastNameFilter", or you have that
word somewhere in your macro. What is it? Why are you referring to it?
 
In the NW example, the condition field is
[CompanyNameFilters]=1, etc. Since I want the macro to
filter on employees' last names, I entered
[LastNameFilter]=1, and so forth, as the condition.

In the Action Arguments section, I entered
[LastNameFilter] Like "A*" as the Where Condition.

I somehow thought that the macro would know to use the
LastName field as the source, but I see now that I need a
filter named "LastNameFilter." I searched Help and went
back to NW to find out how to create a named filter, but
with no success. I understand how to filter data, but
it's creating an object named LastNameFilter that I don't
get. Thanks for your quick reply and your assistance!
 
Correction: In the Action Arguments section, I entered
[LastName] Like "A*" as the Where Condition.
-----Original Message-----
In the NW example, the condition field is
[CompanyNameFilters]=1, etc. Since I want the macro to
filter on employees' last names, I entered
[LastNameFilter]=1, and so forth, as the condition.

In the Action Arguments section, I entered
[LastNameFilter] Like "A*" as the Where Condition.

I somehow thought that the macro would know to use the
LastName field as the source, but I see now that I need a
filter named "LastNameFilter." I searched Help and went
back to NW to find out how to create a named filter, but
with no success. I understand how to filter data, but
it's creating an object named LastNameFilter that I don't
get. Thanks for your quick reply and your assistance!



-----Original Message-----
You have something on your form named "LastNameFilter", or you have that
word somewhere in your macro. What is it? Why are you referring to it?


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Click to expand...
.
Click to expand...
 
In the NorthWind example, the option group is named
CompanyNameFilters
Perhaps your option group is named
LastNameFilter

When you look at the Customer Phone List macro in Northwind, do you see
columns named Macro Name and Condition? If not, go to the View menu and
click the Macro Name and Condition items. You can then see how the 27
conditions and more that define when each of the various ApplyFilter actions
are performed.

Select the first line, i.e. the one where Condition contains:
[CompanyNameFilters]=1
If your option group is named LastNameFilter, and you A toggle button has
the value 1, you will need:
[LastNameFilter]=1

In the lower pane, the WhereCondition reads:
[CompanyName] Like "[AÀÁÂÃÄ]*"
If your file is named LastName, you need:
[LastName] Like "[AÀÁÂÃÄ]*"

The same applies to the next 25 lines as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Jaycee said:
In the NW example, the condition field is
[CompanyNameFilters]=1, etc. Since I want the macro to
filter on employees' last names, I entered
[LastNameFilter]=1, and so forth, as the condition.

In the Action Arguments section, I entered
[LastNameFilter] Like "A*" as the Where Condition.

I somehow thought that the macro would know to use the
LastName field as the source, but I see now that I need a
filter named "LastNameFilter." I searched Help and went
back to NW to find out how to create a named filter, but
with no success. I understand how to filter data, but
it's creating an object named LastNameFilter that I don't
get. Thanks for your quick reply and your assistance!



-----Original Message-----
You have something on your form named "LastNameFilter", or you have that
word somewhere in your macro. What is it? Why are you referring to it?


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Click to expand...
Click to expand...
 
OK, I think the problem is with names. When I click on a
toggle button, the message "There are no records for this
letter" appears. This would be correct, of course,
except that there are records with the letter I'm
selecting. For the sake of simplicity, in this post, "A"
will represent all of the letters.

The macro is set up as follows:
LastName.Alpha Buttons
Condition: [LastNameFilter]=1
Where Condition: [LastName] Like "A*"

The option group is named LastNameFilter.
The AfterUpdate reads LastName.Alpha Buttons

The toggle button is named A, the caption is &A, and the
option value is 1.

The option group was looking for a LastName field on the
form; however, the field containing the last names in a
combo box is the EmployeeID field. The form is based on
the EventDetailsTbl, which does not have the LastName
field -- that's in the EmployeesTbl. The EmployeesTbl is
related to the EventsDetailsTbl via EmployeeID.

I added the LastName field to the EventDetailsTbl and
recreated the combo box so that the LastName field
appears on the form. (This seemed wrong, but I'm not sure
why.) But when the EventDetailsTbl is viewed, it shows
the EmployeeID number instead of the last name in the
LastName field. This is why the macro can't find records
associated with any letter -- they're all numbers!

Again, where did I get off track? Thanks much!

-----Original Message-----
In the NorthWind example, the option group is named
CompanyNameFilters
Perhaps your option group is named
LastNameFilter

When you look at the Customer Phone List macro in Northwind, do you see
columns named Macro Name and Condition? If not, go to the View menu and
click the Macro Name and Condition items. You can then see how the 27
conditions and more that define when each of the various ApplyFilter actions
are performed.

Select the first line, i.e. the one where Condition contains:
[CompanyNameFilters]=1
If your option group is named LastNameFilter, and you A toggle button has
the value 1, you will need:
[LastNameFilter]=1

In the lower pane, the WhereCondition reads:
[CompanyName] Like "[AÀÁÂÃÄ]*"
If your file is named LastName, you need:
[LastName] Like "[AÀÁÂÃÄ]*"

The same applies to the next 25 lines as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
In the NW example, the condition field is
[CompanyNameFilters]=1, etc. Since I want the macro to
filter on employees' last names, I entered
[LastNameFilter]=1, and so forth, as the condition.

In the Action Arguments section, I entered
[LastNameFilter] Like "A*" as the Where Condition.

I somehow thought that the macro would know to use the
LastName field as the source, but I see now that I need a
filter named "LastNameFilter." I searched Help and went
back to NW to find out how to create a named filter, but
with no success. I understand how to filter data, but
it's creating an object named LastNameFilter that I don't
get. Thanks for your quick reply and your assistance!



-----Original Message-----
You have something on your form
Click to expand...
named "LastNameFilter",
or you have that
word somewhere in your macro. What is it? Why are you referring to it?


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jaycee" <[email protected]> wrote
Click to expand...
in
message
Thanks, Allen. I followed the Northwind pattern to
create a macro named Alpha Buttons; the filter is
LastNameFilter. Next I created an option group in the
form and put the macro in the AfterUpdate field of the
option group properties. I then set about to create the
toggle buttons--starting with A and B and testing them
out. Clicking the button produced the following message:
_____________________
The object doesn't contain the Automation
object "LastNameFilter."

You tried to run a VB procedure to set a property or
method for an object. However, the component doesn't
make the property available for Automation operations.

Check the component's documentation for information on
the properties and methods it makes available for
Automation operations.
_______________________

I tried using the wizard to create the option group, but
it only allowed 20 entries.

Where'd I go wrong? Thanks much!!!

Jaycee

-----Original Message-----
It was probably the Customer Phone List form in the
Northwind sample
database.

These are toggle buttons in an option group.

The AfterUpdate event of the group calls the macro:
Customer Phone List.Alpha Buttons

The maco uses the ApplyFilter action to filter the form.

Hopefully that's enough for you to see how it
Click to expand...
works,
and
copy the strategy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Recently I saw a form in a sample database that had
buttons across the bottom, one for each letter of the
alphabet. I assume that clicking on "C" jumped
Click to expand...
to
the
first record where the Last Name, for instance, began
with "C."

Can anyone tell me how to create these buttons? I
made a
couple of attempts at figuring it out, but without
success.

If the process is complex, please be specific. I've
had
no training in SQL/VB.

Thanks!


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
You are attempting to filter the form on the first character of the LastName
field, but the form has no LastName field?

If you want to filter by that field, you need to include it in the
RecordSource of the form. Create a query containing both the existing table
and your EmployeesTbl. In the query's output grid, include all the fields
from your main table and EmployeesTbl.LastName. If you have some records
where the EmployeeID is blank, double-click the join line between the 2
tables, and choose the option:
All records from Events..., and any matches from EmployeesTbl.
Save the query.

Open the form in design view.
Change the RecordSource property of the form to this query.
Because the field is now in the RecordSource, you can filter on it.

You have a macro and a field with the same name ("LastName"). Not sure that
will cause you grief, but just bear it in mind if there is any kind of
naming clash.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, I think the problem is with names. When I click on a
toggle button, the message "There are no records for this
letter" appears. This would be correct, of course,
except that there are records with the letter I'm
selecting. For the sake of simplicity, in this post, "A"
will represent all of the letters.

The macro is set up as follows:
LastName.Alpha Buttons
Condition: [LastNameFilter]=1
Where Condition: [LastName] Like "A*"

The option group is named LastNameFilter.
The AfterUpdate reads LastName.Alpha Buttons

The toggle button is named A, the caption is &A, and the
option value is 1.

The option group was looking for a LastName field on the
form; however, the field containing the last names in a
combo box is the EmployeeID field. The form is based on
the EventDetailsTbl, which does not have the LastName
field -- that's in the EmployeesTbl. The EmployeesTbl is
related to the EventsDetailsTbl via EmployeeID.

I added the LastName field to the EventDetailsTbl and
recreated the combo box so that the LastName field
appears on the form. (This seemed wrong, but I'm not sure
why.) But when the EventDetailsTbl is viewed, it shows
the EmployeeID number instead of the last name in the
LastName field. This is why the macro can't find records
associated with any letter -- they're all numbers!

Again, where did I get off track? Thanks much!

-----Original Message-----
In the NorthWind example, the option group is named
CompanyNameFilters
Perhaps your option group is named
LastNameFilter

When you look at the Customer Phone List macro in Northwind, do you see
columns named Macro Name and Condition? If not, go to the View menu and
click the Macro Name and Condition items. You can then see how the 27
conditions and more that define when each of the various ApplyFilter actions
are performed.

Select the first line, i.e. the one where Condition contains:
[CompanyNameFilters]=1
If your option group is named LastNameFilter, and you A toggle button has
the value 1, you will need:
[LastNameFilter]=1

In the lower pane, the WhereCondition reads:
[CompanyName] Like "[AÀÁÂÃÄ]*"
If your file is named LastName, you need:
[LastName] Like "[AÀÁÂÃÄ]*"

The same applies to the next 25 lines as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
In the NW example, the condition field is
[CompanyNameFilters]=1, etc. Since I want the macro to
filter on employees' last names, I entered
[LastNameFilter]=1, and so forth, as the condition.

In the Action Arguments section, I entered
[LastNameFilter] Like "A*" as the Where Condition.

I somehow thought that the macro would know to use the
LastName field as the source, but I see now that I need a
filter named "LastNameFilter." I searched Help and went
back to NW to find out how to create a named filter, but
with no success. I understand how to filter data, but
it's creating an object named LastNameFilter that I don't
get. Thanks for your quick reply and your assistance!



-----Original Message-----
You have something on your form
Click to expand...
named "LastNameFilter",
or you have that
word somewhere in your macro. What is it? Why are you referring to it?


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jaycee" <[email protected]> wrote
Click to expand...
in
message
Thanks, Allen. I followed the Northwind pattern to
create a macro named Alpha Buttons; the filter is
LastNameFilter. Next I created an option group in the
form and put the macro in the AfterUpdate field of the
option group properties. I then set about to create the
toggle buttons--starting with A and B and testing them
out. Clicking the button produced the following message:
_____________________
The object doesn't contain the Automation
object "LastNameFilter."

You tried to run a VB procedure to set a property or
method for an object. However, the component doesn't
make the property available for Automation operations.

Check the component's documentation for information on
the properties and methods it makes available for
Automation operations.
_______________________

I tried using the wizard to create the option group, but
it only allowed 20 entries.

Where'd I go wrong? Thanks much!!!

Jaycee

-----Original Message-----
It was probably the Customer Phone List form in the
Northwind sample
database.

These are toggle buttons in an option group.

The AfterUpdate event of the group calls the macro:
Customer Phone List.Alpha Buttons

The maco uses the ApplyFilter action to filter the form.

Hopefully that's enough for you to see how it
Click to expand...
works,
and
copy the strategy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Recently I saw a form in a sample database that had
buttons across the bottom, one for each letter of the
alphabet. I assume that clicking on "C" jumped
Click to expand...
to
the
first record where the Last Name, for instance, began
with "C."

Can anyone tell me how to create these buttons? I
made a
couple of attempts at figuring it out, but without
success.

If the process is complex, please be specific. I've
had
no training in SQL/VB.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Actually, the form did have a LastName field (I added it
to the EventDetailsTbl), but when the table was viewed,
the EmployeeID number was shown instead of the name.

Sooo...I removed the LastName field from the
EventDetailsTbl.

Then, following your instructions:

I created a query using all fields from the
EventDetailsTbl and the LastName field from the
EmployeesTbl. The two tables are related via
EmployeeID. There are no records where the EmployeeID is
blank.

I changed the RecordSource of the form to this query.

When I clicked on "A" a message appeared: There is no
LastName field on the form. I checked the RowSource of
the combo box containing last names and it read: SELECT
EmployeesTbl.EmployeeID, EmployeesTbl.LastName FROM
EmployeesTbl; -- which seems right, but apparently isn't.

I deleted the combo box and made several unsuccessful
attempts to create a new one that would work, but when
tested, each resulted in the error message that I was
trying to enter text in a number field. Also, when I
looked at the query again, some of the last names had
changed to ID numbers (for the records I had tested the
combo box on).

My continued thanks for your assistance ...

Jaycee

-----Original Message-----
You are attempting to filter the form on the first character of the LastName
field, but the form has no LastName field?

If you want to filter by that field, you need to include it in the
RecordSource of the form. Create a query containing both the existing table
and your EmployeesTbl. In the query's output grid, include all the fields
from your main table and EmployeesTbl.LastName. If you have some records
where the EmployeeID is blank, double-click the join line between the 2
tables, and choose the option:
All records from Events..., and any matches from EmployeesTbl.
Save the query.

Open the form in design view.
Change the RecordSource property of the form to this query.
Because the field is now in the RecordSource, you can filter on it.

You have a macro and a field with the same name ("LastName"). Not sure that
will cause you grief, but just bear it in mind if there is any kind of
naming clash.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, I think the problem is with names. When I click on a
toggle button, the message "There are no records for this
letter" appears. This would be correct, of course,
except that there are records with the letter I'm
selecting. For the sake of simplicity, in this post, "A"
will represent all of the letters.

The macro is set up as follows:
LastName.Alpha Buttons
Condition: [LastNameFilter]=1
Where Condition: [LastName] Like "A*"

The option group is named LastNameFilter.
The AfterUpdate reads LastName.Alpha Buttons

The toggle button is named A, the caption is &A, and the
option value is 1.

The option group was looking for a LastName field on the
form; however, the field containing the last names in a
combo box is the EmployeeID field. The form is based on
the EventDetailsTbl, which does not have the LastName
field -- that's in the EmployeesTbl. The EmployeesTbl is
related to the EventsDetailsTbl via EmployeeID.

I added the LastName field to the EventDetailsTbl and
recreated the combo box so that the LastName field
appears on the form. (This seemed wrong, but I'm not sure
why.) But when the EventDetailsTbl is viewed, it shows
the EmployeeID number instead of the last name in the
LastName field. This is why the macro can't find records
associated with any letter -- they're all numbers!

Again, where did I get off track? Thanks much!

-----Original Message-----
In the NorthWind example, the option group is named
CompanyNameFilters
Perhaps your option group is named
LastNameFilter

When you look at the Customer Phone List macro in Northwind, do you see
columns named Macro Name and Condition? If not, go to the View menu and
click the Macro Name and Condition items. You can then see how the 27
conditions and more that define when each of the various ApplyFilter actions
are performed.

Select the first line, i.e. the one where Condition contains:
[CompanyNameFilters]=1
If your option group is named LastNameFilter, and you A toggle button has
the value 1, you will need:
[LastNameFilter]=1

In the lower pane, the WhereCondition reads:
[CompanyName] Like "[AÀÁÂÃÄ]*"
If your file is named LastName, you need:
[LastName] Like "[AÀÁÂÃÄ]*"

The same applies to the next 25 lines as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
In the NW example, the condition field is
[CompanyNameFilters]=1, etc. Since I want the macro to
filter on employees' last names, I entered
[LastNameFilter]=1, and so forth, as the condition.

In the Action Arguments section, I entered
[LastNameFilter] Like "A*" as the Where Condition.

I somehow thought that the macro would know to use the
LastName field as the source, but I see now that I need a
filter named "LastNameFilter." I searched Help and went
back to NW to find out how to create a named filter, but
with no success. I understand how to filter data, but
it's creating an object named LastNameFilter that I don't
get. Thanks for your quick reply and your assistance!




-----Original Message-----
You have something on your form named "LastNameFilter",
or you have that
word somewhere in your macro. What is it? Why are you
referring to it?


--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks, Allen. I followed the Northwind pattern to
create a macro named Alpha Buttons; the filter is
LastNameFilter. Next I created an option group in the
form and put the macro in the AfterUpdate field of the
option group properties. I then set about to create
the
toggle buttons--starting with A and B and testing them
out. Clicking the button produced the following
message:
_____________________
The object doesn't contain the Automation
object "LastNameFilter."

You tried to run a VB procedure to set a property or
method for an object. However, the component doesn't
make the property available for Automation operations.

Check the component's documentation for information on
the properties and methods it makes available for
Automation operations.
_______________________

I tried using the wizard to create the option group,
but
it only allowed 20 entries.

Where'd I go wrong? Thanks much!!!

Jaycee

-----Original Message-----
It was probably the Customer Phone List form in the
Northwind sample
database.

These are toggle buttons in an option group.

The AfterUpdate event of the group calls the macro:
Customer Phone List.Alpha Buttons

The maco uses the ApplyFilter action to filter the
form.

Hopefully that's enough for you to see how it works,
and
copy the strategy.

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot
org.

Recently I saw a form in a sample database that had
buttons across the bottom, one for each letter of
the
alphabet. I assume that clicking on "C" jumped to
the
first record where the Last Name, for instance,
began
with "C."

Can anyone tell me how to create these buttons? I
made a
couple of attempts at figuring it out, but without
success.

If the process is complex, please be specific. I've
had
no training in SQL/VB.

Thanks!
Click to expand...
Click to expand...


.
Click to expand...
 
Jaycee, placing the LastName field into the RowSource of the combo is not
enough.

If you want to filter your form on the LastName field, you need to get it
into the RecordSource of the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Actually, the form did have a LastName field (I added it
to the EventDetailsTbl), but when the table was viewed,
the EmployeeID number was shown instead of the name.

Sooo...I removed the LastName field from the
EventDetailsTbl.

Then, following your instructions:

I created a query using all fields from the
EventDetailsTbl and the LastName field from the
EmployeesTbl. The two tables are related via
EmployeeID. There are no records where the EmployeeID is
blank.

I changed the RecordSource of the form to this query.

When I clicked on "A" a message appeared: There is no
LastName field on the form. I checked the RowSource of
the combo box containing last names and it read: SELECT
EmployeesTbl.EmployeeID, EmployeesTbl.LastName FROM
EmployeesTbl; -- which seems right, but apparently isn't.

I deleted the combo box and made several unsuccessful
attempts to create a new one that would work, but when
tested, each resulted in the error message that I was
trying to enter text in a number field. Also, when I
looked at the query again, some of the last names had
changed to ID numbers (for the records I had tested the
combo box on).

My continued thanks for your assistance ...

Jaycee
 
Allen,

The combo box and alpha buttons macro work OK now. I had
to create a query from the EmployeeNamesTbl with only the
LastName in it, then based the combo box on that query.
Once I named the combo box "LastName," the macro worked.

Thanks *very much* for your help -- it was invaluable!

Jaycee
 
Back
Top