I spent a lot of time but still couldn't figure out the reason and solution. I appreciate any help.
TblStatutesFY only has records for 2017 fiscal year. I added an update button on a form in order to copy the 2017 records and create the records for 2018 in the tblStatutesFY. On the form, there is a ctrlFYSelector where I selected 2017 there before I click the btnUpdate. Below is the coding for the button. It always gives me the error message 3167 - record is deleted and it is not doing the intended job. The database is not corrupted.
Private Sub btnUpdate_Click()
IptBox = InputBox("Enter a fiscal year to update the statutes (e.g., 2018):")
IptMath = IptBox - Me.ctrlFYSelector
If Not IsNull(IptBox) Then
Call UpdateStatutes
End If
End Sub
Private Function UpdateStatutes()
Dim IptMath As String
'Copy the statutes into the specified fiscal year
strSQL = " INSERT INTO tblStatutesFY (StatuteID, FiscalYear, ComplianceRating, Probability, Actions) " _
& "SELECT StatuteID, FiscalYear + " & IptMath & ", ComplianceRating, Probability, Actions " _
& "FROM tblStatutesFY " _
& "WHERE tblStatutesFY.FiscalYear = " & ctrlFYSelector & ";"
DoCmd.RunSQL strSQL