Auto Populate a field (Access 97)

[braddy60]

Hi

I have a table called tblGifts which contains Descriptions and a table called tblGift Price which contains Prices.
I have created a drop down list containing the description when I select a description I would like the Price field to automatically select the corresponding price.
Both tables have an ID field from which I have created a relationship.

Hope this is clear

Many thanks

Braddy

[HansV]

I don't understand why you have two tables. If both contain the same ID, you might as well put the price and description in the same table. Or am I missing something?

[braddy60]

Hi Hans
I wrongly thought they should be in separate tables, If they are in the same table how do I get them into separate fields so that I can mulitply the result ie price time quantity

In the screen shot I want to select the baseball cap and the unit price drops in automaticlly.

Hope this is clearer
Braddy

[HansV]

From your screenshot, I would say that you need a setup like this:

1. A table with "product information": GiftID (primary key), Description, UnitPrice, perhaps other info.
2. A table with "order information": OrderID (primary key), CostCentre, Person, GiftID (foreign key, linked to GiftID in the "product information" table), Quantity, Date. NO Total field in the table, since Total is derived information.

Next, you need a query based on these two tables, joined on GiftID. Double click the join and select the option to include all records from the "order information" table. Add the fields you need to the query grid (use the GiftID field from the "order information" table), plus a calculated column
Total: [UnitPrice]*[Quantity]
Since this is a calculated column, it will always be up-to-date when you use the query.

Finally, create a form based on the query. Make the control for the GiftID field into a combo box, with:
- Control Source: the GiftID field from the query.
- Row Source: the name of the "product information" table
- Column Count: 2
- Column Widths: 0";1"
When the user selects a description from the combo box, the corresponding ID will be stored in the field, and the corresponding price will be filled in automatically.

[braddy60]

Hi Hans

That's given something to think about.

Thank you very much for your continued patience.

Braddy

[johnhutchison]

I think the Orderinfo table also needs a UnitPrice field.

UnitPrice in the Product table represents the current list price of each item. Unitprice in te orders table represents the price you actually sold something for.

There are two reasons for having both:

* So that historical info will still be correct if you update your prices.
* So that you can sell an item for price different to its list price if needed - it might be damaged, or you just want to give a discount to someone.

On your form, include a unitPrice control, boundto UnitPrice in the Orders table. Add Unitprice to the combo box as a third column.
In the after update event of the combo set the value of the UnitPrice control to :me![comboItem].column(2)

[HansV]

Thanks, good points. Braddy might take a look at the Northwind sample database that comes with Access to see a working example.

[braddy60]

Hi Hans

I have taken on board what John has said, but I have problem, I have created the tables as you suggested but I don't understand what you mean by foreign key linked to GiftID or how to do this.

Thanks

Braddy

[HansV]

Take a look at the Northwind database to see how a products and orders database is set up.

It might be a good idea to buy and read a book about Access and/or database design, to become familiar with the basic terminology. See for example the thread beginning at <post#=345723>post 345723</post#> for some titles.