Can someone explain how this would be done please?

[microsoftgirl]

I have a database that includes a number of tables, "product details" and "transaction details" are two of these tables.

In the transaction details table, I have a number of fields.

Two of these fields include "product ID" and "sale price".

I was using the lookup wizard to create a relationship between the "product ID" field within the transaction details table, and the product ID itself,

However I still have the "sale price" field.
When I select a specific product ID within the transaction details table, in the specified field, is there a way to ensure that the correct sale price for that product, will appear in the field next to the one that will hold the "product ID"?

I have included a picture to try and show you exactly what I mean.
(As soon as I select the "product ID" from the drop down menu, I want the correct sale price to appear in the field next to it, can this be done? and how?)
Replace (dot) with . on the image, this is my first post, sorry.
oi55(dot)tinypic(dot)com/2n01q39(dot)jpg

 

[pbaldy]

Haven't looked at the link, but presuming you're using a form for data entry (which you should), this should work:

http://www.baldyweb.com/Autofill.htm

In your case the second technique is appropriate, as the selling price should be saved with the record.

 

[Alansidman]

Reiterating what Paul has indicated. You should not be doing this in a table. Your data input should be done in a form.

Here is a link telling why this is not a good practice.

http://access.mvps.org/access/lookupfields.htm

Alan

 

[microsoftgirl]

Thank you both, that really helped a lot.
I was just wondering though. Instead of creating new combo box, is there any way to attach a combo box to the original text box that is generated when I select create form?

Also, I was going to use the combo box as a way to fill in all the text box fields within the form. The combo box is only allowing me to fill in one of the text box fields at a time though, and not all of them.

For example.

The combo box would maybe say:
"Select product: -----------"
Once the product is selected, I wanted the ID, product name and price to appear in the original text boxes. It shows all 3 in the combo box, however it will only place 1 of the 3 in their correct boxes though.

 

[Alansidman]

Let me make sure I understand what you are trying to do:

You have a combo box that has items you wish to select. In the query associated with the lookup, there are three fields. You wish to use the combo box to populate one field and two other.

In the query behind the lookup, the first column is numbered 0 and the second is 1, etc.

In the first text box (not the combo), type =ComboboxName.Column(x) where x represents the appropriate column number from the query. Repeat this as necessary.

 

[pbaldy]

Some additional thoughts on the matter:

http://www.baldyweb.com/Autofill.htm