none
SELECT query generates new records in related tables but does not link foreign key RRS feed

  • Question

  • Hello all,

    I have two related tables with "enforce data integrity" enabled.

    TBL_A
    [PK] LinkingID [AutoNumber]
    FieldA1
    FieldA2

    TBL_B
    [PK] BIndex
    [FK] LinkingID [Long Integer]
    FieldB1
    FieldB2

    QRY_COMBINE  (SELECT query)
    BIndex
    TBL_A.LinkingID
    FieldA1
    FieldA2
    FieldB1
    FieldB2

    QRY_COMBINE is a SELECT query FROM TBL_A LEFT JOIN TBL_B on TBL_A.LinkingID = TBL_B.LinkingID.

    For existing records, QRY_COMBINE correctly shows the combined data from both underlying tables.

    When I try to create a new record using QRY_COMBINE, new records are created in both tables.  On a new record, data entered into the query in FieldA1, FieldA2, FieldB1, FieldB2, are correctly stored to their underlying tables.

    The new record in TBL_A has a newly generated AutoNumber, but the FK LinkingID field of the new record in TBL_B is never populated with the with the newly generated LinkingID of TBL_A.  Instead, the LinkingID field of TBL_B remains blank, and a re-running the query will not display FieldA1, FieldA2 because there is no common LinkingID between the two tables.

    Is there a way to get Access to automatically link the two records on record creation in QRY_COMBINE?

    Thank you for your help.




    • Edited by JackBeQuick Monday, June 12, 2017 4:07 PM Corrected FROM clause
    Friday, June 9, 2017 3:04 PM

