I am using a MS Access 2010 .accdb database connected to a SQL Server 2016 database. My tables and SQL views are managed as linked tables in MS Access. I'm using a DSNless connection with the SQL Server Native Client Driver 11.0 (I do not know if it counts or not).
I have a view / search form that allows users to view records in the database. The form has an Edit This Record button and an Add New Record button.
I have a change / create form that uses an updatable SQL Server query. This form has a number of required fields and an optional field. The form also includes a number of other fields – text and date.
The code of the Add-New-Record button on the visualization / 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 () Go to the error btnSaveClose_Click_Err Save the changes in the form, ie the main form & # 39; DoCmd.RunCommand acCmdSaveRecord If Me.Dirty Then Me.Dirty = False End if & # 39; Close the form DoCmd.Close acForm, "Application Edit - Template" btnSaveClose_Click_Exit: Output under btnSaveClose_Click_Err: MsgBox Error $ Resume btnSaveClose_Click_Exit End Sub
In order to display the newly created record as a display / search, the following code was added to the After_Update event of the edit / create form.
Private Sub Form_AfterUpdate () Dim rst As DAO.Recordset & # 39; Update registration to get the latest data backed up & # 39; note that it mainly involves updating the details of the registration & # 39; t is to say. Last updated by and Date last updated. Me.Refresh & # 39; If it is a new disc ... If boolNewRecord then & # 39; Refresh the navigation form so that the newly registered record is in the recordset. & # 39; We defined boolBypassFormCurrent = True to bypass the redundant & # 39; Calls to the Form_Current event on the form / display / search boolBypassFormCurrent = True [Forms]![Application Browse - Template].Requery & # 39; Navigate the navigation form to the new saved record Define rst = Forms ("Application Browse - Template"). RecordsetClone With first .FindFirst "[DB_Key] = "& Me.txtDBKey <=== Failure here! If this is not the case Forms ("Browse Applications - Template"). Bookmark = .Bookmark End if Finish by Set first = nothing End if boolNewRecord = False boolBypassFormCurrent = False btnSaveClose.Enabled = False End Sub
Once the edit / create form is displayed, if the user provides values for all dropdown lists, including the optional one, and saves the record, everything works as desired (saving is saved, the display / search form is updated to display the new record, and the edit / create form is closed). If the optional drop-down list is left blank and the recording is saved, everything works as you wish. If a value is initially provided for the optional drop-down list, then, before the backup, the optional value is cleared, then the record is saved and the following error is generated.
Runtime Error: 3077 & # 39; Syntax error (missing operator) in the expression.
The debugger stops at the line;
.FindFirst "[DB_Key] = "& Me.txtDBKey
In the form, all fields that are not drop-down lists display the value – # Deleted
Type? Me.txtDBKey in the immediate window reveals that its value is an empty string, that is, empty (and not null).
I think, because Me.txtDBKey is empty, the code is interpreted as;
rst.FindFirst [DB_Key] =
Which would explain the error message.
When the debugger is finished and the edit / create form is closed – by returning the control to the display / search form, the record that the user was trying to add was displayed !
It's like if the recording saved somehow s & # 39; flies from the edit / create form when saving the record – before the After_Update event, only in case the optional drop-down list is set, then disabled.
Can any one explain this very hostile behavior? Someone has ideas to eliminate it?
It should be noted that if the user edits an existing record (chooses the Edit this record from the display / search form button) and removes the value from the optional drop-down list and saves the record, everything works as desired.
I have done a lot of research on this error and I have not found anything that matches my problem. The table under the SQL view has an integer primary key and a RowVersion column, both of which are included in the view. MS Access detects the primary key and also correctly identifies the RowVersion column.
We thank you in advance for your help in solving this very difficult problem.