#Deleted & Record is Deleted Issue

ollyjolly · Feb 3, 2017

I have created a database that is stored on a network drive and accessed by about 15 users in many different locations across the network. Every day I get the error message 'Record is deleted', and appears to be caused from records in the table where the field values are #Deleted. The only way I have found to resolve this issue is to delete the record or records that have #Deleted and perform a compact and repair. I have split the database into a front end (forms, queries, etc.) and back end with linked tables, but this has not corrected the issue.

What would continually cause this to occur and what can I do to resolve?

DavidJMancini · Feb 3, 2017

There might be code running on your instance of the Front-End that is trying to parse records that someone else has already removed since you opened it?

DavidJMancini · Feb 3, 2017

If you have a Recordset opened on a table it is captured as a snapshot; updates someone else makes do NOT propogate into the recordset without a requery. If from that recordset you (read code) try(ies) to modify a deleted record, you'll get a nasty-gram.

ollyjolly · Feb 3, 2017

That would make sense but the database opens a form in Add mode and the form does not allow deletions.

DavidJMancini · Feb 3, 2017

And it happens specifically when you open *that* form?

ollyjolly · Feb 3, 2017

The issue does not happen specifically when the form is opened, but at somepoint during the day when users are entering records into the form.

jdraw · Feb 3, 2017

Do all users have their own copy of the front end on their PCs?

ollyjolly · Feb 3, 2017

No each user accesses a single front end file on the network. Several of the users are on virtual computers which do not have local profiles that they can save the front end too. It was only recently that I split the database trying to resolve the issue of deleted records. So while I know having every user access the same front end file is not ideal I know it is not the cause of the deleted record issue.

DavidJMancini · Feb 3, 2017

Is the form for basic data entry/editing, or are there macros or vba tied to it? If so, what kinds of functions does the form run?

ollyjolly · Feb 3, 2017

The form does have VBA. The user will first open a log-in form where they select their name from a drop down list and click an Ok button. Their name is assigned to a variable (CurrentEmployee) which is passed to another form (New Letters).

Once the New Letters form is loaded some VBA runs to allow changes to the form, and a command button is used to allow the addition of new records. The form has the new record button locked down and new records can only be added through a control button. Also, the form only allows additions, no changes of other records, and no deletions. There is VBA that performs validation on certain fields and values to prevent users from leaving fields blank. After the Identifier field is filled in a subform on the New Letters form displays other table records with the same Identifier.

Here is the VBA used on the New Letters form.

Code:

