CODEWITHSUNDEEP
ms-accessvbams-access-2007
user142914
user142914

Reputation:

Preventing Accidental Data Overwrite in Access 2007

I have a simple Access 2007 database, and I've added a button to the form which toggles the ability to edit the currently displayed record.

By default, I have AllowEdits, AllowAdditions and AllowDeletions set to false. Clicking the 'Edit Mode' button changes this.

I've also added several form events, so that when the displayed record is changed, edit mode is cancelled.

I'm now finding that I'm not able to create new records, as when I click BtnNew (standard Add new record macro attached), I receive the error "You can't go to the specified record".

The VB code I have in the database is as follows, can anyone see what I'm doing wrong, or where to start looking? (My VB/Access knowledge is lacking, but I understand the concepts as I'm a C# developer).

Private Sub BtnEdit_Click()
    If lblEditMode.Caption = "Edit Mode" Then
        Disable
    Else
       Enable
    End If
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    Disable
End Sub

Private Sub Form_Current()
    Disable
End Sub

Private Sub Disable()
    AllowEdits = False
    AllowAdditions = False
    AllowDeletions = False
    BtnNew.Enabled = False
    BtnDelete.Enabled = False
    lblEditMode.Caption = ""
End Sub

Private Sub Enable()
    AllowEdits = True
    AllowAdditions = True
    AllowDeletions = True
    BtnNew.Enabled = True
    BtnDelete.Enabled = True
    lblEditMode.Caption = "Edit Mode"
End Sub

It strikes me that I'm somehow not setting AllowAdditions to True, yet my label clearly displays 'Edit Mode' correctly.

Upvotes: 0

Views: 1812

Answers (1)

Eric Heinold
Eric Heinold

Reputation: 46

Your problem is that when you go to the new record Access sets the AllowAdditions to false via Form_Current which makes staying on the new record invalid (essentially). I'm not sure this is the best solution, but I would set a state variable that is as the form level, then when the new button is selected, set that variable and set the Form_Current to skip the disable function.

Enum ValidStates
    Locked = 0
    Edit = 1
    NewRecord = 2
End Enum

Dim formState As ValidStates

Then for the btnNew, use an [Event Procedure]

Private Sub btnNew_Click()
    On Error GoTo Err_btnNew_Click

    formState = NewRecord
    DoCmd.GotoRecord , , acNewRec

    Exit Sub

Err_btnNew_Click:
    MsgBox Err.Description
End Sub

And in the Form_Current event, add this:

Private Sub Form_Current()
    If formState <> NewRecord Then Disable
    formState = Edit
End Sub

This will allow you to edit the new record. Your Enable and Disable function would also each add a line to set the correct form states:

formState = Locked ' for Disable
formState = Edit ' for Enable

Upvotes: 0

Related Questions