Automatic filling a field

[R. BRADFORD THOMAS]

Good Morning,
I us Windows 2000 and Access 2000.

I have a simple data entry form. 1st field is ItemNumber and the 2nd field
is ItemName. I enter the ItemNumber and then TAB to the field ItemName.

I want the ItemName field to "automatically" look at the ItemNumber just
entered and find the associated ItemName in a Table and put that ItemName in
the field.

I have tried using a Combo Box, but I think my ignorance is overpowering me.

Any help will be appreciated.

Brad

 

[Jeff Boyce]

Brad

You haven't described the underlying data structure. You didn't indicate if
the controls ("fields") are bound to a table/query, or are unbound.

A combo box is the usual way to select an item from a list (rather than
forcing a user to remember and correctly enter an ItemNumber).

If your row source of the combo box is a query that returns both the
ItemNumber and the ItemName, you have (most) everything you need. Set the
first column's column width to "0" in the combo box and it won't show, but
will be the value saved (if the control is bound to a table/query field).
Set the second column's width to whatever shows the ItemName fully. Both of
these are under the Column Width property of the combo box (select
Properties when you have the combo box selected in form design mode).

You don't need a text box to show ItemName, as it will show in the combo
box.

 

[R. BRADFORD THOMAS]

Good morning Jeff,

Thank you for your reply. The Form is bound to a Query (This is a Data entry
Form). I understand your response; however the ItemNumber is in one field
and the ItemName field is in the following field where the Combo Box is
found. I want to have the Combo Box find the ItemName (from a different
Query) by having the Combo Box use the ItemNumber from the previous field.

From what you have told me I see I am on the wrong path. I will code the "on
Lost focus" event of the previous field to find the ItemName and place it
into the right spot.

Thanks for your response and help.

Brad

 

[Jeff Boyce]

Actually, Brad, I would discourage that approach.

If you already have a combo box that: 1) has a source of ItemNumber and
ItemName; 2) displays only the ItemName (using "0" width for the first
(ItemNumber) column; and stores the ItemNumber in the table underlying the
form, there's no need to put another field in to show the ItemName.

Not only does it already show in the combo box, even though the ItemNumber
is stored, but this (my suggestion) avoids storing duplicate data. If your
lookup table already "knows" what ItemName goes with what ItemNumber, any
other table in which you have interest in ItemName only needs to contain the
ItemNumber.

This is a more-normalized, relational design... or have I misunderstood?

 

[R. BRADFORD THOMAS]

No, I think you are correct. Thank you for the info.

Brad