Read-Only and undo properties MS Access (VBA)

Question

I have been surfing the wed looking for an answer to what I thought it was a pretty simple question, for a newbie like me.. But havent found any so here I am!. To keep things simple, i'll attach the two bits of my code that are giving me a bit of a headache. What I intend to do is to switch from a Read-Only mode to a Write Allowed state by clicking a button. In order to do so, I want the routine to check for changes on the record and, if there are any ask the user to decide whether to save the changes or discard them.. While testing this particular button, I found out something quite "funny".. It seems to work well when no changes are introduced. Switches perfectly from read-only to write-allow states. However, when in write-allow and changes have been made, if the "Save option" has been selected/clicked then it does not lock the content although all the other subroutines and changes are implemented.

My other problem, closely related to this, is that I cant find a way to set a "saving point" for the undo option. I would like to find a way to "save a record" so when the undo button is pressed doesnt undo all the changes that the record has suffered since the database has been firstly opened (as its currently happening), but since the button has been pressed. I tried the DoCmd Save function but is not behaving as I was expecting (Note: the last 'else' has been my last try to this problem but, again, its not working as expected)

Many thanks for all your future collaboration,

Alex

Private Sub Command28_Click()
    If Form_AODNewRecord.AllowAdditions = True Then
        Call isModified_Save

        Form_AODNewRecord.AllowAdditions = False
        Form_AODNewRecord.AllowEdits = False
        Form_AODNewRecord.AllowDeletions = False
        MsgBox "Read Only. Addition, Edits and Deletions are now locked"
        Command28.Caption = "LOCKED"
    Else
        Form_AODNewRecord.AllowAdditions = True
        Form_AODNewRecord.AllowEdits = True
        Form_AODNewRecord.AllowDeletions = True
        MsgBox "New records, edits and deletions are now allowed"
        Command28.Caption = "Lock mode OFF"
    End If

    MsgBox Form_AODNewRecord.AllowAdditions
End Sub

Sub isModified_Save()
    If Form_AODNewRecord.Dirty Then
        Dim strMsg As String, strTitle As String
        strMsg = "You have edited this record. Do you want to save the changes?"
        strTitle = "Save Record?"
        If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
            Me.Undo
        Else
            DoCmd.OpenTable "AOD Type", acViewPreview, acReadOnly
            DoCmd.Save acTable, "AOD Type"
            DoCmd.Close acTable, "AOD Type", acSaveYes

        End If
    End If

End Sub

Answer 1

I think you want the following code. You cannot open a table that the form is bound to and affect the form, when you open the table, you have two different instances and they do not affect one another.

An odd side effect of running a macro in Office applications is that it clears the Undo list, so I created a small macro that just shows a message "Undo cleared" and that is exactly what it does!

As an aside, rename your control with meaningful names, such as cmdLock, rather than Command28, you will thank yourself later.

Private Sub Command28_Click()
    If Me.AllowAdditions = True Then
        Call isModified_Save

        Me.AllowAdditions = False
        Me.AllowEdits = False
        Me.AllowDeletions = False
        MsgBox "Read Only. Addition, Edits and Deletions are now locked"
        Command28.Caption = "LOCKED"
    Else
        Me.AllowAdditions = True
        Me.AllowEdits = True
        Me.AllowDeletions = True
        MsgBox "New records, edits and deletions are now allowed"
        Command28.Caption = "Lock mode OFF"
    End If

    ''MsgBox Me.AllowAdditions
End Sub

Sub isModified_Save()
    If Me.Dirty Then
        Dim strMsg As String, strTitle As String
        strMsg = "You have edited this record. Do you want to save the changes?"
        strTitle = "Save Record?"
        If MsgBox(strMsg, vbQuestion + vbYesNo, strTitle) = vbNo Then
            Me.Undo
        Else
            ''Save record
            Me.Dirty = False
            ''Clear undo list
            DoCmd.RunMacro "ClearUndo"
        End If
    End If

End Sub