Tables, lookup/combo box issues... 1 value lookup autofill other table columns

[PsyKo]

hi all

i seem to be having a rather annoying time with a simple table... im using access 2010 & havnt done ANY access work since 2003!!! so a rather long time... BUT, My Problem;

PROBLEM;
creating a simple table for orders where the item is selected from a lookup (this can be combo box/list ect, i dont mind) but i want the description & price to be looked up and displayed automatically after selecting the product from the drop list.

Table;
ID | Primary Key
Customer Name | lookup from client table
Date | Auto date for that day
Product | *** This is the lookup (from Product Table) ***
Product Description | *** i want this to automatically display the descriptio ***
Product Price | *** i want this to automatically display the price ***
Qty | Number
Total | Product price * QTY

Other Notes;
Product table;
ID | Primary Key
Product | Text
Product Description | Text
Product Price | Currency

i will eventually create this table into a form but need to have it as a table to start with. i know its possible as i have seen various access apps doing it but have been unable to see how they have done it.

Thanks in advance

 

[John Big Booty]

Welcome to the forum.

You can use the following as the Control Source of an unbound text box to display the additional info;

=ComboName.Column([B][COLOR="Red"]x[/COLOR][/B])

Where x represents the column in the combo that holds the info you wish to display. Remember that the columns in a combo or list box for that matter are numbered from Zero on up.

This info must form part of the combo's Row Source but does not necessarily have to be in a visible column.

 

[PsyKo]

hi john,

thanks for the quick reply, however i have tried this, (correctly to the best of my knowledge! lol)

but to no avail...

is there more i should be telling the table to do?

i have set the lookup to the following

display control | Combo Box
Row Source Type | table/query ***although have also triad all 3 avaliable options *** do i need to specify here further?
Row Source | =ComboName.Column(3)

i have set this to 3 because
0 | ID | Primary Key
1 | Product | Text
2 | Product Description | Text
3 | Product Price | Currency

obviously this is just the price so far, once working i will also do the description...

regards

 

[John Big Booty]

Have a look at the attached sample for a working example.

 

[PsyKo]

hi john,

yes, what you have uploaded works withn the FORM, however i want the same result within a TABLE.

the idea being the i will have a master table, 'invoice' which will call all client info, name address ect, create a unique ID for 'invoice' and then this lookup will be for products that will be added line by line... once the tables do the job then i will be making them into forms, but i nned the tables to work first.

so something like;

---

Invoice No (primary key)

Client Name (lookup from client table)

Product 1 (a lookup from the product table) | Description (also from the product table) | Price (also from the product table) | Qty (user input) | Total (calulated sum)

Next lines,
Product 2,
Product 3,
Product 4,
ect

hope tht makes sense! lol

Regards

 

[John Big Booty]

If you are already storing an ID in the table from which all that additional Info is being derived, then storing anything else, is not in the best interests of good Data Normalisation practices.

Remember that Tables are simply a place where your data is stored and your User should not have direct access to that data. All user interaction with the data should happen through the "Filter" of a Form (either directly or via a query). In this way you can control how your user views and interacts with that underlying data.