SQL UPDATE Query doesn't Update Table

Can anyone see why my Update query doesn't Update the table 'DiaryDates'
This function is called on the 'ON EXIT' event of each text box on my 8 day
diary form. Using a break point I can see that all the correct values are in
strNote and strDate but it still doesn't update my table. Code is as follows:

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F%
Set db = CurrentDb
strSQL = "UPDATE Diarydates SET DiaryNote = strNote WHERE DiaryDate =
strDate"

For F = 1 To 8
strDate = Forms![Diary]("Dt" & F)
strNote = Forms![Diary]("Note" & F)
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
qd.Close
Next F

End Function
Any help would be much appreciated,
Many Thanks in advace, Andy

"Andy6" wrote:
Can anyone see why my Update query doesn't Update the table 'DiaryDates'
This function is called on the 'ON EXIT' event of each text box on my 8
day
diary form. Using a break point I can see that all the correct values are
in
strNote and strDate but it still doesn't update my table. Code is as
follows:

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F%
Set db = CurrentDb
strSQL = "UPDATE Diarydates SET DiaryNote = strNote WHERE DiaryDate =
strDate"

For F = 1 To 8
strDate = Forms![Diary]("Dt" & F)
strNote = Forms![Diary]("Note" & F)
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
qd.Close
Next F

End Function

Best guess...

Public Function UpdateDiary()
On Error GoTo Err_UpdateDiary
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database
Dim F As Integer

Set db = CurrentDb

For F = 1 To 8
If IsDate(Forms![Diary]("Dt" & F)) Then
strDate = Format(Forms![Diary]("Dt" & F),"mm\/dd\/yyyy")
Else
'don't attempt this one
'(unless you have some "default" date want to use)
GoTo NextF
End If
strNote = Forms![Diary]("Note" & F) & ""
strSQL = "UPDATE Diarydates SET DiaryNote = '" & strNote & "' " _
& "WHERE DiaryDate = #" & strDate & "#"
db.Execute strSQL, dbFailOnError
NextF:
Next F

Exit_UpdateDiary:
Exit Function

Err_UpdateDiary:
MsgBox Err.Description
Resume Exit_UpdateDiary
End Function

"Gary Walter" wrote:
Best guess...

Public Function UpdateDiary()
On Error GoTo Err_UpdateDiary
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database
Dim F As Integer

Set db = CurrentDb

For F = 1 To 8
If IsDate(Forms![Diary]("Dt" & F)) Then
strDate = Format(Forms![Diary]("Dt" & F),"mm\/dd\/yyyy")
Else
'don't attempt this one
'(unless you have some "default" date want to use)
GoTo NextF
End If
strNote = Forms![Diary]("Note" & F) & ""
strSQL = "UPDATE Diarydates SET DiaryNote = '" & strNote & "' " _
& "WHERE DiaryDate = #" & strDate & "#"
db.Execute strSQL, dbFailOnError
NextF:
Next F

Exit_UpdateDiary:
Exit Function

Err_UpdateDiary:
MsgBox Err.Description
Resume Exit_UpdateDiary
End Function

Thanks Gary Your'e pointing me in the right direction but not yet
successful.below is my revised Function and the result of a Debug line. All
seems to be right but the underlying form doesn't get updated if I change
values in the text boxes on the Diary Form.

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strDate = Format(Forms![Diary]("Dt" & F), "dd\/mm\/yy")
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE Diarydates SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & strDate & "#"
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
Debug.Print strSQL
qd.Close
Next F

End Function

Debug print out:

UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #19/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #20/02/07#
UPDATE Diarydates SET DiaryNote = 'Lunch with John 12:00' WHERE DiaryDate =
#21/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #22/02/07#
UPDATE Diarydates SET DiaryNote = 'Dentist 3pm' WHERE DiaryDate = #23/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #24/02/07#
UPDATE Diarydates SET DiaryNote = 'Football 2:30 at Club with Brian' WHERE
DiaryDate = #25/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
Any Thoughts???
Andy.

Sorry Gary I meant the underlying Table, Not form.
Andy

Queries expect your dates formatted in US mm/dd/yyyy

"Andy6"wrote:
"Gary Walter" wrote:
Best guess...

Public Function UpdateDiary()
On Error GoTo Err_UpdateDiary
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database
Dim F As Integer

Set db = CurrentDb

For F = 1 To 8
If IsDate(Forms![Diary]("Dt" & F)) Then
strDate = Format(Forms![Diary]("Dt" & F),"mm\/dd\/yyyy")
Else
'don't attempt this one
'(unless you have some "default" date want to use)
GoTo NextF
End If
strNote = Forms![Diary]("Note" & F) & ""
strSQL = "UPDATE Diarydates SET DiaryNote = '" & strNote & "' " _
& "WHERE DiaryDate = #" & strDate & "#"
db.Execute strSQL, dbFailOnError
NextF:
Next F

Exit_UpdateDiary:
Exit Function

Err_UpdateDiary:
MsgBox Err.Description
Resume Exit_UpdateDiary
End Function

Thanks Gary Your'e pointing me in the right direction but not yet
successful.below is my revised Function and the result of a Debug line.
All
seems to be right but the underlying form doesn't get updated if I change
values in the text boxes on the Diary Form.

Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String, strDate As Date
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strDate = Format(Forms![Diary]("Dt" & F), "dd\/mm\/yy")
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE Diarydates SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & strDate & "#"
Set qd = db.CreateQueryDef("UpdtDiaryNte", strSQL)
qd.Execute
Debug.Print strSQL
qd.Close
Next F

End Function

Debug print out:

UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #19/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #20/02/07#
UPDATE Diarydates SET DiaryNote = 'Lunch with John 12:00' WHERE DiaryDate
=
#21/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #22/02/07#
UPDATE Diarydates SET DiaryNote = 'Dentist 3pm' WHERE DiaryDate =
#23/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #24/02/07#
UPDATE Diarydates SET DiaryNote = 'Football 2:30 at Club with Brian' WHERE
DiaryDate = #25/02/07#
UPDATE Diarydates SET DiaryNote = '' WHERE DiaryDate = #26/02/07#
Any Thoughts???
Andy.

Gary, many thanks for your patience and excelent help and thanks to Rick in
PROGRAMMING section: Between you, I came up with this version and it works
perfectly.
Public Function UpdateDiary()
On Error Resume Next
Dim strSQL As String
Dim strNote As String
Dim db As DAO.Database, qd As DAO.QueryDef
Dim F As Integer
Set db = CurrentDb

For F = 1 To 8
strNote = Forms![Diary]("Note" & F)
strSQL = "UPDATE DiaryNotes SET DiaryNote = '" & strNote & "' WHERE
DiaryDate = #" & Format(Forms![Diary]("Dt" & F), "mm\/dd\/yyyy") & "#"
db.Execute strSQL
Next F
End Function

now on to the next problem!! Don't you just love it.
Many Many Thanks.
Andy.