Cannot add record using addnew with recordset

Option Compare Database

Public Function CheckCoating()

'This function Checks to see if tray was coated prior to wash/block
On Error GoTo Err_CheckCoating


'This function Checks to see if tray was coated prior to wash/block
Dim rstCoated As DAO.Recordset
Dim rstWashed As DAO.Recordset
Dim Response As String
Dim Msg As String
Dim ID As String
Dim flag1 As Integer

Dim recordidX As Integer
Dim trayidX As String
Dim lotnumberX As String
Dim partnumberX As String
Dim OperatornameX As String
Dim starttimeX As Date
Dim endtimeX As Date
Dim endoperatorX As String
Dim approvedX As Boolean
Dim coatedX As Boolean
Dim coatopX As String
Dim coattmX As Date

'set variables to empty

recordidX = Empty
trayidX = Empty
lotnumberX = Empty
partnumberX = Empty
OperatornameX = Empty
starttimeX = Empty
endtimeX = Empty
endoperatorX = Empty
approvedX = Empty
coatedX = Empty
coatopX = Empty
coattmX = Empty
flag1 = 0

'Setup recordset
Set rstCoated = CurrentDb.OpenRecordset("qryCoatedTrays")
Set rstWashed = CurrentDb.OpenRecordset("qryWashedTrays")

'Create a transaction to prevent data loss

Workspaces(0).BeginTrans

'Check for data

If rstCoated.BOF = True Then
    MsgBox "There are no coated trays"
    Workspaces(0).Rollback
    Exit Function
End If

rstCoated.MoveFirst

Do Until rstCoated.EOF
    If rstCoated!TrayID = Forms![frmScanTray]![txtTrayID] Then
         
         With rstCoated
         recordidX = !RecordID
         trayidX = !TrayID
         lotnumberX = !LotNumber
         partnumberX = !PartNumber
         OperatornameX = CurrentUser()
         coatedX = !Coated
         coatopX = !CoatOp
         coattmX = !CoatTM
         End With
         
         starttimeX = Now()
         endtimeX = Empty
         endoperatorX = Empty
         approvedX = False
         
         With rstWashed
         
            .AddNew
            
            !RecordID = recordidX
            !TrayID = trayidX
            !LotNumber = lotnumberX
            !PartNumber = partnumberX
            !OperatorName = OperatornameX
            !StartTime = starttimeX
            !EndTime = endtimeX
            !EndOperator = endoperatorX
            !Approved = approvedX
            !Coated = coatedX
            !CoatOp = coatopX
            !CoatTM = coattmX
            
            .Update
            
    End With
         
         flag1 = flag1 + 1
         
         Exit Do
        
        End If
    
    rstCoated.MoveNext
    
 Loop
 
If flag1 = 0 Then
    MsgBox "Tray not coated"
    Workspaces(0).Rollback
    Exit Function
End If
   
Exit_CheckCoating:
    Exit Function
            
Err_CheckCoating:
    Workspaces(0).Rollback
    MsgBox Err.Description
    
End Function

Select allOpen in new window