Me.ControlName = Something

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

MeSteve

I get an Update or CancelUpdate without Edit error any time I use code that
references a form control using me.controlname.

I was 'upgraded' from 2003 to 2007 when ther error started. It was
suggested that I check my reference libraries, all appeared OK. I also went
and tried my DB on a machine running 2003 and still get the errors.

What happened to my DB and how do I fix it?
 
Several things can cause this message, e.g.:
- timing of the event (when other events are triggered as a result),
- a wrong library reference,
- a Name AutoCorrect error,
- a bad VBA binary.

Where is this code called from? For example, if you use the KeyDown of a
control, and in that event you try to move to another control or record,
several other events are triggered before that complete. Before Access can
move to another control, it has to update the value in the control that has
focus. Before it can move to another record it also has to run the events of
the form (e.g. Form_BeforeUpdate.) If one of these events fail (e.g. the
record cannot be saved, or the control value is in appropriate), the
subsequent updates fail, so the current event fails with a message like the
one you posted.)

So, if your code contains anything that would require the record to be saved
(e.g. moving, filtering, sorting, requerying, closing), I encourage you to
include a line that explictly saves the record. My preferred approach is
like this:
If Me.Dirty Then Me.Dirty = False
If that fails, you get an error message letting you know that the Dirty
property could not be set (i.e. the save failed), and you can take the
appropriate action in your error handler. The line has the effect of
triggering the chain of events that must be cleared before you can continue
with whatever you wanted to do. And when it fails, it's much more obvious
what's going on than the quite oblique error message you experienced.

If that's not the issue, check that you have the correct references for your
version of Access:
http://allenbrowne.com/ser-38.html#LibVer

If you don't have Name AutoCorrect turned off, you probably want to do so
for lots of reasons:
http://allenbrowne.com/bug-03.html

For a bad binary, a decompile will help. Post back for details if you are
still stuck, or search for "decompile" in this page which deals with several
corruption issues:
http://allenbrowne.com/ser-47.html
 
I added the me.dirty line and now I get a run-time 2115 on the dirty = false,
but I have been told to put the timestamp code in the BeforeUpdate event.
Here is the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Save record
If Me.Dirty Then Me.Dirty = False

'Add date/time stamp for DateUpdated
Me.DateUpdated = Now
'CurrentDb.Execute "UPDATE tbl_Projects SET DateUpdated = Now()WHERE
ProjectID = " & Me.ProjectID 'ADDED BECAUSE ME.DATEUPDATED = NOW THROWS
ERROR

If IsNull(ProjectNameLong) = False Then

Do While Len(Trim(ProjectNameShort & "")) < 1
'Set ShortName value using input box
ShortName = InputBox("Please enter an abbreviated name in the
Project Short Name")
'Remove leading or trailing spaces
ShortName = Trim(ShortName & "")
'Set ProjectNameShort to ShortName
'Me.ProjectNameShort = ShortName
'fixes Me.ProjectNameShort = ShortName error, but causes write
error
CurrentDb.Execute "UPDATE tbl_Projects SET ProjectNameShort =
ShortName WHERE ProjectID = " & Me.ProjectID 'ADDED BECAUSE ME.DATEUPDATED
= NOW THROWS ERROR

Loop

End If

End Sub
 
Okay, so now you know that the original message was triggered because the
save failed.

Remove this line from Form_BeforeUpdate:
If Me.Dirty Then Me.Dirty = False
That line can't succeed: the save can't happen before the event completes.
(It's not needed either: Form_BeforeUpate doesn't fire if the form is not
dirty, unless you called it yourself.)

If this table is bound to tbl_Projects (or a query based on that table), the
Execute can't work either. We have a form that's about to write to the
table, and if you do that by executing the query, you're setting yourself up
for a write conflict.

I'm not sure what the purpose of this code is, but there's no way it can
work.
 
First, I want to add the date/time stamp
Second, I want to force an entry into ProjectNameShort if ProjectNameLong is
not empty

Here is the original code

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Add date/time stamp for DateUpdated
Me.DateUpdated = Now()

If IsNull(ProjectNameLong) = False Then

Do While Len(Trim(ProjectNameShort & "")) < 1
'Set ShortName value using input box
ShortName = InputBox("Please enter an abbreviated name in the
Project Short Name")
'Remove leading or trailing spaces
ShortName = Trim(ShortName & "")
'Set ProjectNameShort to ShortName
Me.ProjectNameShort = ShortName
Loop

End If

End Sub

OK, if I comment out everything except me.DateUpdated = Now() I get the
Update or CancelUpdate without Edit error. I don't understand what isn't
working, it did before the 2007 migration.
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.DateUpdated = Now()
If IsNull(ProjectNameLong) Then
Cancel = True
MsgBox "Please enter an abbreviated name in the Project Short Name")
End If
End Sub
 
Totally confused now! If I put me.dateupdated = now() on a button click
event, it works, but on form_beforeUpdate it errors.
 
Steve, I think you will discover that my original diagnosis was probably
correct, and that whatever is actually triggering the Form_BeforeUpdate
event's firing is actually where the problem lies, not the Form_BeforeUpdate
even itself.

If that is not the case, then the module is corrupt and needs a decompile.
Here's a series of steps that will fix a whole bunch of issues, so follow
them through in order. If I was right the first time, you will still have
the same problem to solve after this sequence, but you probably want to know
if it is corrupt anyway.

Try this sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

7. Still in the code window, choose Options on the Tools menu. On the
General tab, make sure Error Trapping is set to:
Break on Unhandled Errors
and the Compile on Demand is unchecked.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
the code syntax is compilable, and the VBA options are set to show errors
and avoid this kind of corruption.

More info:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html
 
First, I would like to say thank you for you time and patience.

I believe I have found where I was going astray. I added the me.dirty
string to the Listbox_AfterUpdate event that was triggerring the form update.
Originally it was triggering th form update at the end, after it was already
trying to change the displayed record, and now it is triggering after the
dirty = false, while its still on the correct record.

Does that make sence as to the cause of this problem? I believe that would
fall under your first diagnosis. I wish I could have understood what you
were trying to tell me then. Again thanks.
 
Back
Top