Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Auto Populate a field (Access 97)

    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
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Populate a field (Access 97)

    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?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Populate a field (Access 97)

    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
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Populate a field (Access 97)

    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.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Populate a field (Access 97)

    Hi Hans

    That's given something to think about.

    Thank you very much for your continued patience.

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Auto Populate a field (Access 97)

    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)
    Regards
    John



  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Populate a field (Access 97)

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

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Auto Populate a field (Access 97)

    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
    If you are a fool at forty, you will always be a fool

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto Populate a field (Access 97)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •