.Close and "Operation is not allowed..."

[E Hines]

I'm running an Access 2000 ADO database. I've seen other
discussions on this error, but those solutions don't seem
to help. I'm getting the Error 3219 on the .Close line at
the end of the following block:

With targetRangeCTGT
.Open "SELECT * FROM BSP_Value", connCTGT,
adOpenKeyset, adLockOptimistic

.AddNew
!Port_Name = name       'Could be written
as .Fields("Stock_Sym") = StockSym
!Tot_Value = value
!Week_Ending = Now()
.Update
.MoveFirst
origValue = .Fields("Orig_Value").value
If .Fields("Orig_Value").value = 0 Then     'check
to see if original value has already been loaded
.Fields("Orig_Value").value = .Fields
("Tot_Value").value    'It wasn't, so load it
origValue = .Fields("Orig_Value").value
Else
.MoveLast       'It was, so use the original
value to calculate percent change since then in last record
.Fields("Prcnt_Change").value = (.Fields
("Tot_Value").value - origValue) / origValue
.Update
End If
.Close
End With

The offending code seems to be the If...Then...Else block--
if I comment out that section, then this code runs fine.
The problem, though, is that I need the If...Then...Else
block (or something similar) so that when I guts up the
table with an initial set of data (as when a portfolio is
first created), I can have the Orig_Value field
automatically populated with the first Tot_Value, and then
this one-time entry of the Orig_Value value, from the
first record of this growing table, can then be used to
calculate the per cent change of the port from that value
in all subsequent updates.

Sorry for the long-ish post, and I appreciate any help
that can be provided.

Eric Hines

 

[Dirk Goldgar]

I'm running an Access 2000 ADO database. I've seen other
discussions on this error, but those solutions don't seem
to help. I'm getting the Error 3219 on the .Close line at
the end of the following block:

With targetRangeCTGT
.Open "SELECT * FROM BSP_Value", connCTGT,
adOpenKeyset, adLockOptimistic

.AddNew
!Port_Name = name       'Could be written
as .Fields("Stock_Sym") = StockSym
!Tot_Value = value
!Week_Ending = Now()
.Update
.MoveFirst
origValue = .Fields("Orig_Value").value
If .Fields("Orig_Value").value = 0 Then     'check
to see if original value has already been loaded
.Fields("Orig_Value").value = .Fields
("Tot_Value").value    'It wasn't, so load it
origValue = .Fields("Orig_Value").value
Else
.MoveLast       'It was, so use the original
value to calculate percent change since then in last record
.Fields("Prcnt_Change").value = (.Fields
("Tot_Value").value - origValue) / origValue
.Update
End If
.Close
End With

The offending code seems to be the If...Then...Else block--
if I comment out that section, then this code runs fine.
The problem, though, is that I need the If...Then...Else
block (or something similar) so that when I guts up the
table with an initial set of data (as when a portfolio is
first created), I can have the Orig_Value field
automatically populated with the first Tot_Value, and then
this one-time entry of the Orig_Value value, from the
first record of this growing table, can then be used to
calculate the per cent change of the port from that value
in all subsequent updates.

Sorry for the long-ish post, and I appreciate any help
that can be provided.

Eric Hines

From the looks of things, your code may modify a field in the recordset
here:

If .Fields("Orig_Value").value = 0 Then 'check
to see if original value has already been loaded
.Fields("Orig_Value").value = .Fields
("Tot_Value").value 'It wasn't, so load it
origValue = .Fields("Orig_Value").value

without issuing a call to the .Update method before it calls the .Close
method. As you see, the call to .Update appears only in the "Else"
block.

 

[E Hines]

-----Original Message-----

I'm running an Access 2000 ADO database. I've seen other
discussions on this error, but those solutions don't seem
to help. I'm getting the Error 3219 on the .Close line at
the end of the following block:

With targetRangeCTGT
.Open "SELECT * FROM BSP_Value", connCTGT,
adOpenKeyset, adLockOptimistic

.AddNew
!Port_Name = name       'Could be written
as .Fields("Stock_Sym") = StockSym
!Tot_Value = value
!Week_Ending = Now()
.Update
.MoveFirst
origValue = .Fields("Orig_Value").value
If .Fields("Orig_Value").value = 0 Then     'check
to see if original value has already been loaded
.Fields("Orig_Value").value = .Fields
("Tot_Value").value    'It wasn't, so load it
origValue = .Fields("Orig_Value").value
Else
.MoveLast       'It was, so use the original
value to calculate percent change since then in last record
.Fields("Prcnt_Change").value = (.Fields
("Tot_Value").value - origValue) / origValue
.Update
End If
.Close
End With

The offending code seems to be the If...Then...Else block--
if I comment out that section, then this code runs fine.
The problem, though, is that I need the If...Then...Else
block (or something similar) so that when I guts up the
table with an initial set of data (as when a portfolio is
first created), I can have the Orig_Value field
automatically populated with the first Tot_Value, and then
this one-time entry of the Orig_Value value, from the
first record of this growing table, can then be used to
calculate the per cent change of the port from that value
in all subsequent updates.

Sorry for the long-ish post, and I appreciate any help
that can be provided.

Eric Hines

From the looks of things, your code may modify a field in the recordset
here:

If .Fields("Orig_Value").value = 0 Then 'check
to see if original value has already been loaded
.Fields("Orig_Value").value = .Fields
("Tot_Value").value 'It wasn't, so load it
origValue = .Fields("Orig_Value").value

without issuing a call to the .Update method before it calls the .Close
method. As you see, the call to .Update appears only in the "Else"
block.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Aaargh! I'm glad someone is paying attention. That
solved the problem. Thanks for your help.

Eric Hines