CODEWITHSUNDEEP
formsms-accessvbams-access-2013
Trippley
Trippley

Reputation: 1

Recordset Addnew modifies first record in a table

I'm currently having problems with adding a new record in a table through VBA in Access. The VBA is used through a Button in a Form which has 2 Combination fields and 2 date fields. 

Private Sub Schlussel_hinzufügen_Click()
    On Error GoTo ErrHandler
    Dim R As Recordset
    Set R = CurrentDb.OpenRecordset("Schluesselhistorie")
    R.AddNew
    ' Normally data is added to the record between these two
    R.Close
    Me.Requery
    DoCmd.Close
    Exit Sub
ErrHandler:
    MsgBox "Couldn't save record!", vbCritical
End Sub

As soon as the R.AddNew is called the first record of the table is modified with the Data from the combination and date fields. A completely new record at the end of the table is created as well when 

R![SLH_Schluessel_ID] = Me.Kombinationsfeld13.Value
R![SLH_Kontakt_ID] = Me.Kombinationsfeld15.Value
R![SLH_Datum_Ausgabe] = Me.SLH_Datum_Ausgabe.Value
R![SLH_Datum_Rueckgabe_Soll] = Me.SLH_Datum_Rueckgabe_Soll.Value
R.Update

is called though. I am kinda irritated as the former (first row event) shouldn't happen as I know and when code is added above both the first row is modified and a new record with these values is added.

The table is externally linked and the field names are in German.

Are there restrictions to the DAO where the Recordset used can't specify which line the Addnew should use. Or does the Addnew take the values of the Form to automatically add the Values to the table?

Upvotes: 0

Views: 827

Answers (1)

Gustav
Gustav

Reputation: 56006

You should use the recordsetclone of the form:

Private Sub Schlussel_hinzufügen_Click()
    On Error GoTo ErrHandler
    Dim R As DAO.Recordset
    Set R = Me.RecordsetClone
    R.AddNew
    ' Normally data is added to the record between these two
    R.UpDate
    R.Close
    Exit Sub
ErrHandler:
    MsgBox "Couldn't save record!", vbCritical
End Sub

Upvotes: 0

Related Questions