Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    189

    Unanswered: VBA to fill main form field with subform entry

    Hi and happy holidays!
    Thank you for reading!!

    I am running three related tables to show components of a product. tblProduct PID tblComponent CID tblAssyDetails AID
    Each product can have one, two or three components. I join the CID to the PID in tblAssyDetails like this:
    tblProduct
    PID Pdesc
    1 Table
    2 Chair
    3 Desk

    tblComponent
    CID Cdesc
    1 Top
    2 Leg
    3 Feet
    4 Small Drawer
    5 Large Drawer
    6 Base
    7 Seat

    tblAssyDetails
    AID PID CID
    1 1 1
    2 1 2
    3 1 3
    4 2 7
    5 2 2
    6 2 3
    7 3 1
    8 3 4
    9 3 5

    Querying this info later on building the database, I realized I need to reference CID in tblProducts as well, so now the table looks like this:
    tblProduct
    PID Pdesc CID1 CID2 CID3
    1 Table 1 2 3
    2 Chair 7 2 3
    3 Desk 1 4 5

    When new products are added it is through a main form, the relating components on a subform.
    I am looking for a way to write VBA to take the CID from the subform and fill main form CID1, CID2 and CID3 in order.
    Can anyone suggest a path?

    thanks!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi

    Other people may have different opinions, but I think you had it right the first time, and you reference the CID through the tblAssyDetail table.

    With you new structure you will need to add a new field to the Product table (effectively changing the DB structure/design) for each new component, when you should only add it to the component list via the user interface.

    Information similar to that show for the 'new' tblProduct, I believe, can be achieves with the original structure and a Crosstab query.

    You might consider adding a Quanty field to the Assebly table (a table has 4 legs?) or you could add a leg 4 times. Both can be accomayeted with the Crosstab, but I would suggest the former, with a suitable primary key.

    I also believe you original structure conforms to a correctly Normalized DB design.

    The user interface for a product would normally have the subform based on the Assembly table (linked with the PID) with the component CID selected using a Combo Box based on the component table.

    HTH


    MTB

Posting Permissions

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