This seems like an easy lookup...what am I missing?

[Scott H]

Ok, I've only got fairly basic Access knowledge, I'm better at SQL,
but here goes. I've tried a million ways of doing this, and have had
no luck so far, any help would be greatly appreciated.

I'm trying to make a fairly basic list of part numbers and pull in the
part's description. The user enters a part number via an Access Form
(continuous form view) and after they type in the part number, the
database needs to pull in the part's description from a table. The
table has the entire list of possible part numbers and it's
descriptions in it. When a person enters a part number, its saved in
separate table. How do I make the form auto-populate the description
field (pulling the data from that table) based on what the user types
in for a part number?

Note : the form is in continuous form view (the users will be
routinely adding and deleting parts on the form) and I'm stuck with
Access 97 (its what we have the license for)

 

[John W. Vinson]

Ok, I've only got fairly basic Access knowledge, I'm better at SQL,
but here goes. I've tried a million ways of doing this, and have had
no luck so far, any help would be greatly appreciated.

I'm trying to make a fairly basic list of part numbers and pull in the
part's description. The user enters a part number via an Access Form
(continuous form view) and after they type in the part number, the
database needs to pull in the part's description from a table. The
table has the entire list of possible part numbers and it's
descriptions in it. When a person enters a part number, its saved in
separate table. How do I make the form auto-populate the description
field (pulling the data from that table) based on what the user types
in for a part number?

Note : the form is in continuous form view (the users will be
routinely adding and deleting parts on the form) and I'm stuck with
Access 97 (its what we have the license for)

If you just want to display the description, include the description field in
the combo box's RowSource (you *are* using a combo box to look up the part
number... right??) and put a textbox on the form with a Control Source

=comboboxname.Column(n)

where n is the zero based index of the description field. You should certainly
NOT try to copy the description from the table of parts into any other table.

I'm not clear on your table structures though: what table is this form based
upon? What tables do you have and how are they related?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Scott H]

If you just want to display the description, include the description field in
the combo box's RowSource (you *are* using a combo box to look up the part
number... right??) and put a textbox on the form with a Control Source

=comboboxname.Column(n)

Not using a combo box right now because there's 35,000 different part
numbers, I thought that'd make too long of combo box. I suppose I
could still do it, as long as they can bring up the part number by
typing it in and not having to scroll down the huge list.

where n is the zero based index of the description field. You should certainly
NOT try to copy the description from the table of parts into any other table.

I'm not clear on your table structures though: what table is this form based
upon? What tables do you have and how are they related?
--

Two tables :

Main Table : Item (user inputed; can be added & deleted), Description
(blank by default, to be looked up from Lookup table once user inputs
the Item)
This is the one the form is based on.

LookupTable : Item (35,000 items), Description (35,000 matching
descriptions)
This is the one that has the pre-set data loaded into it.

So basically, if Main.Item = Lookup.Item; then I want Main.Description
to equal Lookup.Description.

 

[Scott H]

Not using a combo box right now because there's 35,000 different part
numbers, I thought that'd make too long of combo box.  I suppose I
could still do it, as long as they can bring up the part number by
typing it in and not having to scroll down the huge list.



Two tables :

Main Table : Item (user inputed; can be added & deleted), Description
(blank by default, to be looked up from Lookup table once user inputs
the Item)
This is the one the form is based on.

LookupTable : Item (35,000 items), Description (35,000 matching
descriptions)
This is the one that has the pre-set data loaded into it.

So basically, if Main.Item = Lookup.Item; then I want Main.Description
to equal Lookup.Description.- Hide quoted text -

- Show quoted text -

Update on this, I went ahead with the combo box idea like you mention
and it works perfectly, so I think I'll stick with that rather than
trying to make the part number box a text box. Thanks for your help!

 

[John W. Vinson]

Update on this, I went ahead with the combo box idea like you mention
and it works perfectly, so I think I'll stick with that rather than
trying to make the part number box a text box. Thanks for your help!

See http://allenbrowne.com/ser-32.html for a twist to make this more
efficient.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com