Hi,

I'm having a problem when using DoCmd.GoToRecord , , acNewRec in the following code.

It doesn't create a new record. Instead, it changes the current record in the recordset.

What i really want to do is create a new record.

Private Sub Engine2_Click()
On Error GoTo ErrorHandler

    Dim strSearch As String

    Dim intAC_ID As Integer
MsgBox "In engine 2 click"
    'For text IDs
    'strSearch = "[______ID] = " & Chr$(34) & Me![cboSelect] & Chr$(34)

    'For numeric IDs
    'strSearch = "[______ID] = " & Me![cboSelect]
    strSearch = "[AE_AC_ID] = " & Me![cmbAE_AC_ID] & _
        " AND [AE_E_NO] = 2 "

    intAC_ID = Me![cmbAE_AC_ID]

    'Find the record that matches the control

    Me.RecordsetClone.FindFirst strSearch
    If (Me.RecordsetClone.NoMatch = True) Then
        MsgBox "couldn't find a match creating new record"
        DoCmd.GoToRecord , , acNewRec
        txtAE_E_NO = 2
        cmbAE_AC_ID = intAC_ID
        Me![cmbEngine2] = Null
        MsgBox "creating New record cmbAE_AC_ID = " & Me![cmbAE_AC_ID]

    Else
        MsgBox " Found: Bookmarking???"
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If


ErrorHandlerExit:
    Exit Sub

ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

End Sub

Hi David,

When you need to do something like adding a new record while you're working in a bound form, what you want to do is create the new record in the "background". If you need to position to the new record, use bookmarking once the record has been added.

Here's the excerpt I noticed:

    Me.RecordsetClone.FindFirst strSearch
    If (Me.RecordsetClone.NoMatch = True) Then
        MsgBox "couldn't find a match creating new record"
        DoCmd.GoToRecord , , acNewRec
        txtAE_E_NO = 2
        cmbAE_AC_ID = intAC_ID
        Me![cmbEngine2] = Null
        MsgBox "creating New record cmbAE_AC_ID = " & Me![cmbAE_AC_ID]

    Else
        MsgBox " Found: Bookmarking???"
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If


Assuming a table name of "tblEngine" (for example purposes only, this is what I'd do:

#1 Declare a string for a SQL statement
      Dim strSQL As String

#2 Modify the section for the .FindFirst logic - something like this:

    Me.RecordsetClone.FindFirst strSearch
    If (Me.RecordsetClone.NoMatch = True) Then
        MsgBox "couldn't find a match creating new record"
        Me![cmbEngine2] = Null
        MsgBox "creating New record cmbAE_AC_ID = " & Me![cmbAE_AC_ID]
        strSQL = ""
        strSQL = strSQL & "INSERT INTO tblEngine "
        strSQL = strSQL & "("
        strSQL = strSQL & "AE_E_NO, "
        strSQL = strSQL & "AE_CD_ID "
        strSQL = strSQL & ")"
        strSQL = strSQL & " VALUES "
        strSQL = strSQL & "("
        strSQL = strSQL & "2, "
        strSQL = strSQL & intAC_ID
        strSQL = strSQL & ")"
        CurrentProject.Connection.Execute (strSQL)
        Me.Bookmark = Me.RecordsetClone.Bookmark
    Else
        MsgBox " Found: Bookmarking???"
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If


I'm kind of guessing with the column names, but this should hopefully give a better idea.

Hope it helps.