Prevent auto save on form

[tmyers]

From what I have read, disabling Access' autosave on a form can only be done via VBA. I don't fully understand the Dirty property, so am unsure just how to make this work.

All I am after is preventing a particular sub-form from saving/updating until a button is clicked. Otherwise I think it could potentially mess with the Before Update I am trying to cobble together.

The particular code I have thrown together so far that I am worried about messing up (by creating a lot of not needed entries) is:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim oCost       As Long
Dim nCost       As Long
Dim oQty        As Long
Dim nQty        As Long
Dim oPrice      As Long
Dim nPrice      As Long
Dim pID         As Long
Dim tID         As Long
Dim oGP         As Long
Dim nGP         As Long
Dim wrk         As DAO.Workspace
Dim db          As DAO.Database
Dim rs          As DAO.Recordset


    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from tblChangeHistory")



    'old value and new entered value variables
    oCost = Me.Price.OldValue
    nCost = Me.Price.Value
    oQty = Me.Quantity.OldValue
    nQty = Me.Quantity.Value
    oPrice = Me.Text28.OldValue
    nPrice = Me.Text28.Value
    oGP = Me.GP.OldValue
    nGP = Me.GP.Value
    
    'forms Project ID
    pID = Me.ProjectID
    
    
    'begin transaction so action can be rolled back if user cancels or process fails
    wrk.BeginTrans
    
        rs.AddNew
            rs("ProjectID") = pjID
            rs("BeforeChangeQty") = oQty
            rs("AfterChangeQty") = nQty
            rs("BeforeChangeCost") = oCost
            rs("AfterChangeCost") = nCost
            rs("BeforeChangePrice") = oPrice
            rs("AfterChangePrice") = nPrice
            rs("BeforeChangeGP") = oGP
            rs("AfterChangeGP") = nGP
            
            
        If MsgBox("Commit changes?", vbYesNo) = vbYes Then
            
            rs.Update
            wrk.CommitTrans
            Set rs = Nothing
            Set db = Nothing
            
        Else
            wrk.Rollback
            MsgBox "Changes have been discarded", vbOKOnly
            Set rs = Nothing
            Set db = Nothing
            
        End If
            
            
    
End Sub

For the record, I just threw this together and have not done any testing just yet.

 

[Minty]

Very very unnecessary. Try

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If MsgBox("Commit changes?", vbYesNo) = vbNo Then
        Me.Undo
        Cancel = True
    End If
    
End Sub

 

[tmyers]

Very very unnecessary. Try

Private Sub Form_BeforeUpdate(Cancel As Integer)
   
    If MsgBox("Commit changes?", vbYesNo) = vbNo Then
        Me.Undo
        Cancel = True
    End If
   
End Sub

Seems simple enough.

Here another part though. Could I commit all the changes at once rather than one at time? Seems like it would be annoying as you are making changes if you need to do quite a few to be prompted each time. Essentially once they trigger the before update, allow them to keep editing then ask to commit all of them at once.

 

[Isaac]

That's what Minty's code already does. The BeforeUpdate event is going to fire ONCE per record when the record saves.

 

[theDBguy]

Seems simple enough.

Here another part though. Could I commit all the changes at once rather than one at time? Seems like it would be annoying as you are making changes if you need to do quite a few to be prompted each time. Essentially once they trigger the before update, allow them to keep editing then ask to commit all of them at once.

Hi. When the BeforeUpdate event fires, ALL changes will be saved to the table. So, I am not sure what you mean by committing all of them at once. How is it that the BeforeUpdate event is firing? Is the user going back and forth between the subform and the main form? Where is this button for saving the data located? On the main form?

 

[plog]

Every day I go to Google news and click an article that takes me to a site I've never been to before and a full screen cookie notification message appears over everything. Instinctually I hit the x as soon as I can to get past it. That is what you are building, a prompt people will instinctually do anything to get past.

