Help using lookups

[mikeaa101]

Hi

I am trying to get the following scenario to work

I have a database containing 3 tables

Billing
Customers
Components

Each table is related I have a one to many relationship existing between Components and Billing and I use a lookup in my billing table to extract the field component ID and this works fine, however I would like it to also extract another field from the component table e.g. Price. So when I choose the component ID it will automatically import the Price associated with that component. See attached screenshot

 

[pr2-eugin]

Hello mikeaa101, I would advice you not to go forward with this.. Do not use Lookup at table Level.. Use a Form to collect this information.. Look at this similar thread.. Specially Pat's reply..

 

[mikeaa101]

Thanks,

Having looked at the reply I can see why, I have no issue getting the data onto a form and have already done it however, the biggest problem for me at the moment is getting the data to total.

For example if I enter a new order into my DB using the NewOrder form when I choose the CustomerNo it populates the customer details and when I choose the componet id it populates those details too. However, I then wish to have a calculated field at the end that will multiply my UnitPrice by Quantity required. I just can't seem to get this working

Thanks

Mike

 

[pr2-eugin]

Mike, you do not need to store Calculated values in tables.. All this calculation can be gathered by means of Unbound text box/Queries.. All you need to do is create a Text box control and have its control source to be the field you wish to multiply..

= [UnitPrice] * [Quantity]

 

[mikeaa101]

Thanks, I figured it out about 2 minutes after posting

Cheers

Mike