none
New records in subform, master link field left blank

    Question

  • Hi,

    This issue is quite erratic.  I have a form & subform in which deliveries are entered.

    The Header data (Date, Delivery ID, Ship-to-address, etc.) is all stored in textboxes and combo boxes on the main form.  The main form's default view is "Single Form".

    All the shipment lines are entered in the subform (product ID, quantity, etc.).  The subform's default view is "Continuous Forms".

    The links are as follows:
    Link Master Fields = ID
    Link Child Fields = Header_ID

    The back-end is SQL, using linked tables.  What is happening is the record is being committed by the Header_ID is not being populated with the value of the ID field of the main form, i.e. it is left blank.  I have checked in the DB and found that I had accidentally left this as "Allow Nulls".  I have turned this off to prevent this from happening in the future, but can anyone explain why it might be happening?

    Thanks,

    Charles


    Tuesday, November 06, 2012 11:27 AM

Answers

  • Access 2010 running on local desktop(s)
    SQL Server 2008 R2 x64, running on server on LAN

    I don't see a problem there.

    There aren't any timestamp fields in the tables.  What would they do, should I create them?

    Under some circumstances, Access needs the linked SQL table to contain a timestamp (or rowversion) field in order to make it updatable.  I don't know if this is relevant in your case, because you say it works most of the time, and the records do get added.  When I've encountered this in the past, the table couldn't be updated at all until I added the timestamp field.  But you could try adding a timestamp/rowversion field to see if it makes a difference. If you do, I suggest you delete the linked table in Access and relink it.

    I believe I did specify an identity when linking all back-end tables.  Perhaps I should delete and recreate the link...?

    That might be a good idea, too.  When you link an ODBC table, Access stores some information about how to manage it.  It couldn't hurt to make sure Access has the latest, correct information.

    I just thought of another question worth asking:  Does the SQL table have any triggers affecting it?


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



    Wednesday, November 07, 2012 4:50 PM

