I am developing an Access database to keep track of recurring tasks. When a task is completed, a date is entered and a
button clicked. The code then has the record copied to a history table, then resets the start and due dates based on the
frequency. I am getting an error when I run the code. The message just says "Error", so I am not sure what's wrong. Below
is the code for the button. I realize it's rather complex, so if anyone is willing to help and could PM me, I would be glad
to provide a link where you can download my project.
I really appreciate the help. Below is the code:
Private Sub Button96_Click()
'1. The record is copied to the "History" table for historical records.
'2. The Current Due Date changes to the next due date based on the recurring requirement specified
' (i.e. weekly, monthly, etc.), or if it is "one time", it deletes the record.
'3. If recurring, the Completed Date field goes blank (because it is reset).
On Error GoTo Button69_Err
Dim MyStr As String
Dim intErrNum As Integer
' Save the record first, by going to the next record, and then back to the current one.
' This is OK even if there is no next record, since it will go to a new record
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
If IsNull([completion date].Value) = True Then
MsgBox "The completion date is empty", vbExclamation, "Need completion date"
If IsNull(Combo116.Column(0)) = True Then
MsgBox "Don't have a frequency field", vbExclamation, "Need frequency info"
MyStr = "INSERT INTO History ( [OLD_ID#], [SUPERINTENDENT], [TASK DESCRIPTION], [REQUESTED_BY], [DUE DATE], [START DATE], "
"[COMPLETE DATE], [STATUS_COMMENTS], [COMMITTMENT], [GROUP], [INDIVIDUAL], [FREQUENCY] ) " & _
"SELECT " & Str([TASK NUMBER].Value) & " AS Expr1, " & Str([SUPERINTENDENT].Value) & " AS Expr2, " & _
Chr(34) & [TASK DESCRIPTION].Value & Chr(34) & " AS Expr3, " & Chr(34) & [REQUESTED_BY].Value & Chr(34) & " AS Expr4, " &
"#" & [Text128].Value & "#" & " AS Expr5, " & "#" & [START DATE].Value & "#" & " AS Expr6, " & _
"#" & [completion date].Value & "#" & " AS Expr7, " & Chr(34) & [STATUS_COMMENTS].Value & Chr(34) & " AS Expr8, " & _
Chr(34) & [COMMITTMENT].Value & Chr(34) & " AS Expr9, " & Chr(34) & Combo116.Column(0) & Chr(34) & " AS Expr10, " & _
Chr(34) & GROUP_COMBO.Column(0) & Chr(34) & " AS Expr11, " & Chr(34) & [INDIVIDUAL].Value & Chr(34) & " AS Expr12;"
DoCmd.SetWarnings False ' Set warning off
DoCmd.RunSQL MyStr ' Add current record to History table
If Combo116.Column(2) = 0 Then ' If the frequency is "One Time" or "Other", delete current record
'MsgBox "This record will be deleted"
MyStr = "Delete Task.[ID#] FROM Task WHERE (((Task.[ID#])=" & Str([TASK NUMBER].Value) & "));"
DoCmd.GoToRecord , , acNext ' Go to a new record
DoCmd.RunSQL MyStr ' Delete the record that was active before
Me.Requery ' Requery the form after a record is deleted
DoCmd.GoToRecord , , acFirst ' Go to the first record
Else ' Otherwise, it's a recurring task, so set its properties
'MsgBox "This record will be incremented with " & Str(Combo116.Column(2)) & " " & Combo116.Column(1)
Text128.Value = DateAdd(Combo116.Column(1), Combo116.Column(2), Text128.Value) ' increment the date, if recurring
[START DATE] = DateAdd(Combo116.Column(1), Combo116.Column(2), [START DATE])
[completion date].Value = Null ' Completion date goes blank
DoCmd.GoToRecord , , acNext ' save the record
DoCmd.GoToRecord , , acPrevious
DoCmd.SetWarnings True ' Set warnings on
intErrNum = Err
Select Case intErrNum
MsgBox "This is a new task. Need more data to complete it", vbExclamation, "Need more data"