Index for a database

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi

I have created a very simple database to store my addresses. Now I would
like to add an index much like the one in the 'Northwinds' sample database.
Except I don't know how to use that example as I have only used Access once!
I have tried setting command buttons with each letter of the alphabet but my
problem is getting it to goto the field name starting with that letter?
Appreciate any expert's advise.

Thanks

Jim
 
Hi

I have created a very simple database to store my addresses. Now I would
like to add an index much like the one in the 'Northwinds' sample database.
Except I don't know how to use that example as I have only used Access once!
I have tried setting command buttons with each letter of the alphabet but my
problem is getting it to goto the field name starting with that letter?
Appreciate any expert's advise.

Thanks

Jim
Click to expand...

What are you using to view your records: table view, or a form? Since
you mention "command buttons", I assume it's a form.

An index in a relational database management system (RDBMS) such as
MS-Access is, does NOT sort your data like an index in a dBase or
similar flat-file database. Depending on how many records are in the
table of addresses, you might not even need an index. All the index
does is to make it more efficient to pull specific records out of your
table when the selection criteria applies to the particular field(s)
upon which the index is built. Unles you have more than 500 - 1,000
addresses, you probably won't even notice the difference in speed when
selecting addresses with or without the index.

The table where you store your data should be considered to be like a
bucket of unordered items. These "items" are the records which get
entered into the table in some more or less random order. If you
import them from a text file or Excel spreadsheet where the data was
more or less sorted, then the native table order *might* reflect that
order. But once you have entered a new record, there is no facility to
put that record in order with the existing data unless you use a
select query.

IOW, you need to create a select query which sorts the data according
to criteria which you have to specify in the query (NOT in table view
.... these are ignored as soon as you try to set up your query). You
can change the sort order dynamically by using a form, or buttons on a
form, but first of all you need to be able to create a select query
which presents the data -- very similarly to the naked table view, but
filtered with the additional criteria.

The easiest thing to try (if you are in the query design view) is to
add the table to the query, then drag the "star" down to the first row
and column on the grid. Then drag the individual column names in the
order you wish the data to be sorted to the next columns in the grid.
Since the "star" means that all data in the table will be shown, you
can uncheck the "Show" checkbox for the additional columns, but be
sure to set the sort order to "Ascending" or "Descending" as the case
might be. Once it is working satisfactorily (click on the "!" button
on the toolbar to run the query and thus text it), save it under some
meaningful name.

After you get that working, you can use the query (NOT the table) as
the recordsource for the form. THEN you can use form controls to
further influence the sort order, selection criteria and other things
which you could not do with just the table.

But the sorting happens in the query (or directly in a report ... you
will need to set up additional sorting and maybe grouping rules when
you want to print out the data in a report because the report doesn't
honor the query's sorting criteria).

Let us know how you are doing ... I'm sure there are lots of people
here who remember their first adventures with Access and will be
willing to help you out!
 
Bob

Thanks for taking the time and trouble to reply to my badly worded question.
First off, I am a novice at using Access hence the ambiguous question.
From your reply, I gather it is not an index, nor a sort, I require but a
means to jump to all records starting with the chosen letter of the
alphabet. (Not sure what you call it?)
Much like the phone pads you get where you slide or press the letter and it
takes you to the start of that letter.
I have seen it on the Northwinds sample database but cannot figure out how
to copy the code into my own database.
I was hoping the wizard would help me do that but it doesn't off me that
choice.
I put command buttons labelled A to Z and would like to got to the record
starting with that chosen letter.
Again apologies for not expressing myself too clearly.
But did that make any sense to you?

Thanks for your help.

Regards

Jim
 
First you need to set up queries for each letter.
All fields in the table will be entered, with the query sorted by (I assume) surname and this line in the criteria for the surname:
Like "a*"​
This will give you all the records with a surname starting with "a", sorted alphabetically. Create similar queries for the other letters.

Now, in the OnClick property of each command button, select event proceedure and enter the following line:

Forms![enter the name of your form here].RecordSource = "[enter the name of the appropriate query here]"​

There are ways to do it using one query and one event proceedure, both shared by all command buttons, but the above is simpler to explain.
 
Bob

Thanks for taking the time and trouble to reply to my badly worded question.
First off, I am a novice at using Access hence the ambiguous question.
From your reply, I gather it is not an index, nor a sort, I require but a
means to jump to all records starting with the chosen letter of the
alphabet. (Not sure what you call it?)
Much like the phone pads you get where you slide or press the letter and it
takes you to the start of that letter.
I have seen it on the Northwinds sample database but cannot figure out how
to copy the code into my own database.
I was hoping the wizard would help me do that but it doesn't off me that
choice.
I put command buttons labelled A to Z and would like to got to the record
starting with that chosen letter.
Again apologies for not expressing myself too clearly.
But did that make any sense to you?
Click to expand...

I would use an option group with toggle buttons. Command buttons
aren't really good for anything but running code. Also, I haven't
looked at any of Access' sample databases since I got my first version
of Access 2.0 back in 1993 or 1994, so I don't know how NW does it.
But you will need to use a select query as recordsource for your form,
not the bare table.

You can create either 26 toggle buttons, one for each letter of the
alphabet. Assign the numerical value for the letter (i.e. the Asc()
result -- Asc("A")=65, Asc("B")=66, etc.) to each of the buttons as
option value. The option group will acquire the value of whichever
button is down.

In your query, you can add criteria (a "WHERE..." clause) which
filters your records. Add a parameter to the query which has the
option group's fully qualified name. Here's an example, I'll call the
form "YourForm" and the option group "optLetters". The SQL statement
for your query should then look something like this:

PARAMETERS [Forms]![YourForm]![optLetters] LONG;
SELECT * FROM [theTable] WHERE ([theField] LIKE
Chr([Forms]![YourForm]![optLetters]) & "*") OR
([Forms]![YourForm]![optLetters] Is Null);

Note that I haven't tested this myself .. you might need to write "AS
LONG;" instead of just "LONG;" after the parameter statement (can't
remember off-hand).

