Saving a record only via a save button (1 Viewer)

[Danners]

Hi all,

I know a couple of users have had this same issue, and I have had a look around many of the posts on the issue and tested some of the code but I cant get it to work properly. Please bear with me, I'm fairly new and self taught to this stuff so it has mainly been trial and error.

I have an access database with a bound form. The form is used purely for data entry purposes and currently is set up so that when the user selects a button called save and submit it processes the record and moves the form on to the next blank record (I have removed normal navigation buttons)

Code I currently have in the database is as follows:

Option Compare Database

Private Sub cboSubGroup_Change()
Me.txtCost_centre.Value = Me.cboSubGroup.Column(1)
Me.txtInternal_Order_No.Value = Me.cboSubGroup.Column(2)
Me.txtStatistical_Internal_Order.Value = Me.cboSubGroup.Column(3)


End Sub

Private Sub SaveCommand_Click()
DoCmd.GoToRecord , , acNewRec

End Sub


What I now want to do is make it so that the records will not save unless the save and submit button is clicked, so for instance if the user starts accidentally typing a record and then closes the database the record will not save. There are lots of users who expect it to work in the way of excel etc where if you close it will not save changes if you have not clicked save – even though this is not how access works.

So when the user goes to close the database or form I would like to pop up a warning (only if the save button has not been clicked) saying “would you like to discard changes?” Yes or No. If the user selects Yes it will then wipe the database and allow them to close it. If the user selects No then it should not close the database but leave the record open to edit and save. If the save button has been clicked and a new record has been opened with no changes i.e. the form is not dirty, and the user wants to close the database then it should override the warning and just close as normal.

I know this is possible using the Before Update event in the form, I just can’t get the right combination of code to be able to function properly.

Any help at all would be much appreciated.

 

[JHB]

Have you tried the "Me.Undo" command in combination with the Me.Dirty?
Else explain what exactly you can't get to work properly!

 

[Danners]

Hi, thanks for responding.

I have had the me.undo command in various iterations of code I have tried, but not sure where to combine the me.dirty part. As I say I am new to all this, and don't fully understand what I am writing.

If you could provide an example of what that combination would look like that would be helpful?

I have tried combining a boolean command with my save button and following on with a BeforeUpdate event combined with MsgBox leading to a me.undo but this just does nothing - I'm probably writing it all wrong.

 

[arnelgp]

put this in your form's behind code:

make sure to replace the "cmdSave" with correct button name of your command save.

Private blnGood As Boolean
 
Private Sub cmdSave_Click()
    blnGood = True
   docmd.GoToRecord ,,acNewRec
    blnGood = False
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
 
    If Not blnGood Then
        strMsg = "Would you like to discard changes?"
        If MsgBox(strMsg, vbYesNo + vbQuestion, "Confirm") = vbYes Then
            Me.Undo
        Else
            Cancel = True
        End If
    End If
End Sub

 

[Danners]

Hi arnelgp,

Thank you for this.

It works for discarding the changes when I close the form or database. However this code also brings up the 'discard changes?' message when I click the save button, which I don't want it to do - If the user clicks the save button I just want it to do this and navigate to the next record.

I only want the error message to display if the form is dirty but the button has not been pressed.

 

[arnelgp]

refer to my previous post, especially the second line.

 

[Danners]

Yes I did that and changed it to my command button name - however this is not working and will not allow me to save and move onto the next record

 

[arnelgp]

can you post your db.

 

[Danners]

I have managed to fix it after a bit of fiddling, I had my Private blnGood As Boolean line
at the wrong part of my code - I didn't realise it had to be above everything including the bit of code I have for pulling data from another table.

Thank you for your help - it's much appreciated