Re: Me.ControlName = Something

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MeSteve" <MeSteve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:22FCE923-758F-4C8C-929F-6E9BB5E6FD40@xxxxxxxxxxxxxxxx
Anything in the form_BeforeUpdate event causes the update error.

"Allen Browne" wrote:



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

"MeSteve" <MeSteve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:138984E2-7AF9-4F49-AE1E-E545F4B62E31@xxxxxxxxxxxxxxxx
> 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.
>
> "Allen Browne" wrote:
>
>> 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.
>>
>> -- >> Allen Browne - Microsoft MVP. Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "MeSteve" <MeSteve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:FB6481B2-2BD8-4CDF-B8CF-456860D57FCC@xxxxxxxxxxxxxxxx
>> >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
>> >
>> > "Allen Browne" wrote:
>> >
>> >> 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
>> >>
>> >> "MeSteve" <MeSteve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:A80C9D0D-49A8-4FD4-8710-9ACE6FECE793@xxxxxxxxxxxxxxxx
>> >> >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?

.