Problems with CurrentDb.Execute "UPDATE

[GraeagleBill]

In the following function, the first of the two "Updates" seems to execute normally as viewed in debug, but the table "APPENDAGES" table DOES NOT in fact get updated per the criteria. The function code resides in the parent and the right-click is upon a control in the child. In debug, execution DOES NOT proceed beyond the second of the two "Updates", but no diagnostic is issued. The current values for "IndividualsID", "I" and the global variable "gblRetreatYear" are verified in debug to be valid. In the second of the two updates, the field to be updated in table "GROUPINGS" should evaluate to "ID1", as the current value of "I" is 1.



1) I don't know why "APPENDAGES" table does not update?
2) Why the second update does not issue an error message if the statement is invalid?

Your thoughts please.

Thanks,
Bill


Public Function Remove()
Dim CtlName As String 'Name of text-box just right-clicked (Text box is unbounded)
Dim IndividualsID As Integer 'Roster RosID of individual currently assigned to the group, as stored in IDx.
Dim I As Integer 'Essentially the index value identifying which of the 6 ID's in Groupings was detected.
Dim strtemp As String

CtlName = Screen.ActiveControl.name 'Name of text box right-clicked
I = Right(CtlName, 1) 'Which ID in Groupings

IndividualsID = Me.Child58.Form("ID" & I)

'Update Appendages first
CurrentDb.Execute "UPDATE APPENDAGES SET GroupID = Null WHERE AppID = " & IndividualsID & " AND RetYear = '" & gblRetreatYear & "';"

'Now Update Groupings
CurrentDb.Execute "UPDATE GROUPINGS SET ID" & I & " = Null WHERE AppID = " & IndividualsID & " AND RetYear = '" & gblRetreatYear & "';"

SelectID = IndividualsID 'Poised to put individual into a different room

End Function

[GraeagleBill]

I found the problem with the 2nd update, but the first update to "APPENDAGES" continues to be un-effective.

[pbaldy]

Two thoughts. You can use this technique to debug and test the SQL:

http://www.baldyweb.com/ImmediateWindow.htm

second, add

, dbFailOnError

to the end of the executes so you get an error. Sometimes you don't want one, so it's optional.

[GraeagleBill]

Yes, I use the immediate window often if there's some question as to how some of these involved expressions are formatting.

There was no error issued for the 1st of the updates following the inclusion of the ", dbFailOnError". After much "head scratching", I discovered what appeared to be an "un-updated" APPENDAGES table was due to having looked at a record that pertained to a different year. Everything seems to be working okay now.

Thanks,
Bill

[pbaldy]

No problem.