Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add Recs to Subform from Code (2000)

    I am trying to add records to a subform from the Load event of the subform's parent form, but with no success. I get error messages in the bound subform controls and crash Access completely! I am doing this to open a sales order form with a number of detail lines automatically generated. The technique I have tried is to set a DAO recordset object to the recordset of the subform, add the records I want automatically generated to this then requery the subform. Should this work? Am I just doing something silly in the code or is this approach doomed from the start?

    At the moment I am letting Access handle the form/subform relationship but I have just read the section on rolling back subform transactions in the Getz/Gilbert/Litwin 2000 Developer's Handbook and may switch to handling the relationship myself in order to apply their code. Does anyone know if this make my automatic record generation any easier/harder?

    Ian

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add Recs to Subform from Code (2000)

    I would handle adding new records outside of the form. If you're trying to do it in the OnLoad event of the parent now, you might as well add the records first, an then open the form. The new records will be displayed in the subform automatically. Of course, doing it this way, you can't use the subform's recordset, but you can use the table or query that acts as record source instead.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Add Recs to Subform from Code (2000)

    I agree with Hans' approach - the method you are attempting to use is fraught with difficulties, and unless there is an explicit need to roll back transactions based on some external event, the use of transactions adds another layer of complexity. One way we've done this sort of thing is to use an unbound form to drive the process as sort of a wizard. We collect all the info needed for the new main record, ask the user to reconfirm that they really do want to add the record, and then add the main record, the detail records, and then open the form to let them view it. Post back if the concept isn't clear.
    Wendell

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

    Re: Add Recs to Subform from Code (2000)

    Instead of trying to use a transaction, follow Hans' advice and open a recordset on the table to add the records. Forget trying to use the subform's recordset. then in the parent form, test for complete records in parent and child form and simply delete the subform records if the parent form wasn't completed.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add Recs to Subform from Code (2000)

    Thanks to everybody. Everybody seems to think that my approach was the wrong one so now I add records to the underlying tables before the form opens and handle the Cancel request by keeping copies of the 'old' data in holding tables.

    Does anyone have any views on allowing users to open multiple versions of the same order creation form at the same time, so that they can have several orders on the go at once?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add Recs to Subform from Code (2000)

    It *is* possible to open multiple instances of a form, but you have to be aware of a few things:
    <UL><LI>If the user opens five instances of a form, it will occur five times in the Forms collection, but all five items will have the same name!
    <LI>So if you refer to the form in another database object (a query, another form or a report), you don't know to which instance you are referring: Forms!frmMultipleInstance!txtLastName could refer to any of them, since they all have the same name.[/list]Microsoft provides an example in ACC2000: How to Open Multiple Instances of a Form. This article also contains a link to a downloadable sample database.

Posting Permissions

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