Find a record on two fields on a form

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I need to find a record on a form based on the fields "Last Name" and
"First Name"?

What is the easiest way for a user to do this. There should be an
easier way for a user than having to click on the Filter and going
through three or four clicks.

A combo box is not good due to the number of Smiths in the file that
have to be scrolled to.

There should be an easier way??????Help
 
Randy said:
I need to find a record on a form based on the fields "Last Name" and
"First Name"?

What is the easiest way for a user to do this. There should be an
easier way for a user than having to click on the Filter and going
through three or four clicks.
Click to expand...

Have you tried "Filter by Form"?
A combo box is not good due to the number of Smiths in the file that
have to be scrolled to.
Click to expand...

Have you considered using a "select distinct" statement for the combo to
return unique values?

Regards,
Keith.
www.keithwilby.com
 
Filter by Forms works but takes about 5 clicks staff want something
shorter and easier as they have to pull up alot of different cases.

Would you explain select distinct statement?
 
Use two combo boxes. One for last name, one for first name. Then there will
be only one Smith.
That should work until Microsoft releases PsychicInput for XP.

Sometimes you just have to educate users. They always have these wonderful
ideas on how data entry should work. Many times they get what they want and
then don't like it.
 
They are just use to a much more user friendly system such as Approach
that doesn't require you to jump through such rediculous hoops to do
something as simple as find a record based on first and last name.
 
You have to give any application enough information to find a record. It
sounds like you are a little prejudice. In fact, how you easily you can look
up data in an application is more the ability of the developer than the
capabilities of the tool.
 
Randy said:
They are just use to a much more user friendly system such as Approach
that doesn't require you to jump through such rediculous hoops to do
something as simple as find a record based on first and last name.
Click to expand...

Well, here's something you might try, to prove the concept, then build
it into a data-entry Form:

With only a modicum of effort, you can set up Queries that you can view
in Datasheet format and get the information you (or your users) want.
It's not fancy, but it makes efficient use of screen space and is easy
to set up.

Suppose you have a table, [Names], containing the following records:

NamesID First Last More Stuff
----------- ------- ------- -------------------------
-1957848902 Mary Smith Singer and chess champion
-819964391 John Miller Alicia's husband
-819347395 John Jones Great guy
-174729864 Alicia Miller Mayor
1079702707 Mary Jones John's wife


You can define a Query, [Q_NamesLast], which will list only the last
names (once each, in alphabetical order):

SELECT DISTINCT Names.Last
FROM [Names]
ORDER BY Names.Last;

For example, running [Q_NamesLast] will look like this in Datasheet View:

Last
------
Jones
Miller
Smith

Now define another Query, [Q_NamesFirst], to show additional fields:

SELECT Names.Last, Names.First, Names.NamesID
FROM [Names]
ORDER BY Names.First;

Instead of running this by itself, open [Q_NamesLast] in Datasheet View
and use Insert --> Subdatasheet to link [Q_NamesFirst] to it; set both
of Link Child Fields and Link Master Fields to "Last". A little "+"
sign should appear at the left of each last name.

Click on one of the "+" signs; you should see a list something like this:

[Q_NamesFirst] -- assuming I clicked on "Miller":

First NamesID
------ ----------
Alicia -174729864
John -819964391

(Cosmetic comment: Since the [NamesID] values aren't in themselves very
meaningful to human beings, I suggest you right-click on that column
header and select "Hide Columns".)

As before, click on "Alicia" and select Insert --> Subdatasheet, to link
the Table [Names] to it; this time, set both of Link Child Fields and
Link Master Fields to [NamesID].

Now click on the "+" sign beside one of the names. You should see a
record from [Names] that looks something like this, assuming I clicked
on "John":

First Last More Stuff
----- ------- ----------------
John Miller Alicia's husband

Close and save everything.

Now, to retrieve any one record from [Names], you need 3 mouse clicks
(plus possible scrolling):

- Open [Q_NamesLast]
- Select desired value of [Last]
- Select desired value of [First]

.... and -- voilá -- the entire desired record from [Names] appears.

OK, you'd probably do better to make the 2nd subdatasheet be another
Query, but I did it this way to keep things simple.

Having debugged your cascading subdatasheets, you can set up subforms
that do pretty much the same thing but maybe look prettier. Good luck.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top