Results 1 to 7 of 7
  1. #1
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86

    Saving record on form only if 'Save' button is pressed

    Hello again,

    My form/attached subform were based on two tables respectively. Whenever I entered into a field on the form, it would automatically save to the source table, as in real-time updating. I wanted to disable this so I used the code snippets down below to remedy this.



    However, this caused an issue where when I moved from the main form to the sub form all the data in the textboxes in the main form would just get wiped completely - everything became blank.

    This issue did not exist before I introduced the code below to my module, it works when it comes to not saving my data until I click the 'Save' button, but I need to figure out why it is wiping my data when I click into the subform.

    Code:
    Private mSaved As Boolean
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If mSaved = False Then
        Cancel = True
        Me.Undo
        Cancel = False
    End If
    End Sub
    
    Private Sub Form_Current()
    mSaved = False
    End Sub
    
    
    Private Sub cmdSave_Click()
    '############
    'Code that checks validity of record, whether or not it is good enough to be passed to database tables goes here
    '############
    mSaved = True
    '############
    'Code programming button to save record/update tables goes here
    '############
    MsgBox "Changes saved!"
    End Sub
    Does anyone have an idea of why this is occurring and what I could add or change to remedy this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I wanted to disable this
    ??

    Can you describe the edit/validation logic you are trying to implement?

    Usual spot for validation/verification before accepting the record is the BeforeUpdate event of Form.

    You do the verification logic
    -if record fails then you cancel (reject the record with error msg)
    -if record is acceptable, you just move to next record (saved automatically by Access)

  3. #3
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    Basically I want to avoid with every ounce of my being incomplete records in the database. I want every single field (they're all important) filled out.

    I already set up a initial screening of this (meaning, making sure the user enters in all the data, and in a specific format). However, there's the issue where if a user only half-fills out a form and exits out of the form, the record will still be saved as is in my table. This is likely due to the forms being sourced by the tables directly rather than just being empty sheets the user scribbles on.

    And so, I set out to force the user to fill out the entire form and click a 'Save' button for any entry to be saved at all (no partials allowed) but to do this I had to find a solution for the auto-updating feature that occurs between the form and the source tables.

    As in, when I fill out field 1 on my form and move to field 2, field 1 in the source table becomes filled in as well, so on for fields 3, 4, 5, ... , n.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by TerraEarth View Post
    I need to figure out why it is wiping my data when I click into the subform.
    Because when you click into the subform, the main form's update events fire. Since the user didn't click on your button, mSaved is false and the before update event cancels the update.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't know if this thread will be helpful:

    https://www.accessforums.net/showthread.php?t=72521
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    It was right under my nose, wasn't it? It did help!

    I adapted the code to my own purposes but this seems to work better than my previous solution since it doesn't have the same issue with the subform transition.
    Quick question, if I may:

    Code:
    If IsNull(Me!frmComboBoxNewDeleteTransferSubform.Form ![TransferDate]) Then
        If MsgBox("Make sure you enter a Transfer Record." & vbCrLf & "This is MANDATORY." & vbCrLf & "Do you really want to exit?", vbYesNo, "Exit Confirm") = vbNo Then
            Me!frmComboBoxNewDeleteTransferSubform.Form![TransferDate].SetFocus
            Cancel = True
        Else
            CurrentDb.Execute "DELETE * FROM tblYourParentTable WHERE PrimaryKeyValue = " & Me.PrimaryKeyField , dbFailOnError
        End If
    End If
    I changed my SQL statement to
    "DELETE * from tblImport;", dbFailOnError
    From
    "CurrentDb.Execute "DELETE * FROM tblImport WHERE tblImport.ContractNum = " & Me.ContractNum & ";" , dbFailOnError
    As I seem to trigger "error 3061: Too few parameters. Expected 1."
    It works fine as the way I changed it, however I am curious as to why the adapted code did not go through.
    Any idea why this error triggered?

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That error is typically caused by a misspelled table or field name in that situation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-13-2017, 08:51 AM
  2. Replies: 6
    Last Post: 11-14-2013, 07:37 AM
  3. Replies: 3
    Last Post: 10-08-2012, 08:40 AM
  4. Replies: 8
    Last Post: 09-27-2012, 11:12 AM
  5. VB coding for saving when click on save button in form
    By cwwaicw311 in forum Programming
    Replies: 1
    Last Post: 02-04-2010, 11:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums