Detect If Current Record is Deleted on Access Bound Form

Question

If user #1 deletes a record in our Access 2007 database (SQL Server ODBC Linked Tables), other users will likely show #Deleted# in that record (on Datasheet View form). On some occasions this can cause problems because I have code that runs on the Form's current event and simply assumes that a valid record (or new record) exists.

Is there any reliable way to detect if the current record has been deleted? I'd like to wrap it in a function like this:

Public Function IsRecordDeleted(ByRef r As DAO.Recordset)
   'what goes here?
End Function

I did find a RecordStatus property in the DAO reference at MSDN but that property appears to only be valid if the RecordSet is configured for batch updating. When I try to check it's value I get: Run-time error 3251 - Operation is not supported for this type of object.

I actually posted on another forum about this same problem a year or two ago. The recommendations given do not work:

• DCount()
• Bookmark and NoMatch of recordset
• Checking for #Deleted in the PK or FK field

Answer 1

You could check a field which is not blank for sure if the record still exists.

I use a date updated or date created field which is autofilled by the default value =Now() once the form is opened for the first time.

Since its value seems to change to an empty string if the record has been deleted, I can trigger

If Me.DateUpdated = "" Then

on the form itself (e.g. to skip actions in the closing procedure). This check might also work from other forms.

Answer 2

I ran into the same thing via code in the Form_Current event, and ended up here.

Error handling is the best (and only?) way to go here. For my own purposes, I was just running a few simple control updates in the current event, and so I wrote the error handler to recover from the errors I was getting

Private Sub Form_Current()
    On Error GoTo handler

    'do intended things here, e.g. attempt to access the value
    'of a control even though the record may be deleted  

    Exit Sub

  handler:
    If Err.Number = 438 Or Err.Number = 13 Then 
        'type mismatch, probably due to deleted record, skip it
        Resume Next
    ElseIf Err.Number = 3167 Then 
        'record is deleted, skip it
        Resume Next
    Else
        Err.Raise Err.Number, Err.Source, Err.Description
    End If
End Sub

I was not reliably getting 3167 (record deleted), but when attempting to access value properties I seemed to be getting just the 3 errors trapped above.

If your current event is longer, or if it's risky to run over code like this, you could use a similar technique but deliberately provoke the error at the beginning the method (dummyvalue = SomeBoundControl.Value should do the trick) and then change the "Resume Next"s in the error handler to "Exit Sub".

Answer 3

To me, this looks more like a general application design oversight than a simple problem that has a simple work-around.

If you make assumptions on the availability of records in important places, then you must take into account the possibility of deleted records as a major (and valid) state of your data.

It's possible -but I think unlikely- that you will find a workaround, but I think you should take a closer look at your overall design itself.

Some ways to tackle the issue, that may or may not be useful in your case, depending on how your data is contained and accessed by users:

• Simply do not rely on the existence of records if you can't be sure they will be there.
  Basically, rework your assumptions and modify your existing code to avoid relying on the mandatory existence of records.

• Separation of responsibilities
  Don't allow different users to have the same create/edit/delete rights to the same data. For instance, a Purchase Orders should belong to the user who created it. Don't allow other users to delete that order or its items separately.

• Don't actually delete the entity, just allow the user to mark it as 'Cancelled' or 'Obsolete' and keep it in the database for historical reasons (or purge them later).

• Alternatively, don't actually delete records, but add a hidden boolean field to mark them as 'deleted' when the user wants to remove them. You can then do some clean-up, say every night, and actually remove the marked records.
  Of course, you will have to exclude these 'deleted' records from your queries and forms, etc, but data integrity will be preserved.

• Make reports and lists of records read-only so users can't just delete them anywhere. For instance, if users can view Purchase Order items, don't allow them to remove that data except when they actually open the Purchase order details form.

• Cache records locally so that if they disappear from the backend database, they would still be shown to the user viewing them until they refresh the list.
  This is often useful for read-only reports: load the result of a query into a local table and bind that table instead of the query using live data.
  The data being viewed may be slightly stale because it won't update in real-time (since it's cached locally) but for reports, it's generally ok (just provide a 'Refresh' button on your form to allow users to force the refresh).

• Experiment with the various locking options (see database options and form record locking) to disallow deletion of records if someone else is accessing them.

• Manage your own locking scheme.
  As a last resort, you could record in a 'LockingStatus' table the ID and records that are currently being viewed or edited by someone else. For instance:

  Table: LockStatus
  Field: LockNature:   Whether the record is being Edited or Viewed
  Field: LockedTable:  Name of the table of the record being locked
  Field: LockedRecord: ID of the record being locked
  Field: User:         Name of the user holding the lock
  Field: LockTime:     DateTime of the lock creation, so you can detect 
                       and remove stale locks
  

  When a user is viewing or editing a record, first check if there is an existing entry in the table for that record. If there is one, then tell the user they can't perform the operation because someone else is viewing the data. If there is no existing entry, add one, allow edits, and remove the record once the edit is complete.
  This is fraught with complexities since you need to track when a user is moving to another record so you can unlock the previous one, and if you're not careful, you may end-up with lots of stale locks, but it's been done before.

• If you still really want to circumvent the deleted record issue, have a look where, in your VBA code, the Error 3167 "Record Deleted" occurs when you delete a record from another location.
  Once you know where it occurs in your code, trap that error using On Error 3167 Goto YourErrHandler to handle that particular error gracefully (that will really depend on how your forms are designed and how you use your data).

• Another alternative is to use a global error handler for access.
  I know only of vbWatchdog. It's not free but it works extremely well and is very simple to integrate into an application.
  This add-in integrates in your application and doesn't need to be installed separately for each user. Once setup, your application will be able to catch all errors at a high-level. So you will be able to catch 'Record Deleted' errors and deal with them in one place.