MS Access - #DELETED when adding a record

Question

MS Access - #DELETED when adding a record

CanuckBuck

Hall of Fame

Points: 3915
More actions
April 18, 2019 at 12:10 pm

#3450001
Full disclosure: I posted this question on SuperUser and did not get an answer. I'm hoping that I'll have better luck here.
<hr />
I'm using an MS Access 2010 .accdb database connected to a SQL Server 2016 database. My tables and views from SQL are managed as linked tables in MS Access. I'm using a DSNless connection with the SQL Server Native Client 11.0 driver (don't know if this matters or not).
I have a view/search form that enables users to view records in the database. The form has an Edit-this-Record button and an Add-New-Record button.
I have an edit/create form which uses an updateable SQL Server query. This form has a number of mandatory combobox fields and one combobox field which is optional. The form also has a number of other fields - text and date.
The code of the Add-New-Record button on the view/search form is as follows;
```
DoCmd.OpenForm "Application Edit - Template", acNormal, "", "", acAdd, acDialog
```
The edit/create form has a Save-And-Close button with the following code;
```
Private Sub btnSaveClose_Click()
On Error GoTo btnSaveClose_Click_Err

' Save the edits to the form i.e. the main form
'DoCmd.RunCommand acCmdSaveRecord

If Me.Dirty Then
Me.Dirty = False
End If

'Close the form

DoCmd.Close acForm, "Application Edit - Template"

btnSaveClose_Click_Exit:
Exit Sub

btnSaveClose_Click_Err:
MsgBox Error$
Resume btnSaveClose_Click_Exit

End Sub
```
In order to display the newly created record in view/search form the following code has been added to the After_Update event of the edit/create form.
```
Private Sub Form_AfterUpdate()
Dim rst As DAO.Recordset

'Refresh the record to get the latest data that has been saved
'note that this is primarily to refresh the record details
'i.e. last updated by and Last update date time.

Me.Refresh

'If it's a new record...
If boolNewRecord Then
'Refresh the browse form so that the newly saved record is in the recordset
'We set boolBypassFormCurrent = True so we can bypass the redundant
'calls to the Form_Current event on the view/search form

boolBypassFormCurrent = True
[Forms]![Application Browse - Template].Requery

'Navigate the browse form to the newly saved record
Set rst = Forms("Application Browse - Template").RecordsetClone
With rst
.FindFirst "[DB_Key] = " & Me.txtDBKey <=== Failure Here!
If Not .NoMatch Then
Forms("Application Browse - Template").Bookmark = .Bookmark
End If
End With
Set rst = Nothing
End If
boolNewRecord = False
boolBypassFormCurrent = False

btnSaveClose.Enabled = False

End Sub
```
Once the edit/create form is displayed, if the user supplies values for all comboboxes, including the optional one, and saves the record, everything works as desired (the record is saved, the view/search form is updated to display the new record, and the edit/create form is closed). If the the optional combobox is left blank and the record is saved, everything works as desired. If a value is initially supplied for the optional combobox and then, before saving, the optional value is cleared and then the record is saved, the following error is thrown;
Run-time error: '3077' Syntax error (missing operator) in expression.
The debugger stops at the line;
```
.FindFirst "[DB_Key] = " & Me.txtDBKey
```
In the form, all fields which are not comboboxes display the value - **#Deleted**
Typing ?Me.txtDBKey in the immediate window reveals that its value is an empty string i.e. blank (not null).
I believe that because Me.txtDBKey is blank that the code is effectively being interpreted as;
```
rst.FindFirst [DB_Key] =
```
Which would explain the error message.
When the debugger is terminated and the edit/create form is closed - returning control to the view/search form, the record that the user was attempting to add is displayed!
It's as though the saved record somehow *flies away* from the edit/create form when the record is saved - before the After_Update event, only in the case when the optional combobox is set and then unset.
Can anyone explain this very unfriendly behavior? Anyone have any ideas for eliminating it?
It's worth noting that if the user edits an existing record (chooses the Edit-this-Record button on the view/search form) and removes the value from the optional combobox and saves the record everything works as desired.
I've done considerable searching on this error and have not been able to find anything that matches my problem. The table underneath the SQL view has an integer primary key and a RowVersion column which are both included in the view. MS Access detects the primary key and also correctly identifies the RowVersion column.
Thank you in advance for your assistance with this very vexing problem.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WendellB SSCrazy Eights Points: 8678 More actions · Answer 1

One of the most common issues where you get the **#Deleted** message is where you have a boolean (Yes/No) field in the SQL Server table that doesn't have a default value set. Access unfortunately allows three values in such a field, True, False and Null. However unless the combo box that you are clearing is for such a field, it seems unlikely that is the issue. I presume by "an updateable query" you mean a SQL Server view that you are linking to - if not what does the form data source look like?

Without seeing the form and the table design, and all the logic both in Access and SQL Server, it's difficult to give much further advice. I must say that it appears to me you are doing lots of things in code that Access does automatically. I've designed a number of forms with numerous combo boxes, subforms, etc, and let Access save the record once I go to a new form, or return to a previous record, or closed that form and returned to a prior form. Can you give some further insight into the user needs, other than returning to the browse form and displaying the record just added/edited?

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 2

Hello Wendell. Thank you for your response.

Sorry in advance for the lengthy reply. I don't even know that there's a clue in here...

You are correct that the field in question is not a bit field. The column in the base table is an integer with declarative referential integrity (in SQL Server) to the look-table.

You are correct that by "updateable query” I do mean a SQL server view. There is one detail about primary key index to clarify though. MS Access doesn't recognize it automatically from the view (don't know why that is). In order for the linked table in MS Access to be updateable I needed to define a primary key index in MS Access. Here's my index definition.

CurrentDb.Execute "CREATE UNIQUE INDEX AppTracker_Milestone_Dates_Edit_UIX " _
& "ON AppTracker_Milestone_Dates_Edit (DB_Key) " _
& "WITH DISALLOW NULL;"

This is the same index as is defined on the base table in SQL Server.

Here is the underlying view;

USE [AppTracker]
GO

/****** Object:  View [dbo].[AppTracker_Milestone_Dates_Edit]    Script Date: 4/19/2019 12:47:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/***************************************************************************************
Object Name: 
[dbo].[AppTracker_Milestone_Dates_Edit]

Purpose:
This view was created to support the functionality of the AppTracker solution.

Usage:
This view takes no parameters and can be executed as follows;

SELECT <columns> FROM [dbo].[AppTracker_Milestone_Dates_Edit] WHERE <Conditions>

Notes:
The intent of this view is to provide a editable list of Milestone_Dates records
(Applications) for use in the AppTRacker solution. The functionality of the AppTracker 
is such that it will retrieve only one record at a time if the user is desiring
to update it or to display a blank data entry form if the user is desiring to add 
a new record.

Note that although [RowVersion] and [Corporate_GUID] are not required for the user interface
they must be included in the view in order for the view to be updateable in
MS Access.

Commented fields from the underlying table are not presently used but are left
in the code in case they are needed in the future.

Columns additional to the application details are included to enable 
visual cues in the AppTracker in the case of editing records e.g.
- Expired look-up values
- record counts for "child records" in the subforms

Error Indications:

Revisions:
Rev 0Jan 09 2019Michael LockInitial build
***************************************************************************************/
ALTER VIEW [dbo].[AppTracker_Milestone_Dates_Edit] AS
SELECT 
MD.[DB_Key]
,MD.[Application_Id]
,MD.[Applicant]
,MD.[Project_Name_DB_Key]
,MD.[Regulatory_Sector_DB_Key]
,MD.[Directorate_DB_Key]
,MD.[Team_DB_Key]
,MD.[Group_DB_Key]
,MD.[Category_DB_Key]
,MD.[Type_DB_Key]
,MD.[Purpose_DB_Key]
,MD.[Received_Date]
,MD.[Assignment_Date]
,MD.[Completeness_Reviewer_Name]
,MD.[Assure_Completeness_End_Date]
,MD.[Technical_Reviewer_Name]
,MD.[Technical_Review_End_Date]
,MD.[Decision_Maker_Name]
,MD.[Decision_Date]
,MD.[Application_Decision_DB_Key]
--,MD.[Applicant_Contact_Name]
--,MD.[Applicant_Contact_Phone]
--,MD.[Applicant_Contact_Email]
--,MD.[FNC_Id]
--,MD.[FNC_Adequacy_State_DB_Key]
--,MD.[FNC_Adequacy_Date]
--,MD.[PNoA_End_Date]
,MD.[Audit_State_DB_Key]
,MD.[Audit_State_Date]
,MD.[Auditor_Name]
,MD.[Audit_Result_DB_Key]
--,MD.[Field_Office_DB_Key]
--,MD.[Land_Use_Officer_Name]
,MD.[EIA_Required]
,MD.[EIA_Complete_Date]
--,MD.[Land_Status_DB_Key]
,MD.[Approval_Id]
,MD.[Approval_Expiry_Date]
,MD.[ECM_Document_Id]
--,MD.[Paper_File_Request_Date]
--,MD.[Scanned_File_Upload_Date]
,MD.[CoP_Rush_Request]
,MD.[Geographic_Area_Descriptor]
,MD.[OS_Area_DB_Key]
,MD.[Approval_Document_Id]
,MD.[Created_By]
,MD.[Creation_Date_Time]
,MD.[Last_Updated_By]
,MD.[Last_Update_Date_Time]
,MD.[RowVersion]
,MD.[Corporate_GUID]
,MD.[UWI_LR]
,MD.[Well_Licence_Id]
,MD.[Regulatory_Field_Code]
,MD.[Regulatory_Pool_Code]
,MD.[Conf_State_DB_Key]
,MD.[Max_Prod_Rate_Kind_DB_Key]
,MD.[Conf_Expiry_Date]
,MD.[Regulatory_Field_Name]
,MD.[Regulatory_Pool_Name]
,MD.[Well_Type_Waiver_DB_Key]
,MD.[GPP_Via_O38]
,MD.[Pool_Type]
,MD.[Assignment_Assignee]
,MD.[Assignment_Creation_Date]
,PJ.[Obsolete_Date] AS [Project_Name_Obsolete_Date]
,RS.[Obsolete_Date] AS [Sector_Obsolete_Date]
,DI.[Obsolete_Date] AS [Directorate_Obsolete_Date]
,TM.[Obsolete_Date] AS [Team_Obsolete_Date]
,GR.[Obsolete_Date] AS [Group_Obsolete_Date]
,CA.[Obsolete_Date] AS [Category_Obsolete_Date]
,TY.[Obsolete_Date] AS [Type_Obsolete_Date]
,PU.[Obsolete_Date] AS [Purpose_Obsolete_Date]
,RD.[Obsolete_Date] AS [Application_Decision_Obsolete_Date]
,ST.[Obsolete_Date] AS [Audit_State_Obsolete_Date]
,AR.[Obsolete_Date] AS [Audit_Result_Obsolete_Date]
,OS.[Obsolete_Date] AS [OS_Area_Obsolete_Date]
,CS.[Obsolete_Date] AS [Conf_State_Obsolete_Date]
,RC.[Obsolete_Date] AS [Rate_Control_Type_Obsolete_Date]
,WW.[Obsolete_Date] AS [Well_Type_Waiver_Obsolete_Date]
,(SELECT COUNT(*) FROM [dbo].[Application_Location] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [Location_Count]
,(SELECT COUNT(*) FROM [dbo].[Information_Request] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [Information_Request_Count]
,(SELECT COUNT(*) FROM [dbo].[Comment] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [Comment_Count]
,(SELECT COUNT(*) FROM [dbo].[SOC_Link] WHERE [Milestone_Dates_DB_Key] = MD.[DB_Key]) AS [SOC_Count]
--SELECT count(*)
FROM [dbo].[Milestone_Dates] MD
LEFT JOIN [dbo].[R_Project_Name] PJ
ON PJ.[DB_Key] = MD.[Project_Name_DB_Key]
JOIN [dbo].[R_Regulatory_Sector] RS
ON RS.[DB_Key] = MD.[Regulatory_Sector_DB_Key]
JOIN [dbo].[R_Application_Directorate] DI
ON DI.[DB_Key] = MD.[Directorate_DB_Key]
JOIN [dbo].[R_Application_Team] TM
ON TM.[DB_Key] = MD.[Team_DB_Key]
JOIN [dbo].[R_Application_Group] GR
ON GR.[DB_Key] = MD.[Group_DB_Key]
JOIN [dbo].[R_Application_Category] CA
ON CA.[DB_Key] = MD.[Category_DB_Key]
JOIN [dbo].[R_Application_Type] TY
ON TY.[DB_Key] = MD.[Type_DB_Key]
JOIN [dbo].[R_Application_Purpose] PU
ON PU.[DB_Key] = MD.[Purpose_DB_Key]
LEFT JOIN [dbo].[R_Application_Decision] RD
ON RD.[DB_Key] = MD.[Application_Decision_DB_Key]
LEFT JOIN [dbo].[R_Audit_State] ST
ON ST.[DB_Key] = MD.[Audit_State_DB_Key]
LEFT JOIN [dbo].[R_Audit_Result] AR
ON AR.[DB_Key] = MD.[Audit_Result_DB_Key]
LEFT JOIN [dbo].[R_OS_Area] OS
ON OS.[DB_Key] = MD.[OS_Area_DB_Key]
LEFT JOIN [dbo].[R_Conf_State] CS
ON CS.[DB_Key] = MD.[Conf_State_DB_Key]
LEFT JOIN [dbo].[R_Max_Prod_Rate_Kind] RC
ON RC.[DB_Key] = MD.[Max_Prod_Rate_Kind_DB_Key]
LEFT JOIN [dbo].[R_Well_Type_Waiver] WW
ON WW.[DB_Key] = MD.[Well_Type_Waiver_DB_Key]


GO

Here's a screenshot of the data entry form (fields bordered in red are required fields)

AppTracker Data Entry

For the sake of completeness here's a screenshot of the view/search form

The view form is designed so that by default, when it opens, it retrieves undecided records that the user has worked on. The User Name and and State fields enable them to view records others have worked on and also decided records.

A user can either edit an existing record or add a new one. With either choice, the same edit form is displayed, either in edit or add mode. Here are the edit and add subroutines. Here they are

Private Sub btnEdit_Click()
Dim RecordNumber As Long
Dim DBKey As Long

On Error GoTo btnEdit_Click_Err

'Remember the DB_Key of the record we're working on
'So we can return to it after calling the Refresh method

DBKey = Me.txtDBKey

'open the data entry form in edit mode, showing the currently selected record from the browse form

DoCmd.OpenForm "Application Edit - Template", acNormal, "", "[DB_Key]=" & txtDBKey, acEdit, acDialog

'Set the boolBypassFormCurrent variable so we can bypass the redundant
'calls to Form_Current() which occur when the Refesh method is
'called since we're going to immediately return to the record
'the user was editing, which will also call Form_Current()

boolBypassFormCurrent = True

'Requery the databaset to get the changes that were made by the
'user and then return to the record that the user was looking at.

Me.Requery
boolBypassFormCurrent = False
Me.Recordset.FindFirst "[DB_Key] = " & DBKey

'If the sort order chosen by the user results in the edited record
'being row 1 then the Me.Recordset.FindFirst call does not trigger
'the final Form_Current event so we need to call the SetForm subroutine

If Me.CurrentRecord = 1 Then SetForm 'Note: SetForm is a subroutine that sets various visual
                                     'cues on the form e.g. record counts in subforms

btnEdit_Click_Exit:
Exit Sub

btnEdit_Click_Err:
MsgBox Error$
Resume btnEdit_Click_Exit

End Sub

As a side note, in the btnEdit_Click() routine I've had to resort to saving the DB_Key of the records being edited and then using Me.Requery and then navigating back to the record being edited because, for reasons I do not understand, Me.Refresh does not actually refresh the record with the edits the user made on the edit form.

Private Sub btnAdd_Click()
On Error GoTo btnAdd_Click_Err

'Open the data entry form in add record mode

DoCmd.OpenForm "Application Edit - Template", acNormal, "", "", acAdd, acDialog
SetForm

btnAdd_Click_Exit:
Exit Sub

btnAdd_Click_Err:
MsgBox Error$
Resume btnAdd_Click_Exit

End Sub

When a user is adding a new record I use the Form_Update subroutine to save the record and update the view form. It looks like this;

Private Sub Form_AfterUpdate()
Dim rst As DAO.Recordset
      
'Refresh the record to get the latest data that has been saved
'note that this is primarily to refresh the record details
'i.e. last updated by and Last update date time.

    Me.Refresh
    
    'If it's a new record...
    If boolNewRecord Then
        'Refresh the browse form so that the newly saved record is in the recordset
        boolBypassFormCurrent = True
        [Forms]![Application Browse - Template].Requery
        
        'Navigate the browse form to the newly saved record
        Set rst = Forms("Application Browse - Template").RecordsetClone
        With rst
            .FindFirst "[DB_Key]  = " & Me.txtDBKey
            If Not .NoMatch Then
                Forms("Application Browse - Template").Bookmark = .Bookmark
            End If
        End With
        Set rst = Nothing
    End If
    boolNewRecord = False
    boolBypassFormCurrent = False
    
    btnSaveClose.Enabled = False
    btnCancel.Caption = "Close"

End Sub

So, that's pretty much it. I'm sure all of this would be much clearer if I could package up the SQL DB and the Access DB but the SQL DB has dependencies on other DB's that I'd have to resolve... It would be a lot of work.

As far as the user's requirements It's really only to either choose a record from the view form, edit it in the edit dialog, close the dialog and return to the edited record in the view form OR add a new record, close the dialog and return to the newly added record in the view form.

Again, thanks in advance for any assistance you can provide.

p.s. My fallback solution would be to prevent a user from clearing the project name field once it had been set when adding a new record and displaying a message indicating that it can be cleared after the record is saved. A bit of a hack but it would work.

WendellB SSCrazy Eights Points: 8678 More actions · Answer 3

For some strange reason, the post I made last week seems to have disappeared. So I shall try again:

I suspect your issue has to do with the primary key on your SQL linked view, since Access doesn't recognize it when you link to the view. Try adding "CLUSTERED" to your statement that creates the index in SQL Server. See https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd171921(v=sql.100) for details on why that is important. Not sure that will solve your issue with the combo box being updated and then cleared, but you never know....

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!

CanuckBuck Hall of Fame Points: 3915 More actions · Answer 4

Wendell;

I too have experienced lost posts on the SSC site over the last while - since they changed the site.

Thanks for the reply.

I'm familiar with indexed views, however, the structure of my view precludes the creation of a clustered index since it includes both subqueries and left joins which are included to enable other features of the user experience in MS Access. I could eliminate all of those things (which would simply leave me with the base table, in which case I wouldn't need a view), but then I'd have to figure out alternate means to provide the same user experience features.

Since the initial problem is an very low incidence edge case I've decided to trap the condition and instruct the user on how to work around the problem.