AfterUpdate Form Event Fires Repeatedly

[Jenna B]

I've been having this problem for a few months with a db I'm working on. I
know there must be something simple I'm overlooking, so I'd greatly
appreciate any help in identifying what the problem is.

I'm using the following to create a timestamp:

Me.dbLastUpdateUser = Forms!frmUserInfoHIDDEN!txtUserName
Me.dbLastUpdate = Now()

frmuserInfoHIDDEN refers to a form that is hidden on startup that stores the
name the user logged in with. This feature works fine.

The AfterUpdate timestamp used to work as well. However, now it repeatedly
fires and forces the user to stay on the current record. It won't go to the
next or previous records, and inserts now() into the field each time I press
the > or < buttons.

I started over with the form, so now the only event that I have is the
AfterUpdate event. There are no OnCurrent events.

Thanks in advance for any help you can offer! I know it must be some simple
fix. (Note: I'm using Access 2003 for this project)

 

[Jeanette Cunningham]

Hi Jenna,
How are using the timestamp in the after update event?
If your form for entering data is called frmA, I would think code like this
would work

Private Sub Form_AfterUpdate()
forms!frmUserInfoHidden!txtLastUpdate = Now()
End Sub

where txtLastUpdate is the name of the textbox for the date field called
dbLastUpdate

Jeanette Cunningham

 

[George Nicholson]

Any Timestamp code should be in a BEFORE Update event. If its placed in
After Update, you create an endless loop:

Some change is made that will trigger an Update.
AfterUpdate runs Timestamp. Timestamp updates the record, causing
AfterUpdate to fire again,
Timestamp updates,
AfterUpdate fires,
Timestamp updates,
AfterUpdate fires,
Timestamp updates,
... etc...

You'd never get off the record because the Update/AfterUpdate sequence never
finishes.

 

[Jenna B]

Jeanette,

Thanks for your reply. A few more details:

Yes, it is in an AfterUpdate event of my form.

There are two fields on frmA that need to be updated - the user who updated
it and the date.

A hidden form (frmUserInfoHIDDEN) contains the user name of the currently
logged in user based on my login screen. So, I reference that for the
dbLastUpdateUser field.

There is no problem with the correct information being placed in either of
those fields. On AfterUpdate they both display correctly the user name and
date. However, the event continues to fire repeatedly when I attempt to
navigate to another record. Example: it logs the current user as "jenna" and
then successively logs the lastupdate field as "1-10-07 1:51:03am", "1-10-07
1:51:05am", "1-10-07 1:51:07am" as I tried to advance to the next record. It
wouldn't let me add a new record or delete the record either.

Side note: my mouse scroll bar doesn't work for advancing through records on
this form and I don't have it disabled. I don't know if that is part of the
same problem.

Thanks,

Jenna

 

[Jenna B]

I knew it was something simple like that! Thanks so much George.

I plan on avoiding endless loops in the future.

Thanks again for your quick response.

 

[Jenna B]

Side note: apparently the mouse scroll to navigate records in a form in
Access 2007 is disabled. (I'm using 2007 at the moment, working on my 2003
db.)

Access does scroll in continuous and datasheet views though.

Reference: http://allenbrowne.com/Access2007.html

 

[John W. Vinson]

I knew it was something simple like that! Thanks so much George.

I plan on avoiding endless loops in the future.

Recursion, n. See: Recursion.

- The Programmer's Dictionary

John W. Vinson [MVP]