Why does my update query appear to do nothing?

[Stapes]

Hi

I am running the following code:

Dim strsql As String

strsql = "UPDATE TM_Boat SET TM_Boat.FK_Customer = " & [Forms]!
[FM_ChangeBoatOwner]![Combo36] & _
" WHERE (((TM_Boat.PK_Boat)=" & [Forms]![FM_ChangeBoatOwner]!
[TXT_BoatNum] & "));"
Debug.Print strsql
CurrentDb.Execute strsql, dbFailOnError
MsgBox CurrentDb.RecordsAffected & " Records updated"

It keeps telling me "0 Records Updated", although it IS updating the
record!

Any idea why?

Stapes

 

[Roger Carlson]

Probably because the database instance created with CurrentDb.Execute is
destroyed after the line is complete and a new one is created with the
message box line.

It is a good practice (actually a Best Practice) to always create a database
variable and set it to the CurrentDb. It will help avoid problems like this
and doesn't take much longer. So change it to:

Dim db as DAO.Database
Set db = CurrentDb

strsql = "UPDATE TM_Boat SET TM_Boat.FK_Customer = " & [Forms]!
[FM_ChangeBoatOwner]![Combo36] & _
" WHERE (((TM_Boat.PK_Boat)=" & [Forms]![FM_ChangeBoatOwner]!
[TXT_BoatNum] & "));"

Debug.Print strsql
db.Execute strsql, dbFailOnError
MsgBox db.RecordsAffected & " Records updated"

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Dirk Goldgar]

In

It is a good practice (actually a Best Practice) to always create a
database variable and set it to the CurrentDb. It will help avoid
problems like this and doesn't take much longer.

I don't know that I agree with you, Roger, about it's being a Best
Practice, though it's certainly a good practice. But I will fairly
often write something like:

With CurrentDb
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
' ... do something or other ...
End If
End With

It seems clean and simple to me, without any need to create a separate
object variable.

 

[Roger Carlson]

The reason I consider it a Best Practice is not that using CurrentDb in the
way you did below is wrong or unclear. I believe it is Best Practice
because it keeps you from getting into the sort of trouble the Original
Poster had.

For instance, the code you posted (slightly modified):
With CurrentDb
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & .RecordsAffected
End If
End With

does work. However, this will not:

CurrentDb.Execute strSQL, dbFailOnError
If CurrentDb.RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & CurrentDb.RecordsAffected
End If

the WITH in your code keeps the database instance open, which makes it work.
In the second, a new database instance is created for each line. The
problem is that the two styles above can most often be used interchangably,
which can lead to the problems the OP had.

However, by creating a database variable,

Dim db As DAO.Database
Set db = CurrentDb
strSQL = "UPDATE ZipAddress SET ZipAddress.State = 'MI';"
With db
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & .RecordsAffected
End If
End With
Set db = Nothing

this problem is avoided and in fact both coding styles work identically. It
only took 3 extra lines of code to make certain you avoid nasty surprises
like this.

 

[Dirk Goldgar]

In

The reason I consider it a Best Practice is not that using CurrentDb
in the way you did below is wrong or unclear. I believe it is Best
Practice because it keeps you from getting into the sort of trouble
the Original Poster had.

For instance, the code you posted (slightly modified):
With CurrentDb
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & .RecordsAffected
End If
End With

does work. However, this will not:

CurrentDb.Execute strSQL, dbFailOnError
If CurrentDb.RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & CurrentDb.RecordsAffected
End If

.... reflecting a failure to understand how the CurrentDb method works.

the WITH in your code keeps the database instance open, which makes
it work.

Exactly. The With statement ensures that I am referring to the same
object throughout the block.

In the second, a new database instance is created for each
line. The problem is that the two styles above can most often be
used interchangably, which can lead to the problems the OP had.

However, by creating a database variable,

Dim db As DAO.Database
Set db = CurrentDb
strSQL = "UPDATE ZipAddress SET ZipAddress.State = 'MI';"
With db
.Execute strSQL, dbFailOnError
If .RecordsAffected = 0 Then
MsgBox "no records affected"
Else
MsgBox "Records Affected: " & .RecordsAffected
End If
End With
Set db = Nothing

this problem is avoided and in fact both coding styles work
identically. It only took 3 extra lines of code to make certain you
avoid nasty surprises like this.

From my point of view, understanding and using the With statement does
the exact same thing in a simpler way. If anything, I would encourage
using the With statement over defining a separate object variable,
wherever that is feasible. Of course, there are cases where multiple
objects are being manipulated in the same block of code, with no special
reason to assign one primacy over the other. Then then I do find it
clearer to use a separate object variable for each object.

We may have to agree to disagree on this point.

 

[Roger Carlson]

I can agree to that!