This is working up to the Update line, where I get an error about duplicates. Since I'm probably testing wrong, I'll let you play with it. I obviously didn't adjust the code in the Else yet. I've got to work on a project.
Code:
Dim DBS As dao.Database
Dim RS As dao.Recordset
Dim LngCnt As Long
Dim strsql As String
Dim lngNewID As Long
strsql = "SELECT * FROM TblRDStudyAuditDocument WHERE ID_D = " & Me.ID_D
Debug.Print strsql
Set DBS = CurrentDb 'OpenDatabase("W:\RCO\Databases\RCO_Database_BE.accdb")
Set RS = DBS.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
With RS
If Not .EOF Then
' .MoveFirst
' .Index = "PrimaryKey"
' .MoveLast
' lngNewID = .Fields(0) + 1
.AddNew
'.Fields(0) = lngNewID 'left out because it's an autonumber field
!Studyid = Studyid
!AuditDate = AuditDate
!SectionName = TxtSectionName
!DocName = DocName
!Comments = Comments
.Update
Else
.AddNew
.Fields(0) = 1
.Fields(1) = Studyid
.Fields(2) = AuditDate
.Fields(3) = TxtSectionName
.Fields(4) = DocName
.Fields(5) = Comments
.Update
ID_D = 1
End If
.Close
End With
Set DBS = Nothing
Set RS = Nothing