By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,230 Members | 1,265 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,230 IT Pros & Developers. It's quick & easy.

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

P: 52

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.Column(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
Expand|Select|Wrap|Line Numbers
  1.  ElseIf frm.Controls(ctrlSource) = rsexist.Fields(ctrlSource) Then 
Expand|Select|Wrap|Line Numbers
  1. Function fillLastUpdated(ByRef frm As Form, tableName As String, uniqueCtl As TextBox, Optional undoChanges As Boolean = False) As Boolean
  2.     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
  3.         Dim rsexist As Recordset    'rsexist is the existing record set while the current record is from form frm
  4.         Dim ctl As Control
  5.         Dim keyname As String, keyValue As String, ctrlSource As String 
  6.         Dim changes As String
  7.         Dim fieldChanged As Boolean, messageShown As Boolean
  9.         Set rsexist = CurrentDb.OpenRecordset(tableName, dbReadOnly)
  10.         keyname = uniqueCtl.ControlSource
  11.         keyValue = uniqueCtl.Value 'rscurr.Fields(valueSearch)
  12.         fieldChanged = False
  13.         messageShown = False
  14.         changes = ""
  15.         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
  16.             For Each ctl In frm.Controls
  17.                 If HasProperty(ctl, "ControlSource") Then
  18.                     ctrlSource = ctl.ControlSource
  19.                     If fieldExists(ctrlSource, rsexist) Then    'Sometimes you have a control that is part of another table
  20.                         If IsNull(frm.Controls(ctrlSource)) And IsNull(rsexist.Fields(ctrlSource)) Then '2 Null strings do not pass equal check
  21.                         ElseIf frm.Controls(ctrlSource) = rsexist.Fields(ctrlSource) Then
  22.                         ElseIf InStr(1, ctrlSource, "LastUpdated", vbTextCompare) Then  'Changes in updated boxes shouldn't be tracked
  23.                         Else
  24.                             If undoChanges Then
  25.                                 If messageShown Then
  26.                                 Else
  27.                                     Call MsgBox("This item is procured and cannot be edited. Undoing all changes.", vbExclamation, "Record is Locked")
  28.                                     messageShown = True
  29.                                     frm.Undo    'Had to use generic undo since individual field fixing doesn't update the display
  30.                                     Exit For    'Had to use generic undo since individual field fixing doesn't update the display
  31.                                 End If
  32.                             Else
  33.                                 If hasLabel(ctl) Then
  34.                                     changes = "[" & ctl.Controls(0).Caption & "]=" & rsexist.Fields(ctrlSource) & " -> " & frm.Controls(ctrlSource) & "; " & changes
  35.                                 Else
  36.                                     changes = "[" & ctrlSource & "]=" & rsexist.Fields(ctrlSource) & " -> " & frm.Controls(ctrlSource) & "; " & changes
  37.                                 End If
  38.                                 fieldChanged = True
  39.                             End If
  40.                         End If
  41.                     End If
  42.                 End If
  43.             Next ctl
  44.         End If
  45.         If fieldChanged Then    'If undoChanges, then fieldChanged never becomes true
  46.             frm!txtLastUpdated = Now()
  47.             frm!cboLastUpdatedByUserID = Forms!frmLogin!cboUname.Column(1)
  48.             frm!ChangeTrackingBox.Value = Format(Now(), "mm/dd/yy") & " - " & Forms!frmLogin!cboUname.Column(4) & _
  49.                 ": " & Left$(changes, Len(changes) - 2) & Chr(13) & Chr(10) & frm!ChangeTrackingBox.Value
  50.         End If
  51.     End If
  53.     If messageShown Then    'If the message box was shown for the item already being procured, then cancel any form navigation
  54.         fillLastUpdated = False
  55.     Else
  56.         fillLastUpdated = True
  57.     End If
  58.     Exit Function
  59. End Function
Thanks in advance for the help!
Jun 9 '10 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 10K+
P: 14,534
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.
Jun 11 '10 #2

P: 52
Thanks for the reply. I can't requery the form because these operations are done before a form requery (Form_Before_Update event). The purpose is to check if any fields have been changed, and if so, record all changes made into <frm!ChangeTrackingBox.Value> and also the person who updated it <frm!cboLastUpdatedByUserID> and the date it was done <frm!txtLastUpdated>. 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.
Jun 11 '10 #3

Expert Mod 10K+
P: 14,534
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?
Jun 11 '10 #4

P: 52
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.
Jun 11 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.