Check if link in a table record is deleted (Error 3167)

[jbrumbau]

Hello,

I have a function that goes through each field in a form and checks if it was changed between itself and the existing recordset. It is used to track changes done in any record when the logged in user (Forms!frmLogin !cboUname.Colum n(4)) is logged in.

The problem I have is when my control loop checks a field that is linked to a table with a deleted record, the function crashes. This situation arises when two forms are opened at the same time and the user deletes a record from one form while the other is open. How do I check if a field says "#Deleted" and then skip it?

Here is my code for easy reference, it crashes on the line

 ElseIf frm.Controls(ctrlSource) = rsexist.Fields(ctrlSource) Then

...

Function fillLastUpdated(ByRef frm As Form, tableName As String, uniqueCtl As TextBox, Optional undoChanges As Boolean = False) As Boolean
    If frm.Dirty And Not frm.NewRecord Then    'We don't want to insert this stuff into a new record in case the user doesn't want to save changes
        Dim rsexist As Recordset    'rsexist is the existing record set while the current record is from form frm
        Dim ctl As Control
        Dim keyname As String, keyValue As String, ctrlSource As String 
        Dim changes As String
        Dim fieldChanged As Boolean, messageShown As Boolean
        
        Set rsexist = CurrentDb.OpenRecordset(tableName, dbReadOnly)
        keyname = uniqueCtl.ControlSource
        keyValue = uniqueCtl.Value 'rscurr.Fields(valueSearch)
        fieldChanged = False
        messageShown = False
        changes = ""
        If Not rsexist.nomatch Then 'Else If we don't have a match, then the unique identifier was changed and we have an updated record
            For Each ctl In frm.Controls
                If HasProperty(ctl, "ControlSource") Then
                    ctrlSource = ctl.ControlSource
                    If fieldExists(ctrlSource, rsexist) Then    'Sometimes you have a control that is part of another table
                        If IsNull(frm.Controls(ctrlSource)) And IsNull(rsexist.Fields(ctrlSource)) Then '2 Null strings do not pass equal check
                        ElseIf frm.Controls(ctrlSource) = rsexist.Fields(ctrlSource) Then
                        ElseIf InStr(1, ctrlSource, "LastUpdated", vbTextCompare) Then  'Changes in updated boxes shouldn't be tracked
                        Else
                            If undoChanges Then
                                If messageShown Then
                                Else
                                    Call MsgBox("This item is procured and cannot be edited. Undoing all changes.", vbExclamation, "Record is Locked")
                                    messageShown = True
                                    frm.Undo    'Had to use generic undo since individual field fixing doesn't update the display
                                    Exit For    'Had to use generic undo since individual field fixing doesn't update the display
                                End If
                            Else
                                If hasLabel(ctl) Then
                                    changes = "[" & ctl.Controls(0).Caption & "]=" & rsexist.Fields(ctrlSource) & " -> " & frm.Controls(ctrlSource) & "; " & changes
                                Else
                                    changes = "[" & ctrlSource & "]=" & rsexist.Fields(ctrlSource) & " -> " & frm.Controls(ctrlSource) & "; " & changes
                                End If
                                fieldChanged = True
                            End If
                        End If
                    End If
                End If
            Next ctl
        End If
        If fieldChanged Then    'If undoChanges, then fieldChanged never becomes true
            frm!txtLastUpdated = Now()
            frm!cboLastUpdatedByUserID = Forms!frmLogin!cboUname.Column(1)
            frm!ChangeTrackingBox.Value = Format(Now(), "mm/dd/yy") & " - " & Forms!frmLogin!cboUname.Column(4) & _
                ": " & Left$(changes, Len(changes) - 2) & Chr(13) & Chr(10) & frm!ChangeTrackingBox.Value
        End If
    End If

    If messageShown Then    'If the message box was shown for the item already being procured, then cancel any form navigation
        fillLastUpdated = False
    Else
        fillLastUpdated = True
    End If
    Exit Function
End Function

Thanks in advance for the help!

[MMcCarthy]

You need to requery/reopen the form containing the deleted record. That will get rid of the #deleted markers.

Have you looked at the IsDirty event. I think this would save you a lot of effort on edited records.

[jbrumbau]

Thanks for the reply. I can't requery the form because these operations are done before a form requery (Form_Before_Up date event). The purpose is to check if any fields have been changed, and if so, record all changes made into <frm!ChangeTrac kingBox.Value> and also the person who updated it <frm!cboLastUpd atedByUserID> and the date it was done <frm!txtLastUpd ated>. If that particular record is locked, then undoChanges is passed as true, in which case it stops the first moment it finds a box with a differing value.

I need some way to automatically skip deleted boxes without it crashing the whole function.

Thanks again for the help.

[MMcCarthy]

I can't think of anything offhand. If you try to check the value of the control then the control has focus and the application throws an error.

You might want to look at overwriting the error using error handling. If the error message thrown up on crashing gives the err number then use it to catch the error and code the behavior you want. Does that make sense?

[jbrumbau]

I basically used an error handler that checks if the error code for "#Deleted" was hit, then it does frm.undo to essentially wipe out all changes and tells the user to reopen the form and try again.

If there is a way to just skip these deleted boxes that would be a huge blessing.