0

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.

0

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
  • Lee; Thanks for the response. If all else fails (meaning no one has an answer for this question) this or something similar (e.g. using error trapping code to at the least communicate the situation to the user and possibly preventing them from clearing the field before saving) may be the approach to take. However, this would be responding to the symptom and not addressing the cause. – CanuckBuck Apr 15 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.