Lookup Help

[Ja (:]

I have a lookup on a table that calls ProductID, ProductName and ProductCost from tblProducts. The lookup is on the field ProductID on tblBasket and when the item is selected it displays the correct value.

However when the item is selected i need to take the ProductCost value from tblProducts and have it on the field ProductCost on tblBasket.

Im sure ive done this before, but i cant remember how i went about it. Anyone have an idea how i could achieve this?

Thanks.

 

[RuralGuy]

Is this what you have? The Evils of Lookup Fields in Tables It sounds to me as though you can do what you want in a query assuming you have your relationships set up correctly.

 

[Ja (:]

I'm not sure, but basically speaking all i want to do is display a price based on the currently selected ProductID? Whats the easiest way i can do that?

 

[RuralGuy]

Are you displaying the price on a form? Do you have any relationships set up between your tables so you could join them in a query?

 

[Ja (:]

I have relationships set up yes.

The main form has tblCustomer the subform has tblBasket.

tblBasket has CustID, ProductID (joint primary key) NoOfProduct, ProductCost, SubTotal

When i add the items to the form i dont add CustID from basket just from the tblCustomer.

I select which customer it will be then in the basket i select items using productID.
Having CustID and ProductID as a joint primary key means that each item can only be added to the basket once and that to have more than one NoOfItems has to be entered.

Then i wanted to the ProductCost to load based on the ProductID selection.

The subtotal field was going to be a running total which would be NoOfProduct*ProductCost then add up each line as more items were added. Then the highest subtotal value would be used on the order table as total cost.

Thanks

 

[RuralGuy]

If you bind your tblBasket form to a query that joins the Basket table with the Products table on the ProductID field then the price will be available in the RecordSource of the form. If historically, you need the price to be in the Basket table then you can use the Column property of the ComboBox to copy the price in the AfterUpdate event of the ComboBox.

 

[Ja (:]

Okay youve lost me

If i join the 2 tables in a query and add them onto the basket form then when i click the drop down box it displays options but doesnt let me choose one, just gives error beep?

the price is displayed straight away but only of the first item

But the thing is i need to be able to have multiple item, like this:

http://i5.tinypic.com/6xsg38w.jpg

thanks

 

[RuralGuy]

Any chance you can post your db in acXP or ac2k format? Remove any sensitive data and Compact and Repair before you zip it. It needds to be < 394KB.

 

[Ja (:]

Its a mess at the moment really, just trying different things. Once ive worked out this problem i should be okay.

thank you again!

 

[RuralGuy]

See if there are some ideas you can use in this revision of your db.

 

[Ja (:]

..omg i love you thats awesome

Thank you so much!

 

[RuralGuy]

You are certainly welcome. You need to decide if you want to retain the selling price in the Basket table for historical reasons. If the price changes then previous baskets will change. It is trivial to put the current price in the tblBasket when you select the product.

 

[Ja (:]

Ive saved the info from the basket i need to, but is there a way i can take the value from Basket Total and put it into a field on another table?

 

[RuralGuy]

Without knowing more, you could use an UPDATE or APPEND query. What field of what table?

 

[Ja (:]

I wanted Basket total to go to SubTotal on tblOrders and at the same time CustID to tblOrders

 

[RuralGuy]

You may wish to revisit your design. Normally when we do an Invoice, one table contains the Invoice number and customer details or ForeignKeys and another table contains the line items of the invoice. Is that similar to what you are doing?

 

[Ja (:]

ive got a table that stores all items ordered, which has custmer id, product id, cost, amount of item

then the order table contains total price of the order, shipping options and selects card from payment table

the basket is only temporary i run an append query once the order has been placed.

 

[RuralGuy]

I'm sorry Ja. I seem to have dropped this thread. Have you resolved your issues or are you still struggling with it?