1

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.

Ben Rhys-Lewis
  • 3,118
  • 8
  • 34
  • 45
Oopsyscoops
  • 13
  • 3
  • Is there any other code in the form, especially in the `BeforeUpdate` or `AfterUpdate` events? – Andre Dec 01 '15 at 22:06
  • If things start behaving weirdly all of a sudden, Repair & Compact and a [Decompile run](http://stackoverflow.com/a/3268188/3820271) are always worth a try. Although this doesn't really sound like something that would be solved by it. – Andre Dec 01 '15 at 22:08
  • 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 Dec 02 '15 at 03:21
  • 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 Dec 02 '15 at 08:23

2 Answers2

1

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.

Chris Test
  • 59
  • 4
  • I've seen this suggestion around the internet, but could you explain how exactly that would help? – Oopsyscoops Dec 04 '15 at 16:22
  • I'm not 100% sure exactly how it works, but the system I use has an Access app using SQL data via an ODBC connection. Without a timestamp field, we often experience a delay when updating records, as they're updated in Access and then the update runs in the SQL data source, where the record is deleted then recreated with the new data. The reason you see #DELETED is because the record has been deleted, and your form needs to grab the new updated version of it. – Chris Test Dec 04 '15 at 16:27
  • (locked out for editing for too long). And the timestamp field, as far as I understand, facilitates the grabbing of the correct record. The way I imagine it works is that it selects the records, groups by myPrimaryKey and selects the maximum timestamp. – Chris Test Dec 04 '15 at 16:34
  • Not quite. The Timestamp field helps Access to determine if the record was edited by another user while you were editing it. See https://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx#optaccsql_topic3 – Andre Dec 04 '15 at 16:40
  • That said, it is definitely worth a try. Although it's a bit of a hassle to add a Timestamp column to an existing table with data. – Andre Dec 04 '15 at 16:41
  • Thanks @Andre451 - wasn't entirely too sure myself, but I know my Access app doesn't work without it – Chris Test Dec 04 '15 at 16:41
  • I appreciate all of the help that you guys have offered. Really, my first question on SO has gone quite well, and you guys are cool. Guess what, though? I came back into work today to try out the suggestions you offered, and the bloody thing works now. It just works like it did before. I changed nothing to make it this way, so it must have been divine intervention or something. Again, thank you all for your suggestions. I will mark this as my accepted answer in thanks. – Oopsyscoops Dec 04 '15 at 16:56
  • Okay, so I've replicated the issue. It happens every single time AFTER I have opened a report that is connected to the same table in the db. I have restarted the computer, C&R'd the db, and it still keeps happening. Is it possible that it could have something to do with me having both a form and a report open that both use the same db table as their recordsource? – Oopsyscoops Dec 04 '15 at 17:21
  • NEVERMIND I FIGURED IT OUT. I was incorrectly populating some of my text fields that were linked to the db table. Classic case of PEBCAK. Thanks anyway, guys =) – Oopsyscoops Dec 04 '15 at 17:52
0

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.

dbDesigner
  • 79
  • 1
  • 6