help with finding a specific record

  • Thread starter Thread starter Kathy
  • Start date Start date
K

Kathy

I posted this in the "tables & Database" list but didn't
get a reply. My data base has several thousand records
and I would like to use the find function (Ctrl+F) to
allow the users to quickly find a specific individual.
When using Access 2002 this works great even for combo
boxes where I can search on the names that are visible
even tho the field is storing the ID number. However,
when I move the database to my production computer which
has Access 2000, it will not allow me to search on the
names in combo boxes, but only allows me to search for
the underlying number.
Is this a difference between Access 2000 and Access 2002,
or have I set some property differently somewhere along
the way without knowing it? Any help would be much
appreciated. Kathy
 
I posted this in the "tables & Database" list but didn't
get a reply. My data base has several thousand records
and I would like to use the find function (Ctrl+F) to
allow the users to quickly find a specific individual.
When using Access 2002 this works great even for combo
boxes where I can search on the names that are visible
even tho the field is storing the ID number. However,
when I move the database to my production computer which
has Access 2000, it will not allow me to search on the
names in combo boxes, but only allows me to search for
the underlying number.
Is this a difference between Access 2000 and Access 2002,
or have I set some property differently somewhere along
the way without knowing it? Any help would be much
appreciated. Kathy
Click to expand...

Just a suggestion:

Use a Query.

Table datasheets are VERY limited. You've run into one of the
limitations of the so-called "Lookup" datatype, a seriously
misdesigned feature if you ask me. The name is, of course, not stored
in your table, just an ID.

I'd suggest creating a Form based on a query linking your tables, or
with a combo box displaying the name but bound to the ID; you can put
a second unbound combo box on the Form to easily locate the record for
a particular name. This will be even simpler for the user than Ctrl-F.
 
John -- Thank you for the quick reply. I am sorry I
wasn't clear, the problem is in the forms but the forms
are calling the fields from the tables (or from a query )
that has the look-up box. Should I not use lookup boxes
in the tables? I am not sure how an unbound lookup box
would find the record, sorry to be so dense but doesn't
it have to be bound to the record to bring it up? Do
you know why this works in Access 2002 but not in 2000??
 
John -- Thank you for the quick reply. I am sorry I
wasn't clear, the problem is in the forms but the forms
are calling the fields from the tables (or from a query )
that has the look-up box. Should I not use lookup boxes
in the tables? I am not sure how an unbound lookup box
would find the record, sorry to be so dense but doesn't
it have to be bound to the record to bring it up? Do
you know why this works in Access 2002 but not in 2000??
Click to expand...

Well, ALL forms call data from tables via queries (even if it's not a
query you created yourself). http://www.mvps.org/access/lookupfields
has a critique of table lookups that you might want to read.

An unbound Combo Box (let's use the right name for the tool!) can
indeed find a record, using a little bit of VBA code in its
AfterUpdate event to synchronize with the form. Using the toolbox
wizard in form design to create the combo gives you (or should give
you) this as an option - "use this combo to find a record"; if it
doesn't, post back, the code's really very straightforward.

I don't know why 2000 and 2002 would differ in this regard, but never
having used table lookup fields after my original experimentation, I
can't say that I'd have run into the problem!
 
Back
Top