Updating table from command button

[mandls]

I have a form that I added a command button to. If the Data_Start is populated then I need to update data in a table.

Here is the code:

Private Sub AddIntakeGoals_Click()
On Error GoTo Err_AddIntakeGoals_Click


Dim db As Database


Set db = CurrentDb



If (Me!Date_Start) Is Not Null Then


Dim MyDate As Date
Dim strSQL As String
Dim strTrGoal As String
Dim strGoalA As String
Dim strGoalB As String
MyDate = Date
strTrGoal = "To participate in the therapeutic process"
strGoalA = "Client will complete intake paperwork including completing a social history"
strGoalB = "Client will assist therapist in developing at least one individualized treatment goal"

strSQL = "INSERT INTO tblGoal (GoalIDStaff,Treatment_Goal, " _
& " GoalA,GoalB,Goal_Number,GoalA_Date,GoalB_Date,Updated) " _
& " SELECT tblGoalStaff.GoalIDStaff, " _
& " strTrGoal As Treatment_Goal, " _
& " strGoalA As GoalA, " _
& " strGoalB As GoalB, " _
& " 1 As Goal_Number, #" & MyDate & "# As GoalA_Date, " _
& " #" & MyDate & "# As GoalB_Date, #" & MyDate & "# As Updated " _
& " FROM (tblGoal inner join tblGoalStaff on tblGoal.GoalIDStaff = tblGoalStaff.GoalIDStaff)"



CurrentDb.Execute strSQL, dbFailOnError
End If


Exit_AddIntakeGoals_Click:
Exit Sub

Err_AddIntakeGoals_Click:
MsgBox Err.Description
Resume Exit_AddIntakeGoals_Click

End Sub
I keep getting an object required Access error message. Also when I tested this without the Date_Start it updated all records in my table not just the current record like I need. Luckily I am in a test database. I am down to the wire on this. Any help would be really appreciated.

 

[PHV]

Replace this:
If (Me!Date_Start) Is Not Null Then
with this:
If Not IsNull(Me!Date_Start) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mandls]

Made the change and now getting the message "Too few parameters. Expected 3.

 

[PHV]

Replace this:
& " strTrGoal As Treatment_Goal, " _
& " strGoalA As GoalA, " _
& " strGoalB As GoalB, " _
with this:

& "'" & strTrGoal & "' As Treatment_Goal, " _
& "'" & strGoalA & "' As GoalA, " _
& "'" & strGoalB & "' As GoalB, " _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya mandls . . .

Correcting and formatting for easier reading, I come up with:

[blue]   Dim db As Database, MyDate As Date, SQL As String
   Dim strTrGoal As String, strGoalA As String, strGoalB As String

   Set db = CurrentDb
   MyDate = Date
   strTrGoal = "To participate in the therapeutic process"
   strGoalA = "Client will complete intake paperwork including completing a social history"
   strGoalB = "Client will assist therapist in developing at least one individualized treatment goal"
        
   If Not IsNull(Me!Date_Start) Then
      SQL = "INSERT INTO tblGoal (GoalIDStaff, " & _
                                 "Treatment_Goal, " & _
                                 "GoalA, " & _
                                 "GoalB, " & _
                                 "Goal_Number, " & _
                                 "GoalA_Date, " & _
                                 "GoalB_Date, " & _
                                 "Updated) " & _
            "SELECT tblGoalStaff.GoalIDStaff, " & _
                   "strTrGoal As Treatment_Goal, " & _
                   "strGoalA As GoalA, " & _
                   "strGoalB As GoalB, " & _
                   "1 As Goal_Number, " & _
                   "#" & MyDate & "# As GoalA_Date, " & _
                   "#" & MyDate & "# As GoalB_Date, " & _
                   "#" & MyDate & "# As Updated " & _
            "FROM tblGoal " & _
            "INNER JOIN tblGoalStaff " & _
            "ON tblGoal.GoalIDStaff = tblGoalStaff.GoalIDStaff;"
   db.Execute SQL, dbFailOnError

   set db = Nothing
 End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]