Tjyylli

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 (choses 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.

Could you not simply test whether Me.txtDBKey is null or an empty string and branch accordingly?

For example:

If IsNull(Me.txtDBKey) Or Me.txtDBKey = vbNullString Then

    ' Notify user?

Else

    '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