SQL UPDATE Query doesn't Update Table

A

Andy6

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
 
G

Gary Walter

Andy6 said:
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
Click to expand...

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
 
A

Andy6

Gary Walter said:
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
Click to expand...
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.
 
G

Gary Walter

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

"Andy6"wrote:
Gary Walter said:
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
Click to expand...
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.
Click to expand...
 
A

Andy6

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

export vba not working 0
VBA works in orignal but fails in copy of database 1
Update Query Problem 2
SQL Syntax Error - UPDATE command 1
Error 3144 - Syntax error in UPDATE Statement 6
Run-time error '3265' 19
assigning a query to an array 5
VBA query help - I've never done this before... 2

Top