Form fields show '#DELETED' after running 'acCmdSaveRecord'

1
votes

I've been having an issue today where one of my forms is filling all fields with "#DELETED" after I save the record. This wasn't happening three hours ago, and it seems to have started without me changing anything on the form itself. The record is still being saved, and there are no "#DELETED" entries in the table at any point. I am using the following code to open the form:

DoCmd.OpenForm "frmPoster", , , , acFormAdd

This is the code that saves it:

Call DoCmd.RunCommand(acCmdSaveRecord)

I am using Access2010 with SQL Server and VB. As I said, the really confusing part is that seemingly nothing has changed about this form that would be causing this. It worked, and now it doesn't. Any ideas?

Also, if you want any more information regarding the issue, I will be happy to provide.

2
ms-accessvba
Is there any other code in the form, especially in the BeforeUpdate or AfterUpdate events? - Andre
If things start behaving weirdly all of a sudden, Repair & Compact and a Decompile run are always worth a try. Although this doesn't really sound like something that would be solved by it. - Andre
There is some code in the AfterUpdate events for a few textbox fields, but they have never caused issues before... Also, I already ran R&C to no avail. Does a "decompile run" have any risk of losing all my work up to this point? I'm pretty paranoid about that, as the project is practically done. - Oopsyscoops
I have never had any problems with Decompile, but it is an undocumented feature. You should follow these steps by the letter, the first of them is "1. backup your database". - Andre

2 Answers

1
votes

I've used SQL with Access a lot and had some similar problems, I'd advise unless you have already to make sure you have a timestamp field in the SQL data, and also do a me.requery in VBA after the update so it re-selects the record.

0
votes

I ran into the same problem yesterday - as soon as I try to save the record, all fields turn to #Deleted. Tried to resolve for hours - no luck. I use Access 10 / 2016 and MySQL ODBC connector 5.3. I narrowed it down to one specific field with a 13 digit number. The datatype for the field was varchar. When I enter any value that does not match the required format (as checked by VBA), and then delete or edit it and move to the next field, the form refuses to create the UserID upon save and gives #Deleted. If I skip this field during data entry, then the form creates the UserID (Primary Key) and saves. Also, if I enter a value that matches the field criteria first time round, the form generates the Primary key and saves. As suggested somewhere I changed the datatype to numeric 14,0 for this field. This did not help. I tried to requery the form after saving as suggested but since the record is deleted, it simply displays blank fields. So here is my hack: 1. On the "On Enter" event of the first field in the form, run code to set temporary values in each of the required fields, for example: First I created an unbound hidden field with default value of "No". Then:

If Me.NewRecord=True and Me.FormPreppedYN="No" then
    Me.txtFirstName="Hello"
    Me.txtLastName="There"
    Me.IDNr=123
    Me.Ref="ABC"
    Me.Dirty=False
    Me.txtFirstName="" 
    Me.txtLastName=""
    Me.IDNr=Null
    Me.Ref=""
    Me.txtFirstName.SetFocus
    Me.FormPreppedYN="Yes"
End If

This forces the form to create a Primary key. This happens very quick and the user doesn't notice it. They just enter the data. This worked reliably every time. I still don't know what causes the error, but this fixes it.