Populating a field value of one table with a corresponding table value

[BOONEY]

I am looking at this from the perspective of data entry into a table or form.

An example would be where you have an Item Master table with an Item # and Item Description Field. This table is fully populated with all items and descriptions.

In a second table, the user is keying in the item # and would like the description for that item it populate the descritpion field automatically based on the item number being entered. I am trying to accomplish this in Access 97 and not in exel using a vlookup.

I am looking for the specific mechanics to accomplish this. Either in a form or in the table itself.

I dont think I can be much clearer that this.

Thanks for your help regarding this.

Mike

 

[LittleSmudge]

No, NO, NO

You do NOT do that. It breaks all the rules of Normalisation, Data Integrity etc. etc.. ..

The whole point of having the "Item Master table" is that in the Data table you ONLY store the Item Number.
Then on the Form you use Access to look up the Description from the ItemMaster table that corresponds to the ItemNumber and display the description in the control.


HOW TO DO IT
On the form you place a combo box called cboItem

cboItem.ControlSource = ItemFieldName
Where ItemFieldName is the name of the Field in the data table that will contain the Item's number

cboItem.RowSource = "ItemMaster" ( or whatever the name of the ItemMaster table really is )

cboItem.ColumnCount = 2
cboItem.ColumnWidth = 0cm
cboItem.BoundColumn = 1

The user will then see a full list of available item descriptions when the combo box drops down ( User won't see the Item Number because Width = o hides the first column )
When an item is selected Access will store the NUMBER ( in the first, hidden, column ) into the data table ( because bound column = 1 ).

On return to this record at a leter date the combo box is bound to the ItemFieldName field so the value of the control is the number stored in the field. However, becuase bound column ( col 1 ) is hidden ( = ocm ) then the combo box actually displays the DESCRIPTION on the form ( works the same on a report too )



'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[BOONEY]

Thank you for your excellent response