Look-ups when there are many choices

[Rob Astyk]

Hi,
I apologize at the start for my lack of knowledge and the fact that this may
be a very dumb question . Here goes.

I have a table that runs to more than 2,300 entries right now and will
probably exceed 3,000 ultimately. Many of the entries are actually related,
but, except in a very few instances I do not yet know which are related. In
fact, future research may show that many more entries are related than now
appears to be the case. Thus the table was constructed with a series of
cross-reference fields so that the cross-references would appear to the right
of the main entry. I would like to keep retyping of entries to a minimum to
prevent misspellings and missed references.

Making the cross-reference fields a look-up of the name list was easy
enough, but scrolling through 2300+ names is more time consuming and tedious
than retyping the name to be cross-referenced.

Basing the look-up on a parameter query helps with the problem, but the
parameter query returns only a few of the possible cross-references and then
does not permit scrolling to get to other potential entries. This again makes
the look-up far more complex than it should be.

What I think I need is something that, in effect, opens the "Find Record"
function for the particular table and allows me to find each possible
matching record until I have the correct cross-reference entry. I just don't
have any idea how I could create such a thing.

I have a copy of The Access Bible which has been less than helpful in
everything but that /i probably need to know Visual Basic (I don't) to get
even close to the problem.

So do any of you have some suggestions?

Thank you for your help in advance.

Take care,

Rob Astyk

 

[KARL DEWEY]

Try --
Like "*" & [Enter your search criteria] & "*"

 

[MacDermott]

It sounds as if you're using a table for data entry -
I'd strongly recommend that you build a form for this purpose.
It's a whole lot more flexible.
Also remember the AutoFill feature of the combobox - if you start
typing, it autofills with the first item in its list which matches what
you've typed. This is often a lot easier to use than scrolling through the
entire drop box.

HTH

 

[Albert D.Kallal]

I have to assume that you build a nice form to edit the data.

I would simply put a button on the form that launches a another nice custom
built search form. That search form could have many options to search, and
then the user edit selects a record and the search form closes, and sets the
value of the field you use.

Here is some screen shots to give you an idea of the kind of form you can
pop up for your users...

http://www.members.shaw.ca/AlbertKallal/Search/index.html

 

[Rob Astyk]

Hi, Karl,

Try --
Like "*" & [Enter your search criteria] & "*"

Where? In the Criteria line of a Query? I'm a real dolt and way in over my
head. I just need to be talked to as if I were the dumbest person you've ever
met. Sorry!

But thank you so far,

Rob Astyk

 

[Rob Astyk]

Hi, MacDermott,

It sounds as if you're using a table for data entry -
I'd strongly recommend that you build a form for this purpose.
It's a whole lot more flexible.
Also remember the AutoFill feature of the combobox - if you start
typing, it autofills with the first item in its list which matches what
you've typed. This is often a lot easier to use than scrolling through the
entire drop box.

Actually, I'm not. I'm a little more sophisticated than that. I've got a
form for data entry and use it.

The problem is that the Look-up concatenates 4 pieces of the name (first,
Middle, Last & Organization (as "Co." or "& Sons"). Therefore, just typing
the first few letters can bring up "George Parker" or "Geographic Image"
equally. The object here is to type in the Last Name as in a parameter query
and, once I'm amongst the names that Begin with "George", be able to search
amongst the Georges for the correct one. The parameter query doesn't give me
all possibles and then it only allows me to choose from a short list of
possibles none of which, often enough, are the correct choice.

I'm not sure if I'm asking the possible. Thank you for your help so far
though. I really appreciate the response.

Take care,

Rob Astyk