ERROR- Update' or CancelUpdate without AddNew or Edit

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I was editing a proceedure for a form and I started getting this error when I
attempt to navigate on my form in any way.

The proceedure (current) simply requeried a listbox and then set it's
selected property. When I commented out the lines in the proceedure or even
deleted the proceedure, the error wouldn't go away.

What causes this error?
 
Somewhere in code that is running is an Update statement for a recordset
that doesn't have an AddNew or an Edit statement preceding it in the code.
Likely inadvertent commenting out of some code lines, or accidental deletion
of some code lines.

Do a "Find" for the text string ".Update" in your modules and see if you can
find the offending code snippet.
 
Additionally, you might find it quickly if you click on Debug | Compile
while in the VBEditor. That will identify such a syntax error for you and
will take you right to the offending code.
 
Ken-

Thanks for your rapid reply!

There is an .update statement in another procedure for the form, but it is
preceeded by an .AddNew statement. I get the same error even if I comment out
the .update and .AddNew. In fact, I get the error when I try to switch from
form view to design view (using the Form menu or the button) in my form.

It is as if Access itself had an .update statement without an .AddNew in its
code for using the form!

Any ideas?
 
Ken--

I managed to delete the proceedure that I had been working on when this
happened and made the error go away. But when I add the proceedure back in, I
get the error again after I navigate to the next record. The offending
proceedure is:

Private Sub Form_Current()
' when the form goes to a new record:
' requery the groups control
Me.groups.Requery
' select the first item in the groups control
Me.groups.Selected(0) = True
End Sub

"controls" is a listbox whose recordset is a query as a SQL statement. The
query has as a criteria a field on the form that is bound to a field in the
form's current table.

How is this proceedure giving me that error? What is wrong with this
proceedure?

Thanks!
 
This code, as you've concluded, should not be a source of the error. It's
possible that the compiled code has become confused in the form's module. Or
that the form has become corrupted.

When you deleted this procedure, did you close the form (saving the changes,
of course), and then do a compact/repair on the database? If not, try that
(keep copy of the procedure that you delete). Then put the procedure back
into the form's module (tie it to the OnCurrent property via [Event
Procedure], of course). See if that eliminates the problem.

If it doesn't you might try importing the form into a new database and
delete it from the current database. Compact/repair the current database.
Then reimport the form from the new database back into the original
database.

If these don't work, post back and we'll see what other options we might
try.
 
Me.groups.Selected(0) = True

"controls" is a listbox whose recordset is a query as a SQL statement.
Click to expand...

One of these is a typo... if the listbox is called "controls" then that is
going to be the source, because

Me.Controls.Selected()

definitely means the wrong thing!

Just a thought


Tim F
 
Hi,

selecting of row in listbox depends on two listbox's properties: MultiSelect
and ColumnHeads.

For MultiSelect set to None:
'check, if there are any rows to choose from
lngCount = Me.groups.ListCount
If Me.groups.ColumnHeads Then 'column heads costs one row...
lngCount = lngCount - 1
End If
If lngCount <= 0 Then
MsgBox "There is no item to select!", vbExclamation
Exit Sub '..or whatever you want
End If

' now select first row below heads if any
' If uses ColumnHeads, the first row is the heads
If Me.groups.ColumnHeads Then
l = 1
Else
l = 0
End If
Me.groups.Value = (Me.groups.Column(Me.groups.BoundColumn - 1, l) + 0)


For MultiSelect set to Simple/Extended:
If Me.groups.ColumnHeads Then ' If uses ColumnHeads, the first row is
the heads
l = 1
Else
l = 0
End If
Me.groups.Selected(l) = True


Pavel K.


Don Starnes said:
Ken--

Thanks for your help!

Repairing, deleting and reimporting didn't help. Through these measures and
further testing (adding the code for the [event propceedure] back one line at
a time), I have found that the error only occurs when the

Me.groups.Selected(0) = True

statment is included (yes, "groups" is the name of the control; I'm glad
that you knew what I meant ;--} ).

I'm trying, of course, to select the first item of the listbox after the
list is updated (I would then uxe the selected item to update other
controls). I see no reason why this line causes this error.

Is there something wrong with this statement?

Don

Ken Snell said:
This code, as you've concluded, should not be a source of the error. It's
possible that the compiled code has become confused in the form's module. Or
that the form has become corrupted.

When you deleted this procedure, did you close the form (saving the changes,
of course), and then do a compact/repair on the database? If not, try that
(keep copy of the procedure that you delete). Then put the procedure back
into the form's module (tie it to the OnCurrent property via [Event
Procedure], of course). See if that eliminates the problem.

If it doesn't you might try importing the form into a new database and
delete it from the current database. Compact/repair the current database.
Then reimport the form from the new database back into the original
database.

If these don't work, post back and we'll see what other options we might
try.
--

Ken Snell
<MS ACCESS MVP>

Don Starnes said:
Ken--

I managed to delete the proceedure that I had been working on when this
happened and made the error go away. But when I add the proceedure
Click to expand...
back
in, I
get the error again after I navigate to the next record. The offending
proceedure is:

Private Sub Form_Current()
' when the form goes to a new record:
' requery the groups control
Me.groups.Requery
' select the first item in the groups control
Me.groups.Selected(0) = True
End Sub

"controls" is a listbox whose recordset is a query as a SQL statement. The
query has as a criteria a field on the form that is bound to a field
Click to expand...
in
the
form's current table.

How is this proceedure giving me that error? What is wrong with this
proceedure?

Thanks!
:

Somewhere in code that is running is an Update statement for a recordset
that doesn't have an AddNew or an Edit statement preceding it in the code.
Likely inadvertent commenting out of some code lines, or accidental deletion
of some code lines.

Do a "Find" for the text string ".Update" in your modules and see if
Click to expand...
you
can
find the offending code snippet.

--

Ken Snell
<MS ACCESS MVP>



I was editing a proceedure for a form and I started getting this error
when I
attempt to navigate on my form in any way.

The proceedure (current) simply requeried a listbox and then set it's
selected property. When I commented out the lines in the proceedure or
even
deleted the proceedure, the error wouldn't go away.

What causes this error?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top