Preventing record deletion in Access through VBA

My current system has a business rule that students cannot be deleted.  They might age-out, die, move out of state, whatever.  We keep the record.  They’re no longer available for viewing but the information is retained for auditing and study.  On the SQL Server side, where the data is stored, no one has delete permissions on any table to maintain the validity of the audits.  I wanted to provide a message to any user trying to delete a student along with a very lurid color notification.

Canceling the Delete action is the simple ‘cancel = True’ line, that’s all there is to it.  We then save the current color of the form header and detail to a long variable, set the colors to green, pop up the messagebox, then return the colors to normal.  If you have a form footer, obviously you’ll need a line for that if you want to twist the entire screen.

Private Sub Form_Delete(cancel As Integer)
    Dim headercolor As Long
    cancel = True
    headercolor = Me.FormHeader.BackColor
    Me.FormHeader.BackColor = RGB(0, 255, 0)
    Me.Detail.BackColor = RGB(0, 255, 0)
    MsgBox "You cannot delete students from this system."
    Me.FormHeader.BackColor = headercolor
    Me.Detail.BackColor = headercolor
End Sub

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s