All replies

  • Hi, The recommended approach to create child records is through a form/subform setup. Using a form for the parent table and a subform for the child table, whenever you create a child record, Access will automatically assign the foreign key. I'm not sure there is a way to do it using just a SELECT query. You will probably need an UPDATE query as well. Just my 2 cents...
    Friday, June 9, 2017 3:12 PM
  • CREATE FUNCTION [dbo].[FncInsert]
        (
          @FieldA1 NVARCHAR(MAX),
          @FieldA2 NVARCHAR(MAX),
          @FieldB1 NVARCHAR(MAX),
          @FieldB2 NVARCHAR(MAX)
        )
    RETURNS INT
    AS
        begin
             TBL_A Insert values(@FieldA1,@FieldA2);
             TBL_B Insert values(SCOPE_IDENTITY(),@FieldB1,@FieldB2)
    return(1)
        end

    After creating the above function, we can use it with the following code.

    Select FncInsert('Field A1 Value','Field A2 Value','Field B1 Value','Field B2 Value')




    Code is Poetry

    Friday, June 9, 2017 3:33 PM
  • Although the logic may be simplest of you use a form/subform arrangement to enter these records, it should work in a query if you include TBL_B.LinkingID in the selected fields:


    SELECT 
       BIndex, 
       TBL_A.LinkingID, 
       TBL_B.LinkingID, 
       FieldA1, 
       FieldA2, 
       FieldB1, 
       FieldB2
    FROM 
       TBL_A 
    LEFT JOIN 
       TBL_B 
    ON TBL_A.LinkingID = TBL_B.LinkingID;


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, June 9, 2017 4:31 PM
  • Thank you both for your help.

    I cannot use a form+subform because I want my desired interface form to be set to continuous forms.

    I will try using either an UPDATE query or passing both LinkingIDs through the COMBINE query and programmatically (using VBA) setting the non-generated one to match the auto-generated field.

    Thanks again.

    Friday, June 9, 2017 4:41 PM
  • Thank you both for your help.

    I cannot use a form+subform because I want my desired interface form to be set to continuous forms.

    I will try using either an UPDATE query or passing both LinkingIDs through the COMBINE query and programmatically (using VBA) setting the non-generated one to match the auto-generated field.

    Just to make sure you understood, *if* you include TBL_B.LinkingID in your query's selected fields, then it will be automatically updated if you modify both a field from TBL_A and a field from TBL_A in your query results.  You don't need any code to make that happen.

    In fact, you don't even need to include TBL_A.LinkingID in the selected fields; just TBL_B.LinkingID.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, June 9, 2017 5:03 PM
  • Hi Dirk,

    It's good to know. Thanks.

    Friday, June 9, 2017 5:28 PM
  • While it can be done as Dirk describes, I would not recommend inserting rows by means of a query which joins the tables.  It makes it too easy for a user unfmiliar with the way in a a relational database works, to unwittingly edit the value of a column in the referenced table, TBL A, thinking that this will only apply to the current record in the referencing table, TBL B, whereas it will of course apply to all matching rows in the referencing table.You can use a form based on TBL B, which can be in single or continuous forms view as preferred.  In the form include a combo box bound to The foreign key LinkingID column, set ups as follows:

    Name:    cboFieldA1

    ControlSource:    LinkingID

    RowSource:  SELECT LinkingID,FieldA2, FieldA1 FROM [Tbl A] ORDER BY FieldA1

    BoundColumn:     1
    ColumnCount:     3
    CoulmnWidths:    0cm;0cm;8cm

    If your units of measurement are imperial rather than metric, Access will automatically convert the units to inches.  The important thing is that the first two dimensions are zero to hide the columns.

    In the form add a text box with a ControlSource property of:

    =cboFieldA1.Column(1)

    The Column property is zero-based, so this will show the value from the second column, FieldA2, which corresponds to the selected FieldA1 value.

    In the NotInList event procedure of the combo box put:

        Dim ctrl As Control
        Dim strMessage As String
        
        Set ctrl = Me.ActiveControl
        strMessage = "Add " & NewData & " to list?"
        
        If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
            DoCmd.OpenForm "frmA", _
                DataMode:=acFormAdd, _
                WindowMode:=acDialog, _
                OpenArgs:=NewData
            ' ensure frmA closed
            DoCmd.Close acForm, "frmA"
            ' ensure new row has been inserted
            If Not IsNull(DLookup("LinkingID", "[TBL A]", "FieldA1 = """ & _
                NewData & """")) Then
            Response = acDataErrAdded
            Else
                strMessage = NewData & " was not added to TBL A table."
                MsgBox strMessage, vbInformation, "Warning"
                Response = acDataErrContinue
                ctrl.Undo
            End If
        Else
            Response = acDataErrContinue
            ctrl.Undo
        End If

    This enables you to type a new FieldA1 value in to the combo box, and, after user confirmation, insert a new row into TBL A via frmA, which is a form bound to TBL A, and which has the following code in its Open event procedure:

        If Not IsNull(Me.OpenArgs) Then
            Me.FieldA1.DefaultValue = """" & Me.OpenArgs & """"
        End If

    Note that this will not initiate a new record in TBL A until data is entered via the form into FieldA2

    Rather than the above code, for a cheap and cheerful solution, you can simply set the ListItemsEditForm property of the combo box to the name of frmA.  This will not pass the new value entered in the combo box to the new form, however, and the user will have to enter it again into frmA.

    Whichever method is used the user will enter a value manually in frmA in the control bound to FieldA2.

    As well as being opened via the NotInList event procedure or by virtue of being the ListItemsEditForm property of the combo box, frmA can of course be used independently for editing existing data in TBL A.

    Ken Sheridan, Stafford, England


    Friday, June 9, 2017 5:33 PM
  • QRY_COMBINE is a SELECT query FROM TBL_A LEFT JOIN TBL_B on TBL_A.LinkingID = TBL_A.LinkingID.

    Correction: I think most of you have realized my mistake and made the correction by context, but this line was supposed to read "TBL_A.LinkingID = TBL_B.LinkingID."

    This correction applies just to my original post.  The query design was already set up for TBL_A.LinkingID = TBL_B.LinkingID.

    • Edited by JackBeQuick Monday, June 12, 2017 3:45 PM Grammar mistake
    Monday, June 12, 2017 3:44 PM
  • Although the logic may be simplest of you use a form/subform arrangement to enter these records, it should work in a query if you include TBL_B.LinkingID in the selected fields:


    SELECT 
       BIndex, 
       TBL_A.LinkingID, 
       TBL_B.LinkingID, 
       FieldA1, 
       FieldA2, 
       FieldB1, 
       FieldB2
    FROM 
       TBL_A 
    LEFT JOIN 
       TBL_B 
    ON TBL_A.LinkingID = TBL_B.LinkingID;


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Hello Dirk,

    Thank you for your response.  Sorry, I hadn't seen your reply when I made my first reply.

    I have the query set up exactly as you have written above.  I also was expecting this to work but as it does not seem to work as intended.

    With the query set up like above, I can enter a new record in my query and a TBL_A.LinkingID will be automatically generated, but TBL_B.LinkingID will remain blank.  Manually entering a value in TBL_B.LinkingID will throw an error if the manually entered number does not match the next sequential AutoNumber value.

    Monday, June 12, 2017 4:05 PM
  • I have the query set up exactly as you have written above.  I also was expecting this to work but as it does not seem to work as intended.

    With the query set up like above, I can enter a new record in my query and a TBL_A.LinkingID will be automatically generated, but TBL_B.LinkingID will remain blank. 

    That's odd, because it works for me.  When you enter a new record in your query, are you entering values in the fields that come from both TBL_A and TBL_B?  If you only modify the fields from TBL_A, then no TBL_B record will be created, so TBL_B.LinkingID will remain Null.  If you modify only fields from TBL_B, then you must give a value for TBL_B.LinkingID that already exists in TBL_A.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, June 14, 2017 5:25 PM