All replies

  • I think you have left also behind the auto increment. If you don't set to auto increment then the ID will not be generated by the DB. IMPORTANT never allow null values on your ID column/field.

    In this case you need to DROP the table and CREATE with your PK

    Something like this...

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[your table name]') AND type in (N'U'))
    DROP TABLE [dbo].[your table name]
    GO
    
    USE [your db name]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[your table name](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[field1] [int] NULL
     CONSTRAINT [PK_your table name] PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO

    BUT WARNING!!!... save you data BEFORE. Or simple create another table with a PK(PRIMARY KEY) and IDENTITY(1,1).

    Hope that helps..


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Tuesday, November 06, 2012 2:01 PM
  • Hi scorpdevil,

    I believe I did not explain myself too well.  The schema is like this (watered down version to keep things simple):
    ----------------------------------------------------------------
    tblDeliveries_Header
        ID (int, identity = yes, not null)

    tblDeliviers_Details
       ID (int, identity = yes, not null)
       Header_ID (int, identity = no, allow null, not unique)

    FK constraint = "[tblDeliveries_Detail] CONSTRAINT FK_tblDeliveries_Detail_tblDeliveries_Header] FOREIGN KEY([Header_ID]) REFERENCES [tblDeliveries_Header]([ID])"
    ----------------------------------------------------------------

    What I have now done is set [tblDeliviers_Details].[Header_ID] to "not null". I am expecting this to stop the problem of orphaned records, but I don't understand why Access is not populating the Header_ID field on creation of a new record in tblDeliveries_Details?

    The ID column for both tables is being auto-incremented and is working well.  It is the referencing column ([tblDeliveries_Header].[Header_ID]), which cannot be an identity column, that is being left blank.

    Thanks,

    Charles
    Tuesday, November 06, 2012 3:23 PM
  • How are you inserting the data?


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Tuesday, November 06, 2012 3:59 PM
  • Perhaps I do not understand the question, but it is being inserted using Access, form with subform, standard method.  Access is supposeed to fill the Header_ID field of tblDeliveries based on:
    Link Master Fields = ID
    Link Child Fields = Header_ID

    Please see first post.

    Thanks.


    Tuesday, November 06, 2012 4:06 PM
  • Example:

    If I understand correct, you have two bound forms where main form is bound to tblDeliveries_Header and your subform is bound to tbl_Deliviers_Details.

    In order for your configuration to work Access has to insert data into tblDeliveries_Header then get the SCOPE_IDENTITY(), Then Insert Into tbl_Deliviers_Details the needed data + the tbl_Deliveries_Header ID ak...(SCOPE_IDENTITY())...

    Now In order to do that Access must use a Macro, VBA Code, Pass-Trough Query or it's internal DAO.Recordset; all thru a ODBC String Connection to the BE SQLServer.

    Now, How are you inserting the data?

    Using your own VBA Code? or Triggering a Macro?...or you just inserting data thru your bound forms?

    SD


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Tuesday, November 06, 2012 4:34 PM
  • Perhaps I do not understand the question, but it is being inserted using Access, form with subform, standard method.  Access is supposeed to fill the Header_ID field of tblDeliveries based on:
    Link Master Fields = ID
    Link Child Fields = Header_ID

    Please see first post.

    Yes, it should work.  If both the main form and the subform are bound, and the Link Master/Child Fields are specified correctly, Access should be handling the insertion of the Link Master Field's value into the Link Child Field.

    What versions of Access and SQL Server are involved here?

    Do the SQL Server tables have timestamp (or rowversion) fields?

    When you linked to the back-end tables, did you specify an identity field?


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

    Tuesday, November 06, 2012 5:02 PM
  • Hi SD,

    Like I say, using the standard method.  Entering through bound forms.

    I am not using any VBA code, macros or anything like that.  This is why I don't get why it is failing.  The only non standard thing I am doing is linking to SQL tables on the backend rather than using local Access tables.

    I am guessing that the problem of orphaned records is down to me having originally set the [tblDeliveries_Details].[Header_ID] field to "allow nulls".  What must have happened is the record was committed before / while Access was retrieving and updating the value of the Header_ID field.  Access didn't realise that it hadn't been updated, or if it did it didn't worry about it, and SQL let the record be committed because Header_ID allowed nulls.

    I cannot think of another reason.  I am not expecting any more orphaned records since changing Header_ID to "not null", but I am expecting users to get SQL error messages due to null values in Header_ID.  I would like to avoid this as well.

    What I can do is write some VBA in the Before_Insert event of the subform to check if the Header_ID textbox (not visible) is null, and if so to get the correct value and insert it.  I think that might work, I haven't tried it yet.  However, I would prefer to fix the bug with a proper solution rather than band-aid it with VBA code.  That just feels neater.

    Thanks,

    Charles

    Tuesday, November 06, 2012 5:12 PM
  • Hi Charles,

    Thanks for your clarification. I just wanted to know exactly your set up. (some people like to use vba to insert date even when forms are bound) don't ask me why!! :)

    Anyways, I did recreated your set up and I'm not getting errors.

    Access should be handling that automatically for you. (Access 2010 & SQL2008)

    I can't think of anything else. Double check your Master/Child TextBoxes

    Hope that helps...

    SD


    The computing scientist's main challenge is not to get confused by the complexities of his own making.

    Tuesday, November 06, 2012 5:38 PM
  • Thanks SD.  Yeah it is super eratic - it works 99% of the time.  That is why I am thinking some temporary loss of connectivity to the SQL DB (hosted on a server on the LAN) at the time of inserting the Header_ID field, and Access has no way of knowing that the field was not updated.  Or if it does know, no way of rectifying it.

    I have checked the Master/Child textboxes, but if there was something wrong it ought to be permanent, not only occuring occasionally.

    Well I have made the field a mandatory one now, which I should have done from the outset, so at least I won't get orphaned records.  Who knows maybe that somehow miraculously makes the issue go away entirely.  Here's hoping!

    Many thanks,

    Charles
    Wednesday, November 07, 2012 9:32 AM
  • Hi Dirk,

    Sorry I totally missed your post.

    Access 2010 running on local desktop(s)
    SQL Server 2008 R2 x64, running on server on LAN

    There aren't any timestamp fields in the tables.  What would they do, should I create them?

    I believe I did specify an identity when linking all back-end tables.  Perhaps I should delete and recreate the link...?

    Thanks,

    Charles
    Wednesday, November 07, 2012 9:36 AM
  • Access 2010 running on local desktop(s)
    SQL Server 2008 R2 x64, running on server on LAN

    I don't see a problem there.

    There aren't any timestamp fields in the tables.  What would they do, should I create them?

    Under some circumstances, Access needs the linked SQL table to contain a timestamp (or rowversion) field in order to make it updatable.  I don't know if this is relevant in your case, because you say it works most of the time, and the records do get added.  When I've encountered this in the past, the table couldn't be updated at all until I added the timestamp field.  But you could try adding a timestamp/rowversion field to see if it makes a difference. If you do, I suggest you delete the linked table in Access and relink it.

    I believe I did specify an identity when linking all back-end tables.  Perhaps I should delete and recreate the link...?

    That might be a good idea, too.  When you link an ODBC table, Access stores some information about how to manage it.  It couldn't hurt to make sure Access has the latest, correct information.

    I just thought of another question worth asking:  Does the SQL table have any triggers affecting it?


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



    Wednesday, November 07, 2012 4:50 PM
  • Thanks Dirk,

    I have deleted the the link and recreated it.  Let's see how we get on now.  There were a lot of changes to this table during development, perhaps this has caused a gremlin to sneak in.

    The table does not have any triggers.

    Regards,

    Charles
    Thursday, November 08, 2012 9:44 AM