Find a certain key

[Mommio2]

Hi,
I have a menu screen that gives the options to add a new record, edit
an existing record, or run any of a number of reports. All are working
correctly except the edit. How can I cause it to go to a certain key? My
key is last name, first name. Let's say I have 3 Jones - Ann, Bob, & Mary.
I would like to be able to enter "Jones" or just "J" and go to the first one
(or to the beginning of the J's). Right now I have an edit button on my
menu screen that sends you to a macro. The macro has "go to" for the Record
field. I need to replace the button on the menu screen with a place to type
in the name or first letters of the name I want to find. How exactly should
I do this? What commands do I need to make it go to that record? Thanks a
bunch in advance!

 

[Arvin Meyer [MVP]]

Have a look at these 2 sample databases:

http://www.datastrat.com/Download/Search2K.zip

http://www.datastrat.com/Download/ExpandingSearch2K.zip
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[tina]

all other issues aside, i recommend against using a person's name as a
primary key for a table. pick up the phone directory in any town, and you
can see that there are lots of common, duplicate names in this world, even
in small geographic areas. what happens when you have to enter another Mary
Jones?

suggest you use an Autonumber field as the primary key for your table.

hth

 

[Mommio2]

Thanks, but I have been looking and looking at them, and they are waaayyy
over my head! Is there a simple way to just enter the first 3 letters of
the desired last name into a text box and have it search the table for the
first entry beginning with those 3 letters? Would a query do it? I'd like
to be able to do this on the first menu screen and then have it take me
right to the form which I will use to update the record. This will be a
very small table (less than 200 entries), so it is OK for the user to go to
the first one and then page through until they find the right one. Also,
how do I specify that I want just the first three letters of
Student_Last_Name? Thanks!

 

[Arvin Meyer [MVP]]

You cannot do it very well from a textbox unless you do it similarly to the
code I posted. Your alternative is to use a combo box (sometimes called a
dropdown box). Make sure that that the auto-expand property is set to yes
(the default)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

Thanks, but I have been looking and looking at them, and they are waaayyy
over my head! Is there a simple way to just enter the first 3 letters of
the desired last name into a text box and have it search the table for the
first entry beginning with those 3 letters? Would a query do it? I'd like
to be able to do this on the first menu screen and then have it take me
right to the form which I will use to update the record. This will be a
very small table (less than 200 entries), so it is OK for the user to go to
the first one and then page through until they find the right one. Also,
how do I specify that I want just the first three letters of
Student_Last_Name? Thanks!

Rather than a textbox, consider using a Combo Box based on a query
sorted by last name. The COmbo Box Wizard will create a combo for you
to find a record based on the selection, and the combo box will indeed
autofill for you - if you type CZA it will jump right to the line for
young Mr. Czarecki.

John W. Vinson[MVP]

 

[Mommio2]

Hey, that worked GREAT...well, almost! Now, my only problem is that it
lets me choose the last name from the combo box and then it takes me to the
form for updating, but it is positioned at the first record in the form, not
the one I chose in the combo box. Do I have to check for " = to "
somewhere? Thanks!

 

[onedaywhen]

i recommend against using a person's name as a
primary key for a table. pick up the phone directory in any town, and you
can see that there are lots of common, duplicate names in this world, even
in small geographic areas. what happens when you have to enter another Mary
Jones?

suggest you use an Autonumber field as the primary key for your table.

How then would you know whether the second is Mary Jones is not the
same Mary Jones entered a second time? i.e. an autonumber PK without a
natural key *facilitates* duplicates rather than preventing them.

Going with your phone directory idea, phone number would make a better
identifier than an autonumber (but not much).

Jamie.

--

 

[tina]

if there are additional fields in the table that, taken together, serve to
identify Mary Jones uniquely, then i would create a multi-field unique index
to help the user identify duplicate records at the time of data entry. since
i rarely use multi-field primary keys, my autonumber recommendation stands.

the use of surrogate keys v. natural keys has been debated in these
newsgroups numerous times, so i'm not going to engage in such a debate in
this thread. any reader who's interested in the issue need only google the
newsgroups to read those discussions.

hth

 

[onedaywhen]

the use of surrogate keys v. natural keys has been debated in these
newsgroups numerous times, so i'm not going to engage in such a debate in
this thread. any reader who's interested in the issue need only google the
newsgroups to read those discussions.

And each of those threads contains a lot of noise, so I'll briefly
state the outcome: a surrogate needs a candidate key. Thus, a more apt
caption would be surrogate+natural key v natural key.

Jamie.

--

 

[Mommio2]

Thanks for your suggestions, and I will talk it over with my principal, but
we are a very small school - only 175 students in K - 8th. The chances of
us getting 2 kids with the same 1st, middle, and last names are not very
high. I appreciate it, though, and we will certainly discuss.

 

[John Vinson]

Hey, that worked GREAT...well, almost! Now, my only problem is that it
lets me choose the last name from the combo box and then it takes me to the
form for updating, but it is positioned at the first record in the form, not
the one I chose in the combo box. Do I have to check for " = to "
somewhere? Thanks!

Please post the code. I have no idea what problem you're having
because I can't see what you're doing!

John W. Vinson[MVP]

 

[Mommio2]

Thanks, but I did it again! Every time I think I am totally stuck and can't
go on alone, I keep thinking about it and the cobwebs seem to clear away and
I get it! It is now working! Thanks a bunch for all the suggestions and
help, everyone! Couldn't do it without ya!
Mommio2