Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subform not saving record (Access 2000 9.0.3821 SR-1)

    I've been playing around with Access for years but I'm stumped on this one.

    I've got a main form and a subform with a one-to-one relationship. I've only got two date fields in the subform that default to today's date and three months later. The problem is that the subform record isn't saved unless I manually edit one of the dates. I can move back and forth between records in the form and the linked records I've just entered will show up in the forms but when I exit and re-enter the form, the subform record isn't saved in its table while the main form record is. If I manually enter a date or change it, though, the subform record is saved and all is well.
    Access is supposed to save a record automatically but apparently it needs me to manually modify the record even though the date I haven't modified or entered is saved.
    Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: subform not saving record (Access 2000 9.0.3821 SR-1)

    Is this new records you're talking about? Move back and forth between parent form records? If you can see associated subform records you've just entered when you move back and forth between parent records, they're being saved. If they don't show up later, I suspect the records aren't properly linked to the parent record. How are you inserting the parent key into the subform record?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform not saving record (Access 2000 9.0.3821 SR-1)

    Yes, new records. I would think they're being saved, too, but, in fact, they are only being saved when I change data in the subform. When I change data in a subform field, the subform record is properly linked to the main form record. I'm using Autonumbering for the primary key of the mainform record and a corresponding unique indexed long integer field for the subform record.

    The form was built from a query. I can enter data through the query.

    I'm not exactly sure how the parent key is being inserted. The data tab of the subform properties shows the correct field values for "link child fields" and the "link master fields" and the auto-ID of the mainform record is displayed as the num field of the subform record on the form. If I change or simply re-enter a date, the correct values are saved. If I simply tab through all the fields in the subform, they are not.

    When I examine the mainform table, I can see the new records, but there are no corresponding records, linked or unlinked, in the subform table. However, when I alter a value in any of the subform's fields, the correct records, properly linked, are in both tables and display properly in the form.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: subform not saving record (Access 2000 9.0.3821 SR-1)

    You're creating a new record in the main form and you want to create a subform record automatically, is that it? I suspect that what you're seeing on the subform is the default values. If you're enforcing cascading updates between the two tables, then it takes some action to actually create the secondary record. Otherwise, the linking key may not get inserted into the record and so it won't be saved. Default values aren't enought to trigger the cascade, apparently, so you may need to use code to insert the parent table primary key into the corresponding field in the subform. You could also try setting the default value of a control bound to the linking field equal to Parent![KeyField], where KeyField is the name of the primary key from the parent table.

    One way to see what's going on is to put a control on your subform bound to the long integer that holds the linking key. I'd do the same for the main form so you can watch what happens. I suspect you'll see that although the default values appear in the fields, the key field never gets filled in unless you force the cascade by entering a value.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: subform not saving record (Access 2000 9.0.3821 SR-1)

    I'm guessing that the values for those 2 date fields are set by the DefaultValue property? This would explain why you see the values but they aren't saved. As far as Access is concerned, you are still sitting on a new record but haven't entered anything yet! So, you have to do something to make the record "dirty". One quick solution would be to set the subform record to .dirty=true from the AfterInsert event of your mainform, then save it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: subform not saving record (Access 2000 9.0.3821 SR-1)

    Thanks! That did it.

Posting Permissions

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