Results 1 to 2 of 2
  1. #1
    Curious is offline Novice
    Windows XP Access 2000
    Join Date
    Aug 2011
    Posts
    1

    Populating table values in Subform from a table by using Combo Box and/or DLookup

    Hi,

    Please help.

    I would like to do the following:

    In the QuoteTemplateProductSubform with a table with the following columns/fields:
    ProductModelID
    ProductDescription
    Quantity
    UnitListPrice

    Whenever I enter a ProductModelId, I would like the ProductDescription, Quantity, and UnitListPrice to populate (information from ProductModelPricingTable)

    Should I use Combo box and Dlookup to do this? If yes, how? This is my first time doing this, so please be as detail as possible. Thank you in advance for your help!


    I have the following:

    Table:
    1. QuoteTemplateTable --- the following field
    a. QuoteID (primary key)
    b. QuoteDate
    c. CustomerName
    d. CustmerPhone#

    2. QuoteTemplateProductTable --- the following field
    a. QuoteID (primary key)
    b. ProductModelID
    c. ProductDescription,


    d. Quantity
    e. UnitListPrice

    3. ProductModelPricingTable --- product information and the following field
    a. ProductModelID (primary key)
    b. ProductDiscription,
    c. Quantity,
    d. UnitlistPrice

    Relationships have been established between the above tables: (all 3 tables are connected)

    1. QuoteTemplateTable (with QuoteID) has a one-to-one Relationship with QuoteTemplateProductTable (with QuoteID)…with Inforce Referential Integrity.

    2. QuoteTemplateProductTable (with ProductModelID) has a one-to-many relationship with ProductModelPricingTable (with ProductModelID)…with Inforce Referential Integrity.

    Form & Subform:
    1. QuoteTemplateForm --- fields from QuoteTemplateTable
    2. QuoteTemplateProductSubform --- fields from QuoteTemplateProductTable (such as QuoteID, ProductModelID, ProductDescription, Quantity, and UnitListPrice)
    QuoteTemplateProductSubform with a table with the following columns/fields:
    ProductModelID
    ProductDescription
    Quantity
    UnitListPrice

    I placed the QuoteTemplateProductSubform into the QuoteTemplateform (the main form)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are you saving duplicate data? This will required VBA code. Instead, just save the key from ProductModelPricingTable into QuoteTemplateProductTable. Then you can always view the related data by joining tables on primary/foreign key fields. This is a basic principle of relational database. Display the related info in the subform by making its RecordSource be a query joining the tables QuoteTemplateProductTable and ProductModelPricingTable. The jointype would be 'Show all records from QuoteTemplateProductTable...'. Have textboxes bound to the fields from ProductModelPricingTable. These bound textboxes should not be editable but you might want to set Locked to Yes and TabStop to No. I like to set the backcolor of editable textboxes different from non-editable.

    Why is Quantity field in ProductModelPricingTable?

    QuoteID is primary key in both tables. It might not be necessary to have these tables separate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 02-19-2012, 03:48 PM
  2. Combo box not picking up values basis dlookup
    By amangupts in forum Programming
    Replies: 13
    Last Post: 07-15-2011, 11:03 AM
  3. Incorrect Values Populating My Combo Boxes
    By charlyzaingel in forum Forms
    Replies: 19
    Last Post: 06-23-2011, 10:17 AM
  4. Replies: 16
    Last Post: 08-19-2010, 01:40 PM
  5. How do I get dlookup values to store in table
    By rpmyhero in forum Access
    Replies: 1
    Last Post: 11-25-2009, 05:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums