Results 1 to 5 of 5
  1. #1
    equestrian is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    15

    Append Query

    I am using Access 2007

    I have a database that I am working on that will build a unit with a serial number and keep track of what parts were used to build the unit. Each unit has a Bill of Material list with parts that are mandatory or discretionary.


    tblBOM
    UnitNum_FK
    PartNum_FK
    BOMQty
    ReplacementType_FK (if =1 mandatory, if=2 discretionary)
    ReplacementNote

    tblUnitAssy
    UnitAssyNum_PK
    UnitNum_FK
    SerialNum
    UnitAssyDate
    UnitAssyQty

    tblPartAssy
    PartAssyNum_PK
    PartNum_FK
    PartAssyQty

    I want to setup a form with a subform. The form will update tblUnitAssy. I have a query to use as the record source for the form:

    SELECT tblUnitAssy.UnitAssyNum_PK, tblUnitAssy.UnitNum_FK, tblUnitAssy.SerialNum, tblUnitAssy.UnitAssyDate, tblUnitAssy.UnitAssyQty
    FROM tblUnitAssy;

    I have a query to use for the subform:

    SELECT tblBOM.UnitNum_FK, tblBOM.PartNum_FK, tblBOM.BOMQty, tblBOM.ReplacementType_FK, tblBOM.ReplacementNote
    FROM tblBOM
    ORDER BY tblBOM.ReplacementType_FK;

    The link between the forms will be the tblUnitAssy.UnitNum_FK=tblBOM.UnitNum_FK

    The user will either leave the tblBOM.BOMQty as is or change it depending on the parts used. If the part was not replaced they will enter 0. The problem I am having is that I do not want the tblBOM updated I want the tblPartAssy updated. I was thinking of putting a button on the form. When that button if clicked the tblPartAssy is updated. I think I would need to do an Append Query to insert rows. Just not sure exactly how to set this up.



    Any thoughts on how I can get started?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am confused.... at first I thought you had a typo in table tblBOM because the first field has a "_FK" suffix. But the more I look at your post, I think you also have two more tables.
    Maybe the table structure is something like this???
    Click image for larger version. 

Name:	equestrian.jpg 
Views:	19 
Size:	35.1 KB 
ID:	28361

    If this is right, you cannot (at least I have never seen) two "many" tables linked in a Main/sub form arraignment. The main form would be based on "tblUnits" (the one) and the sub form would be based on "tblUnitAssy" (the many).

  3. #3
    equestrian is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    15
    You are correct on how the tables are structured. Maybe I need to fix the structure. I know I need the following

    tblUnits
    UnitNum_PK
    Unit

    tblParts
    ParNum_PK
    Part

    I need a way to show what parts should be used in a unit when it is built. This is the BOM
    Example
    UnitA
    Part123 qty 3 Mandatory
    Part456 qty 2 Discretionary
    Part789 qty 1 Mandatory

    I also need a way to show when a unit is built and what parts went into that unit
    So we build UnitA, give it a serial number, and use the following parts
    Part123 qty 3
    Part456 qty 1
    Part789 qty 1

    Currently I have
    tblBOM
    UnitNum_FK
    PartNum_FK
    BOMQty
    ReplacementType_FK (if =1 mandatory, if=2 discretionary)
    ReplacementNote

    tblUnitAssy
    UnitAssyNum_PK
    UnitNum_FK
    SerialNum
    UnitAssyDate
    UnitAssyQty

    tblPartAssy
    PartAssyNum_PK
    PartNum_FK
    PartAssyQty

    Maybe I should split tblBOM into 2 tables with a PK joining the two?

    Maybe add UnitAssyNum_FK to tblPartAssy to join the 2 tables?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ssanfu View Post
    If this is right, you cannot (at least I have never seen) two "many" tables linked in a Main/sub form arraignment.
    Actually you can. I have used such design once :-)

    I had an unbound form with 2 continous forms on it. OnCurrent event of 1st form did write active ID from 1st Form to unbound control on unbound form. The 2nd form was linked to this unbound control.

  5. #5
    equestrian is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Sep 2015
    Posts
    15
    I'm not extremely experienced with Access, but I have noticed that when my structure is set up correctly, the queries, forms, etc come pretty naturally. I really feel like I have a structural problem I am going to go back and look at this. I have posted a thread in the Database Structure section. I think that is probably a more fitting subject. Thank you for your help.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-02-2015, 08:44 AM
  2. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  3. Replies: 1
    Last Post: 10-06-2011, 08:37 AM
  4. Replies: 7
    Last Post: 07-21-2011, 01:01 PM
  5. Append query won't append
    By yelkenli in forum Queries
    Replies: 5
    Last Post: 02-12-2010, 11:19 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