[SIZE=3][FONT=Calibri]Option Compare Database[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Option Explicit[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub cmdClose_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]On Error GoTo cmdClose_Click_Err[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]DoCmd.Close , ""[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]cmdClose_Click_Exit:[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]Exit Sub[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]cmdClose_Click_Err:[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]MsgBox Error$[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]Resume cmdClose_Click_Exit[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub AllLoad_Click()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If Len(Nz(Me.Authorization, "")) = 0 Then[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]   [/SIZE][SIZE=3]MsgBox "Please enter Authorization"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]   [/SIZE][SIZE=3]Me.Authorization.SetFocus[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]   [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ElseIf (Len(Nz(Me.Status, "")) = 0) Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox "Please enter Status"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Status.SetFocus[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]ElseIf (Len(Nz(Me.Date_expires, "")) = 0) And ((Me.Status <> "Expired") And (Me.Status <> "Care validated")) Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox "Please enter Expiration Date"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Date_expires.SetFocus[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]           [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ElseIf (Len(Nz(Me.Identifier, "")) = 0) And ((Me.Status <> "Expired") And (Me.Status <> "Care validated")) Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox "Please enter Identifier"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Identifier.SetFocus[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]ElseIf (Len(Nz(Me.Name, "")) = 0) And ((Me.Status <> "Expired") And (Me.Status <> "Care validated")) Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox "Please enter Name"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Name.SetFocus[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]ElseIf (Len(Nz(Me.Date_Entered, "")) = 0) And ((Me.Status <> "Expired") And (Me.Status <> "Care validated")) Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox "Please enter Date Entered"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Date_Entered.SetFocus[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]ElseIf (Len(Nz(Me.cboCurrentEmployee, "")) = 0) And ((Me.Status <> "Expired") And (Me.Status <> "Care validated”)) Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]MsgBox "Please enter Employee Name"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.cboCurrentEmployee.SetFocus[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][SIZE=3]Else[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]' '[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][SIZE=3]If (Not IsNull(cboCurrentEmployee)) Then[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]        [/SIZE][SIZE=3]TempVars.Add "CurrentUserID", cboCurrentEmployee.Value[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]        [/SIZE][SIZE=3]DoCmd.Close , ""[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]        [/SIZE][SIZE=3]DoCmd.OpenForm "New Letters", , , , , , "AllowChanges"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]        [/SIZE][SIZE=3]'When Open Form condition is AllowChanges then it triggers the Form Load event[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]        [/FONT][/SIZE]
[FONT=Calibri][SIZE=3]           [/SIZE][SIZE=3]DoCmd.GoToRecord acActiveDataObject, , acNewRec[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]        [/SIZE][SIZE=3]Exit Sub[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]'Me.AllowAdditions = True[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][SIZE=3]''End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][SIZE=3]''If Not Me.NewRecord Then[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][SIZE=3]' DoCmd.GoToRecord acActiveDataObject, , acNewRec[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][SIZE=3]End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub Form_Load()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If Me.OpenArgs = "AllowChanges" Then[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]   [/SIZE][SIZE=3]Me.AllowAdditions = True[/SIZE][/FONT]
[SIZE=3][FONT=Calibri] [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub cboCurrentEmployee_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]On Error GoTo cboCurrentEmployee_AfterUpdate_Err[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]TempVars.Add "CurrentUserID", Screen.ActiveControl.Value[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]On Error Resume Next[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]DoCmd.Requery ""[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]DoCmd.RunCommand acCmdRefresh[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]  [/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]cboCurrentEmployee_AfterUpdate_Exit:[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]Exit Sub[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]cboCurrentEmployee_AfterUpdate_Err:[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]MsgBox Error$[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    [/SIZE][SIZE=3]Resume cboCurrentEmployee_AfterUpdate_Exit[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub Form_AfterInsert()[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Me.AllowAdditions = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub Authorization_Change()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If Len(Me.Date_Entered & "") = 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Date_Entered = Date[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub Identifier_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Refresh[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub Name_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Refresh[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]If Len(Me!Name) > 0 Then[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]   [/SIZE][SIZE=3]Me!Name = UCase(Me!Name)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]   [/SIZE][SIZE=3]Me!Name = Trim(Me!Name)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][SIZE=3]End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub InitialAddress_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Refresh[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub NewAddress_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Refresh[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Private Sub Status_AfterUpdate()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Me.Refresh[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]
[FONT=Calibri][SIZE=3] [/SIZE][/FONT]

DavidJMancini · Feb 4, 2017

Ok, so for one - this line:

ElseIf (Len(Nz(Me.cboCurrentEmployee, "")) = 0) And ((Me.Status <> "Expired") And (Me.Status <> "Care validated”)) Then

you have a "fancy" quote... Still reading it

DavidJMancini · Feb 4, 2017

One thing that comes to mind: if this form is meant to enter data (I notice your AllowAdditions setting) then you may want to rethink the way the form is loaded. It looks like if two people open this form, they're initially looking at the same record; if one deletes the record, and another tries to push one of the buttons (that call values from "Me") then... you have a failure. That's my best guess judging from what you've described and your code.

ollyjolly · Feb 4, 2017

Thanks David. Not sure what happen with that one quote. Probably happened when I copied it into a word document before posting to the forum.

Not quite sure how to reload the form differently...Luckily users are not able to delete values from the form, and there is only one command button to add a new record. Based on my code and the way the form loads I believed the values from Me were only for one instance, and the values were passed back to the table as a new record once the command button to add a new record is clicked.

The_Doc_Man · Feb 4, 2017

OK, due to virtual workstation environment, you can't give everyone their own copy of the FE - but actually you can if you have at least a little control over the shared folder where all that stuff sits. Make distinct child folders for each person and put a copy of the FE in each of those folders. The ones who DO have physical workstations (and thus CAN have truly separate FE files) should have their own FE.

While I can't point to something specific that says your deleted records are due to the fact that you are sharing a FE file, I can say unequivocally that if you can force people to NEVER EVER IN A GAZILLION YEARS share one FE file, you eliminate a LOT of the file locking and internal Access locking that you otherwise would get from sharing the FE. Let's say this: I don't know if giving everyone a distinct folder and distinct FE will stop your deleted record cases. But I know it will make other things quieter and will eliminate one possible source of trouble.

ollyjolly · Feb 6, 2017

That is a good idea to allow each user to have their own FE. The deleted record issue was occurring prior to having the database split and hopefully it will cease or at least decrease once each person has their own FE. It is those types of issues that are so frustrating. :banghead::banghead:

ollyjolly · Feb 6, 2017

Quick update. After providing each user their own FE the issue is still occuring.

Shortly after the issue occurred I was able to isolate that around the time the #Deleted record was created in the table one of the users had very slow response on the network which leads me to believe the network connection with the database dropped.

Maybe the solution would be to not allow the record to save if the network connection to the database is lost?

kevlray · Feb 7, 2017

Some years back I saw some code that would make a copy of the FE when another user opened the DB. It would attach a user name to the copy of the FE, thus even though they were on a shared drive, each user ended up with their own copy of the FE while they were using it. I have long since deleted the code and do not remember where I found it.

ollyjolly · Feb 7, 2017

In attempt to resolve the issue I asked each user to let me know if anything out of the normal occurs. Shortly, afterwards one of the users contacted me with a run-time error. After filling in several fields on the form they selected a value from the drop down list in the Status field, and with no delay received a run-time error ‘3167’: Record is deleted. As soon as the error message appeared all the fields that were previously entered by the user and blank fields not yet entered displayed ‘#Deleted’.

The Status field is a Combo box that pulls a list of values from the Status table using SELECT [Status].[Status] FROM Status. The field does have an After Update event that performs Me.Refresh.

With each user entering data from individual front ends, and the error occurring immediately after the field was entered my thoughts are that the issue is not the result of file locking or network connection issues.

I am at a complete loss….

kevlray · Feb 7, 2017

I just know that odd things can happen when multiple users use the same front end. Not sure what the best solution is.

The_Doc_Man · Feb 8, 2017

Another factor to consider is whether you have selected optimistic or pessimistic locking. You ALWAYS want optimistic locking on any forms and queries that allow it. Pessimistic locking almost always is too restrictive and causes locking conflicts. There is ALSO the option to do NO locking. For anything treated as READ ONLY you might wish to explicitly set it to NO LOCKS.

#Deleted & Record is Deleted Issue

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Immoderate Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Immoderate Moderator

Similar threads

Users who are viewing this thread