finding records faster with list box--how do i do it?

[police officer in small town]

I have an Access database that is growing too large/too fast. I currently
move thru the form by scrolling the records one at a time, but it's too slow.


The table/form is a simple identity database: last name, first name, birth
date, etc. I need a list box/combo box (or whatever would be best) on the
form to show a list of the names that are in the database/table, then let the
user choose that name and go directly to the record. For example, I
currently start at the first letter of the last name of the person, "Adams",
and have to do a lot of clicking one-by-one to get to the records that start
with "Smith". There is no data entry connected to this, just to be able to
see the total record.

I am VERY new at this, so any directions should be simple, clear and
specific. I've read thru posts looking for something similar, but can find
final resolution.

 

[John W. Vinson]

The table/form is a simple identity database: last name, first name, birth
date, etc. I need a list box/combo box (or whatever would be best) on the
form to show a list of the names that are in the database/table, then let the
user choose that name and go directly to the record.

A Combo Box can do this. Open your form in design view, be sure the magic wand
icon on the toolbox is selected, and add a Combo Box control; choose the
wizard option to "Use this combo box to find a record".

If the wizard is uncooperative post back, it's pretty easy to do it manually.

 

[Albert D. Kallal]

I give some thoughts on searching here:

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

There is a sample search form in my super easy word merge that is much like
the ones in the above article:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[Tom Wickerath]

I have a ready-made Access application with a very powerful QBF (Query by
Form) search form, that is tailer made for your needs. I'm willing to share a
copy with you, if you send me a private e-mail message with a valid reply
address. My e-mail address is available at the bottom of the contributor's
page indicated below. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[police officer in small town]

I thank everyone for their help and suggestions. After reading some of the
items that were given to me AND giving it some more thought, I might have a
simple solution, but I can't get it to work.

First, I learned that 'large' is a relative term. My database is not
anywhere near 'large' in the sense that most of you deal with. It's only a
few thousand names.

Second, instead of an additional box/control on the form,as I had asked
about on my initial post, I realized the LASTNAME text box should really be
a combo box listing all the last names in the base, so the user can skip
directly to a certain last name then go thru the records one by one. I've
been able to place the combo box but can't it it formatted to list the names.


For example, the current form shows the last name in a text box. I can move
to the next record in the table by using the scroll buttons. My test combo
box will only show the last name of the current record, with no list of names
only blank. There are scrolling buttons that operate but show nothing when
used. The wizard walked me through the set up so I didn't think I would have
any problems.

I have 23 columns in my table. #1 is Contact ID (primary key) and column #2
is Last Name.
I have Column Count 2, Column Width 0;.8 and Bound Column is 1. I have
Control Source as LastName (the name of the field).

How do I get a LIST to appear? And how will it go to that record in the
table?

 

[Tom Wickerath]

Hello -

Second, instead of an additional box/control on the form,as I had asked
about on my initial post, I realized the LASTNAME text box should really be
a combo box listing all the last names in the base, so the user can skip
directly to a certain last name then go thru the records one by one. I've
been able to place the combo box but can't it it formatted to list the names.

Try this tutorial:

Using A Combo Box To Find A Record
http://www.access.qbuilt.com/html/find_a_record.html

However, that said, I'm not sure you really want a combo box that includes a
few thousand entries. You would probably be better off using an unbound text
box. If you are willing to consider using some VBA code, you can create a
powerful QBF (Query by Form) search form that I think you would be much
happier using. You might want to try this tutorial to see this technique in
action, using the sample Northwind database:

http://www.seattleaccess.org/downloads.htm
Look for the February 12, 2008 download: "Query By Form - Multi Select"

If you've never dealt with VBA code, well, no time like the present to give
it a try. Take a look at these two resources if you want some background
material:

DAO - Back To Basics Compilation/Demo by Tom Wickerath, Jan/Feb 2007
(Available on the same downloads page as the above sample)

and

Access Basics, written by Access MVP Crystal
http://www.accessmvp.com/Strive4Peace/Index.htm

The QBF technique I cover in the first download is one of my favorite things
to implement in databases. I'm willing to make a pledge to help you get it
working (no charge, of course), by private e-mail exchanges, if you want to
explore this method.

I have 23 columns in my table. #1 is Contact ID (primary key) and column #2
is Last Name.
I have Column Count 2, Column Width 0;.8 and Bound Column is 1. I have
Control Source as LastName (the name of the field).

A combo box that is used to assist the user with finding records is unbound.
An unbound control does not have a field specified as the Control
Source--this property is left blank. A combo box that is used to assist the
user with entering data, by allowing them to pick a value from a list, is
generally bound. In that case, the field specified as the Control Source
*MUST* be a compatible data type with the bound column. Your bound column is
column 1, which corresponds to the first column of width 0 (hidden from the
user). You didn't mention what the Row Source is for your combo box, but if
you want to use a bound combo box, to assist with data entry, then the first
field specified in the Row Source would be the bound column (as long as you
don't change the bound column property). However, I'm thinking you don't want
a bound combo box in any case.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________