Updating a field using 'on click' event procedure (access 2000)

[Guest]

I get the wonderful task of 'fixing' a database where the original author is
no longer available.
Among other things, this database prints out one of several form letters and
is then supposed to update a field "DateLetterSent" with the current date.

The letters print just fine, but the date doesn't get updated, and I cannot
see what is missing or in error here.

This is the code used for this procedure:


Private Sub cmdFormLetters_Click()

On Error Resume Next

DoCmd.OpenReport "rptLetter1", acViewNormal
DoCmd.OpenReport "rptLetter2", acViewNormal
DoCmd.OpenReport "rptLetter3", acViewNormal
DoCmd.OpenReport "rptLetter4", acViewNormal
DoCmd.OpenReport "rptLetter5", acViewNormal
DoCmd.OpenReport "rptLetter6", acViewNormal
DoCmd.OpenReport "rptLetter7", acViewNormal

Dim dbs As Database
Dim sSql1 As String
Dim sSql2 As String
Dim sSql3 As String
Dim sSql4 As String
Dim sSql5 As String
Dim sSql6 As String
Dim sSql7 As String
Dim MyDate As Date

Set dbs = CurrentDb

DoCmd.RunCommand acCmdSaveRecord
sSql1 = "UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] =
1 AND IsNull(DateLetterSent)"
sSql2 = "UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] =
2 AND IsNull(DateLetterSent)"
sSql3 = "UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] =
3 AND IsNull(DateLetterSent)"
sSql4 = "UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] =
4 AND IsNull(DateLetterSent)"
sSql5 = "UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] =
5 AND IsNull(DateLetterSent)"
sSql6 = "UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] =
6 AND IsNull(DateLetterSent)"
sSql7 = "UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] =
7 AND IsNull(DateLetterSent)"

dbs.Execute sSql1
dbs.Execute sSql2
dbs.Execute sSql3
dbs.Execute sSql4
dbs.Execute sSql5
dbs.Execute sSql6
dbs.Execute sSql7

End Sub


As stated the correct letter(s) print, but the 'DateLetterSent' doesn't get
updated. Any help or suggestions?

 

[Allen Browne]

The Execute method fails silently if you do not use the dbFailOnError
switch, e.g.:
dbs.Execute sSql1, dbFailOnError

You can also trace whether anything was in fact changed by a line like this
after each execute:
Debug.Print dbs.RecordsAffected " record(s) changed for letter 1."

If the letter has already been sent, presumably DateLetterSent will not be
null, and so the criteria will preclude it being redated.

 

[Guest]

Thank you for the quick reply.

I've added the dbFailOnError and the Debug.print to the code and printed
these letters again. I didn't get any errors, which presumably indicates that
there were none. Also I didn't get anything for the debug.print command.

I still didn't get the 'DateLetterSent' field updated either and this field
is null for the records in question (the query that selects the records to be
printed checks for null values in this field, and these records still list in
that query).

 

[Alex Dybenko]

Hi,
try to comment out On Error Resume Next at the beginning of proc

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Allen Browne]

After running the code, if you opened the Immediate Window (Ctrl+G) and saw
no output, then Debug.Print commands that you added after each Execute
statement did not run.

This indicates that the procedure is not running to completion. Open it in
design view, place the cursor in the first Execute line, and press F9. This
inserts a break point. When you click the button, the execution will break,
highlighting the line that is about to run in yellow. You can now press F8
to single-step through the lines, and see where it drops out.

 

[Guest]

Haha!

also "Doh!"

I'm getting "data type mismatch in criteria expression". Previously, this
field was marked as 'text' and I changed it to 'date/time' with a format of
dd/mm/yyyy. As a result I had to re-do the queries which select the records
to print, but I didn't re-type the commands listed in the code.

BTW I did get a message in the ctrl G window, it said "0 records updated
for letter 5". I wasn't sure where to look before.

Thank you for your time and consideration, I believe that I can take it from
here.

 

[JM]

Try this sql statement

UPDATE tblPIR SET DateLetterSent = DATE() WHERE [FormLetter#] = 1 AND
DateLetterSent is NULL