Your button will not prevent any mistakes from being made it will just train people to hit 'Save' and then immediately hit the prompt box to get rid of it, there will be no brain activity between the two actions. This method will not have your intended effect.

 

[tmyers]

I apologize. Isaac is correct in that it does work that way. My mind wasn't thinking Access records and was more in Excel mode.

 

[tmyers]

Every day I go to Google news and click an article that takes me to a site I've never been to before and a full screen cookie notification message appears over everything. Instinctually I hit the x as soon as I can to get past it. That is what you are building, a prompt people will instinctually do anything to get past.

Your button will not prevent any mistakes from being made it will just train people to hit 'Save' and then immediately hit the prompt box to get rid of it, there will be no brain activity between the two actions. This method will not have your intended effect.

That is a very fair point.

 

[gemma-the-husky]

We discussed this recently on another thread. Personally, I would not add a save button. It's really hard not to auto-save a record. Users get used to not having to save records in Access. It's like removing a close button from a form. It can be done, but it's rarely a good idea.

 

[Minty]

@plog Makes a really valid point, it's like having a Save button that then opens another prompt to say "Do you really want to save this?"
99.99% of the time the answer is Yes, so you have annoyed 99.99% of the users and added an extra keypress for them.

Your car doesn't ask you twice if you wanted to start the engine or stop it does it?

The only time I ever do this type of double-check is where I'm giving some form of Admin a function that deletes incorrectly entered data irrevocably.

 

[gemma-the-husky]

Seems simple enough.

Here another part though. Could I commit all the changes at once rather than one at time? Seems like it would be annoying as you are making changes if you need to do quite a few to be prompted each time. Essentially once they trigger the before update, allow them to keep editing then ask to commit all of them at once.

They ARE all committed at once. A user edits a record. Until he moves off this record to another record, nothing is saved. He can press <Esc> to undo his latest edit, then press <Esc> again to undo all edits.

It's better to get used to this, than to force users to adopt a different paradigm just for this one programme.

Add a record selector to the form. Record selectors are really useful, even for single forms. (Single bar down the left). They are generally essential for continuous forms. A black arrowhead indicates the record hasn't been edited. A pencil indicates it has. Press <Esc> a couple of times, and the pencil goes back to an arrowhead. Click the pencil and the record saves, and the indicator goes back to an arrowhead. Move to another record, and the same thing happens. No doubt there is a ribbon process to save the record. Record selectors are useful, even on "single" forms, as you can see whether the record is edited (dirty).

You can add validation to check that all important fields are filled in correctly. You can set constraints at the field level to do the same. That will prevent incomplete entries saving.

 

[Isaac]

And I definitely land on the side of explicit Saves. The first thing I do when creating an Access form is get rid of all the crap ways that Access tries to sneaky auto save and nobody would expect (that no website anywhere does), and make sure no save ever occurs other than through the button.

If you're concerned about giving users easier more efficient ways to 'key' records without saving, then you put &Save so that the S is underlined and they can key Alt+S.

I land on the side of, definitely don't let Access auto save - it defies people's expectations, because no other program sites behave like that.

 

[tmyers]

We discussed this recently on another thread. Personally, I would not add a save button. It's really hard not to auto-save a record. Users get used to not having to save records in Access. It's like removing a close button from a form. It can be done, but it's rarely a good idea.

You are 100% correct. This is another case in the long list of cases where I have over thought the whole ordeal.

In a slight follow up, I am having an issue with the code. It for some reason isn't giving the desired results in regards to my variables oGP and nGP. Table field wise, these are numbers that I am displaying as percentages. When I hover over the variables, in my test they both say 0.06, but when the transaction is committed the field in my before/after change does not appear to get updated. I have tried changing them to single, double and variant, but the results are the same.

 

[theDBguy]

@Isaac

...sneaky auto save and nobody would expect (that no website anywhere does),...

This website does. Try this little experiment:

