Problem with copying record to history table

[jereece]

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"
[completion date].SetFocus
Exit Sub
End If

If IsNull(Combo116.Column(0)) = True Then
MsgBox "Don't have a frequency field", vbExclamation, "Need frequency info"
Combo116.Dropdown
Exit Sub
End If


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
End If



DoCmd.SetWarnings True ' Set warnings on




Button69_Exit:

Exit Sub




Button69_Err:

intErrNum = Err
Select Case intErrNum
Case 2105
MsgBox "This is a new task. Need more data to complete it", vbExclamation, "Need more data"
Case Else
MsgBox "Error!"

End Select
Resume Button69_Exit
End Sub

 

[FoFa]

REF: Insert statement: Well I think you either need a from in your select or, Me. in front of you variables so it knows it is coming from the form.

 

[jereece]

I am not much of a programmer , so can you give me an example of what you are talking about? I got a lot of help developing the existing code from some folks on this forum.

I appreciate the help.

Jim

 

[FoFa]

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;"

Where are the values [TASK NUMBER], [SUPERINTENDENT] etc. actually coming from?

If it is trying to pull them from form fields, then you need to use the syntax Me.[TASK NUMBER] and Me.[SUPERINTENDENT] for your fields. How ever it is pulling this information from another table then you need a FROM tablename and WHERE some criteria to select the row/s you require.

 

[jereece]

It might be easier if you just look at my database, so I have attached it. The button that launches this code is on the form called TASK FORM. It's the button called TASK COMPLETED. The fields TASK NUMBER, SUPERINTENDEND, etc. are names of the fields in a table called TASK which this form is reading from. So I am trying to copy the record to the HISTORY table, then reset the start and due dates based on the frequency, and finaly clear the completion date field.

I really appreciate any help.

Jim

 

[FoFa]

Ok, I took a different approach, but see if it wil work or gives you any ideas.

 

[jereece]

That works great! Thank you for the help. I really, really appreciate it!

Jim