Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2010
    Posts
    12

    Unanswered: Subform data not saved on Form close

    Hi all! I seem to have a bizarre problem that I just cannot find the answer to!

    I've got a form with two subforms in it. I enter data in the form as well as the subforms but when i close the main form, the data in the subforms is not saved! If I navigate between records after entering the data, everything in the subforms is saved, but ultimately the user will not have access to record navigation so I want the subforms as well as the main form to keep the entered data on close.

    I've tried saving the record using:
    "DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70"

    as well as trying "me.refresh" but neither retains the subform data.

    Does anyone know what I might try in order to get this to work properly? Thanks!

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by intergnat1 View Post
    Hi all! I seem to have a bizarre problem that I just cannot find the answer to!

    I've got a form with two subforms in it. I enter data in the form as well as the subforms but when i close the main form, the data in the subforms is not saved! If I navigate between records after entering the data, everything in the subforms is saved, but ultimately the user will not have access to record navigation so I want the subforms as well as the main form to keep the entered data on close.

    I've tried saving the record using:
    "DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70"

    as well as trying "me.refresh" but neither retains the subform data.

    Does anyone know what I might try in order to get this to work properly? Thanks!
    How are you closing the form?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Apr 2010
    Posts
    12
    I'm using the X button on the top right of the window but I'm going to be placing an "exit" command button on the main form.

  4. #4
    Join Date
    Apr 2010
    Posts
    12
    Does anyone have any ideas on how I can solve this? Can I provide any additional information? Thanks!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try to save the data in the subform by using the following code in the subform class module:
    Code:
    Private Sub Form_Close()
    
        If Me.Dirty = True Then Me.Dirty = False
        
    End Sub
    Have a nice day!

  6. #6
    Join Date
    Apr 2010
    Posts
    12
    Hi Sinndho! I tried putting the code behind a command button in a subform that recalculates some of the values in the subform's text boxes so it should be changing dirty to false each time i hit the button to recalc. Unfortunately it still doesn't seem to retain the information from the subform when I hit the command button.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nothing was said about using this code to respond to the Click event of a command button performing "recalculations" before. Changing the value of controls using VBA code does not automatically change the Dirty property of a form. Did you try to use the code on the Close event of the subform?
    Have a nice day!

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Is the default value for the field which links the 2 tables (the one on your main form and the one on your subform), is the default value of this field on your subform something like: =Forms!MyMainForm!MyLinkingFieldName.

    This would then 'create' the value needed for new records in the subform to make sure it relates to the correct ID type field on the main form.

    You should never really need to issue any kind of 'saverecord' command.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Apr 2010
    Posts
    12
    Quote Originally Posted by Sinndho View Post
    Nothing was said about using this code to respond to the Click event of a command button performing "recalculations" before. Changing the value of controls using VBA code does not automatically change the Dirty property of a form. Did you try to use the code on the Close event of the subform?
    Hi Sinndho,
    I tried changing dirty to false on both the subform close event as well as the mainform close event. Unfortunately neither worked. I apologize if I didn't provide enough information for you before. I started working on this database with absolute zero training in Access so I might not understand all of the in's and out's yet.

    Quote Originally Posted by pkstormy View Post
    Is the default value for the field which links the 2 tables (the one on your main form and the one on your subform), is the default value of this field on your subform something like: =Forms!MyMainForm!MyLinkingFieldName.

    This would then 'create' the value needed for new records in the subform to make sure it relates to the correct ID type field on the main form.

    You should never really need to issue any kind of 'saverecord' command.
    Thanks for the tip PKStormy. Right now the linking field value is created using this code behind the form_current event on the subform:

    If Me.InvoiceID = 0 Then
    intResponse = MsgBox("Create New Invoice?", vbYesNo, "New Invoice")
    If intResponse = 6 Then
    Me.OrderDate = Date 'auto-insert current date in new invoice
    intInvoiceTemp = DMax("InvoiceID", "Invoices") 'auto-increment invoice number in new invoice
    intInvoiceTemp = intInvoiceTemp + 1
    Me.InvoiceID = intInvoiceTemp
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    End If
    End If

    I need the field to auto-increment an invoice number on new record and the default value in the subfield always is 0 so I figured I would handle the problem this way. All of the above code is within the subform, not the mainform and InvoiceID is the linking field between the main and subform. Should I be handling this differently?

Posting Permissions

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