With CurrentDB.Execute "UPDATE ... getting Object Required erro

[Guest]

Code is below

DoCmd.OpenQuery "ADT-forUpdate_2", acViewNormal, acEdit 'Creates a
table from a query

DoCmd.OpenTable "Original_Begin_Balance", acViewNormal, acReadOnly
'Table created by query

DoCmd.GoToRecord acTable, "Original_Begin_Balance", acFirst

DoCmd.GoToControl "Begin Balance" 'Will set temporary value to this
begin balance

CurrentDb.Execute "UPDATE OrigBeginBalCache SET OrigBeginBal = " _
& Original_Begin_Balance.[Begin Balance], dbFailOnError

gets as far as the last statement and then I get the error message. any
ideas?

 

[Tim Ferguson]

CurrentDb.Execute _
"UPDATE OrigBeginBalCache " & _
"SET OrigBeginBal = " & Original_Begin_Balance.[Begin Balance], _
dbFailOnError

I cannot see where you have defined any object Original_Begin_Balance. You
seem to have opened various user interface windows (which vba knows nothing
about, of course), but this is implying a real variable of some type.

You probably could get the value you want with a

DFirst("[Begin Balance]", "[Original_Begin_Balance]")

but to be honest, I'd redo the whole thing using normal database access.
Where does this [Begin Balance] actually live in the real tables -- why
can't you just look it up there?

HTH


Tim F

 

[Steve Schapel]

Jsccorps,

You can't refer to the value of a field in a table in this way... The
syntax in invalid, but even if it was valid, Access would have no way of
knowing which record in the table you are referring to. You could
eliminate the OpenTable, GoToRecord, and GoToControl methods from your
code, they achieve nothing. And then you could do something like this...

CurrentDb.Execute "UPDATE OrigBeginBalCache SET OrigBeginBal = " _
& DFirst("[Begin Balance]","Original_Begin_Balance"), dbFailOnError

Mind you, this is using your concept of the first record, which would
generally be an unreliable way of getting what you want, as the data in
a table is in no particular order, so the First or DFirst is a bit random.