Run SQL Update Not Working

[djs]

I have never used access before and started working with it a couple months ago and have gotten a very good understanding on how it works but now I have been trying to learn some coding. This is my problem:

I have a table (tbl_events) which contains training records for some employees. It contains both old events and upcoming training events. I have 2 querys qry_oldevents, qry_schdevents which just seperates the data based on a checkbox in the tbl_events. If its checked then it is completed and if not then it is an upcoming event. I then have 2 subforms one for old and one for upcoming events. In the upcoming events subform I have a checkbox for each record and when clicked it asks if it was completed and the date it was completed. I would just like this date that the user puts in to be placed in the CompletedDate field in the tbl_events for that record. This is the code I am using:

Private Sub Completed__Click()
Dim strCompDate As String
If MsgBox("Has This Been Completed?", vbYesNo) = vbYes Then
Do
strCompDate = InputBox("Enter completion date:", "Completion Date?", Format(Now(), "Short Date"))

If strCompDate = "" Then Me.Completed_.Value = False
If strCompDate = "" Then MsgBox "GoodBye"
If strCompDate = "" Then Exit Sub
If IsDate(strCompDate) = False Then MsgBox "Incorrect date format -- please re-enter!!"

Loop Until IsDate(strCompDate)

DoCmd.RunSQL ("UPDATE tbl_events SET tbl_events.completeddate = ' " & strCompDate & " ' WHERE tbl_events.course = ' " & Me.Course & " ' ")

Else
Me.Completed_.Value = False
End If
End Sub

I dont get any error messages or anything but the date just does not get put in. I also deleted the WHERE part to see if it would update everything and it updates the completed date for every other record except the one I want. There may be easier ways to do this but I really just started trying to learn this about a week or two ago so any help or tips are appreciated. Thanks

 

[pbaldy]

Your completeddate field must be text rather than date/time? If it updates every other record but the displayed record, my guess is the record isn't committed yet. Try this to force a save before running the SQL:

If Me.Dirty Then Me.Dirty = False

 

[djs]

Ya I wasnt sure about using the string or date/time but I tried what you had said and that code worked perfectly. Thank you for the quick reply and help I appreciate it.

 

[pbaldy]

No problem. Personally I'd use the date/time data type to store a date. It will be much easier if and when you want to do any type of calculation or date manipulation. If you make that change, the code would change to:

DoCmd.RunSQL ("UPDATE tbl_events SET tbl_events.completeddate = #" & strCompDate & "# WHERE tbl_events.course = ' " & Me.Course & " ' ")