1. Start composing a reply to this thread
2. Watch the Save icon at the top right corner of the reply window
3. Once you see a green dot blink, navigate away from this thread and don't submit your post
4. Once you have viewed a different page, go back to this thread and see if your "draft" reply was saved

 

[tmyers]

@Isaac

This website does. Try this little experiment:

1. Start composing a reply to this thread
2. Watch the Save icon at the top right corner of the reply window
3. Once you see a green dot blink, navigate away from this thread and don't submit your post
4. Once you have viewed a different page, go back to this thread and see if your "draft" reply was saved

I actually just noticed this a few days ago. It is quite sneaky.

 

[Isaac]

@Isaac

This website does. Try this little experiment:

1. Start composing a reply to this thread
2. Watch the Save icon at the top right corner of the reply window
3. Once you see a green dot blink, navigate away from this thread and don't submit your post
4. Once you have viewed a different page, go back to this thread and see if your "draft" reply was saved

Not in the way that I'm referring to. I'm referring to a more permanent save, like Access does to a record when you close it - which is more analogous to if this site posted a thread when you were in a the middle of a draft and closed the window

 

[tmyers]

For the record, the more "complete" code I cobbled together is:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim oCost       As Single
Dim nCost       As Single
Dim oQty        As Long
Dim nQty        As Long
Dim oPrice      As Single
Dim nPrice      As Single
Dim pID         As Long
Dim tID         As Long
Dim oGP         As Variant
Dim nGP         As Variant
Dim tName       As String
Dim wrk         As DAO.Workspace
Dim db          As DAO.Database
Dim rs          As DAO.Recordset


    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from tblChangeHistory")
    Set wrk = DBEngine.Workspaces(0)



    'old value and new entered value variables
    oCost = Me.Price.OldValue
    nCost = Me.Price.Value
    oQty = Me.Quantity.OldValue
    nQty = Me.Quantity.Value
    oPrice = Me.Text28.OldValue
    nPrice = Me.Text28.Value
    oGP = Me.GPTxt.OldValue
    nGP = Me.GPTxt.Value
    tName = Me.TypeName
    
    'forms Project ID
    pID = Forms!ProjectDetailsFrm!ProjectID
    
    'begin transaction so action can be rolled back if user cancels or process fails
    wrk.BeginTrans
    
        rs.AddNew
            rs("ProjectID") = pID
            rs("BeforeChangeQty") = oQty
            rs("AfterChangeQty") = nQty
            rs("BeforeChangeCost") = oCost
            rs("AfterChangeCost") = nCost
            rs("BeforeChangePrice") = oPrice
            rs("AfterChangePrice") = nPrice
            rs("BeforeChangeGP") = oGP
            rs("AfterChangeGP") = nGP
            rs("TypeName") = tName
            
            
        If MsgBox("Commit line changes?", vbYesNo) = vbYes Then
            
            rs.Update
            wrk.CommitTrans
            Set rs = Nothing
            Set db = Nothing
            
        Else
            
            wrk.Rollback
            Me.Undo
            Cancel = True
            
        End If
            
            
    
End Sub

 

[Isaac]

@tmyers
here's my opinion, I would have your confirmation button only if the Save button is your form's Default (i.e., it's the button that immediately takes the press on someone hitting Enter, which is quite likely they'll do by mistake, coming from Excel). Otherwise, I'd skip it.

 

[tmyers]

@tmyers
here's my opinion, I would have your confirmation button only if the Save button is your form's Default (i.e., it's the button that immediately takes the press on someone hitting Enter, which is quite likely they'll do by mistake, coming from Excel). Otherwise, I'd skip it.

Got it. I will ask the few people I am building this for and see if they want that extra line of defense or not, but you all made valid points for getting rid of it.

 

[gemma-the-husky]

@tmyers Glad you got it working.

A couple of observations. I have never once used a transaction with roll back . It's quite a specialised thing. Normal users wouldn't really need it. A clearing bank or investment house might. I wouldn't use single data types. For money values, I would use currency (much the best) or doubles.