Lookups in tables

[lynxbci]

Hello,
I have a table tbl Packing List where the user inputs a product code. This field is restricted as it looks up possible values from tbl product list This works ok, as I have linked the tables.

I would then like the description, also stored in tbl product list to also be retrieved into a description field in tbl Packing List.

It seems a simple enough process but I can not work it out.

Summary
User inputs code into table, then we want description for that code to come into the description field.


thanks

 

[pr2-eugin]

Why not use ComboBoxes, and then you can extract the different field values? http://www.baldyweb.com/Autofill.htm

 

[lynxbci]

Thanks, but it doesn't do what I need.

I can use this method if I am filling in multiple text fields on a form, but not to populate 2 fields in a table it seems.

 

[pr2-eugin]

Wait, you are saying you want to store the a value of one field depending on another field ? This is a clear violation of the Fundamentals of Database Design - Avoiding Calculations

More on calculated fields, http://allenbrowne.com/casu-14.html

Evils of Lookup : http://access.mvps.org/access/lookupfields.htm

To remove lookups in tables : http://www.btabdevelopment.com/ts/removelookups

 

[lynxbci]

Thought it might be, so I will get round it by using text fields instead of a table then output them to a table using vba. Thanks anyway.

 

[pr2-eugin]

You do not need to store this information anywhere, you have Table A which already holds the information you need. You only need to JOIN them to get what you want. You do not need to copy Stuff to Table B. Why are you storing redundant data?

 

[lynxbci]

Hi
Ok I have a user who wants to create a packing list, they will type in a code, and it will get them a description then they add a volume and it will calc boxes, and pallets etc, then this needs to be in a report.

It could be 1 row or 30 rows I don't want 30 lines of text boxes and I can not think of another way to do it.