You will need to write a one-line event procedure for the AfterUpdate
event of the option group. The one line should read "Me.Requery".

Finally, in order to turn off the selection and show all the records,
you will need a separate command button (not inside the option group)
which sets the option group to Null. Write an event procedure for the
OnClick event which will read like this (I'm naming your button
"cmdAllRecords" here):

Private Sub cmdAllRecords_Click()
Me.optLetters = Null
optLetters_AfterUpdate
End Sub

You have to run the AfterUpdate event procedure by calling it
explicitly because setting the option group's value in code won't
trigger the event like pressing a button would.

If you get stuck, let us know.
 
Thanks Bob

Have finally succeeded with the following code:

Private Sub a_Click()

Dim LastName As Control
DoCmd.GoToControl "LastName"
DoCmd.FindRecord "A", A_START

End Sub
I use the above for each letter of the alphabet.

However your method looks better so shall give that a try too.
I appreciate you taking the time and trouble to answer my query and am most
grateful.

Thanks again

Jim
Bob Hairgrove said:
Bob

Thanks for taking the time and trouble to reply to my badly worded
question.
First off, I am a novice at using Access hence the ambiguous question.
From your reply, I gather it is not an index, nor a sort, I require but a
means to jump to all records starting with the chosen letter of the
alphabet. (Not sure what you call it?)
Much like the phone pads you get where you slide or press the letter and
it
takes you to the start of that letter.
I have seen it on the Northwinds sample database but cannot figure out how
to copy the code into my own database.
I was hoping the wizard would help me do that but it doesn't off me that
choice.
I put command buttons labelled A to Z and would like to got to the record
starting with that chosen letter.
Again apologies for not expressing myself too clearly.
But did that make any sense to you?
Click to expand...

I would use an option group with toggle buttons. Command buttons
aren't really good for anything but running code. Also, I haven't
looked at any of Access' sample databases since I got my first version
of Access 2.0 back in 1993 or 1994, so I don't know how NW does it.
But you will need to use a select query as recordsource for your form,
not the bare table.

You can create either 26 toggle buttons, one for each letter of the
alphabet. Assign the numerical value for the letter (i.e. the Asc()
result -- Asc("A")=65, Asc("B")=66, etc.) to each of the buttons as
option value. The option group will acquire the value of whichever
button is down.

In your query, you can add criteria (a "WHERE..." clause) which
filters your records. Add a parameter to the query which has the
option group's fully qualified name. Here's an example, I'll call the
form "YourForm" and the option group "optLetters". The SQL statement
for your query should then look something like this:

PARAMETERS [Forms]![YourForm]![optLetters] LONG;
SELECT * FROM [theTable] WHERE ([theField] LIKE
Chr([Forms]![YourForm]![optLetters]) & "*") OR
([Forms]![YourForm]![optLetters] Is Null);

Note that I haven't tested this myself .. you might need to write "AS
LONG;" instead of just "LONG;" after the parameter statement (can't
remember off-hand).

You will need to write a one-line event procedure for the AfterUpdate
event of the option group. The one line should read "Me.Requery".

Finally, in order to turn off the selection and show all the records,
you will need a separate command button (not inside the option group)
which sets the option group to Null. Write an event procedure for the
OnClick event which will read like this (I'm naming your button
"cmdAllRecords" here):

Private Sub cmdAllRecords_Click()
Me.optLetters = Null
optLetters_AfterUpdate
End Sub

You have to run the AfterUpdate event procedure by calling it
explicitly because setting the option group's value in code won't
trigger the event like pressing a button would.

If you get stuck, let us know.
Click to expand...
 
One of the options of the Combo Box Wizard is to select a Record to be
displayed on the Form... because the default for Combo Boxes,
post-Access-2.0, is that the AutoExpand property is set to true, as you type
into the Combo Box, it will scroll to the matching value displayed in its
drop-down list.

This would seem to me to accomplish what you want, without much effort,
though it is a different "view" of finding what you want than the buttons.

Larry Linson
Microsoft Access MVP


Jim said:
Thanks Bob

Have finally succeeded with the following code:

Private Sub a_Click()

Dim LastName As Control
DoCmd.GoToControl "LastName"
DoCmd.FindRecord "A", A_START

End Sub
I use the above for each letter of the alphabet.

However your method looks better so shall give that a try too.
I appreciate you taking the time and trouble to answer my query and am
most grateful.

Thanks again

Jim
Bob Hairgrove said:
Bob

Thanks for taking the time and trouble to reply to my badly worded
question.
First off, I am a novice at using Access hence the ambiguous question.
From your reply, I gather it is not an index, nor a sort, I require but a
means to jump to all records starting with the chosen letter of the
alphabet. (Not sure what you call it?)
Much like the phone pads you get where you slide or press the letter and
it
takes you to the start of that letter.
I have seen it on the Northwinds sample database but cannot figure out
how
to copy the code into my own database.
I was hoping the wizard would help me do that but it doesn't off me that
choice.
I put command buttons labelled A to Z and would like to got to the record
starting with that chosen letter.
Again apologies for not expressing myself too clearly.
But did that make any sense to you?
Click to expand...

I would use an option group with toggle buttons. Command buttons
aren't really good for anything but running code. Also, I haven't
looked at any of Access' sample databases since I got my first version
of Access 2.0 back in 1993 or 1994, so I don't know how NW does it.
But you will need to use a select query as recordsource for your form,
not the bare table.

You can create either 26 toggle buttons, one for each letter of the
alphabet. Assign the numerical value for the letter (i.e. the Asc()
result -- Asc("A")=65, Asc("B")=66, etc.) to each of the buttons as
option value. The option group will acquire the value of whichever
button is down.

In your query, you can add criteria (a "WHERE..." clause) which
filters your records. Add a parameter to the query which has the
option group's fully qualified name. Here's an example, I'll call the
form "YourForm" and the option group "optLetters". The SQL statement
for your query should then look something like this:

PARAMETERS [Forms]![YourForm]![optLetters] LONG;
SELECT * FROM [theTable] WHERE ([theField] LIKE
Chr([Forms]![YourForm]![optLetters]) & "*") OR
([Forms]![YourForm]![optLetters] Is Null);

Note that I haven't tested this myself .. you might need to write "AS
LONG;" instead of just "LONG;" after the parameter statement (can't
remember off-hand).

You will need to write a one-line event procedure for the AfterUpdate
event of the option group. The one line should read "Me.Requery".

Finally, in order to turn off the selection and show all the records,
you will need a separate command button (not inside the option group)
which sets the option group to Null. Write an event procedure for the
OnClick event which will read like this (I'm naming your button
"cmdAllRecords" here):

Private Sub cmdAllRecords_Click()
Me.optLetters = Null
optLetters_AfterUpdate
End Sub

You have to run the AfterUpdate event procedure by calling it
explicitly because setting the option group's value in code won't
trigger the event like pressing a button would.

If you get stuck, let us know.
Click to expand...
Click to expand...
 
Back
Top