Now() and CurrentUser() functions on form and table

[thunderchicken]

Hi,

I've been attempting to set up a user and timestamp on a table to document changes to data. To make this work, I've attempted to create an event trigger tied to a subform, (to maintain data on the DRAW_REV table), to update the user id and the date updated when the NOTE field is updated:

Private Sub NOTE_BeforeUpdate(Cancel As Integer)
UID = CurrentUser()
DATEUP = Now()
End Sub

The user id is updated fine, but the date updated remains unchanged by the routine.

Related to this:

I added the =Now() as a default value on the DRAW_REV.DATEUP field, and this works fine when a new record is added. The data is displayed properly by the subform, but just not updated.

I tried setting the default value for the UID field, on the table, to be =CurrentUser(), but get an error when I try to save the table design:

Unknown function 'CurrentUser' in validation expression or default value on 'DRAW_REV.UID'.

If I set the subform up to assign the default values when a new row is added, they both work fine. Go figure!

Can anyone tell me why some of these functions only work some of the time, and how I can get the event trigger to work?

I appreciate any insights you can provide.

Dave

 

[gusbrunston]

Hi:

Here's a good FAQ by Elizabeth on this form will do what you want:

faq181-74 Gus Brunston An old PICKer, using Access2000.

 

[thunderchicken]

Hi Gus,
Thanks for the reply.

I looked at the FAQ you suggested, but I think that's essentially what I've already tried, with the differences being that I am doing it on BeforeUpdate vs. BeforeInsert, and i've chosen Now() instead of Date(), (I did try using =Date() and =Time() just to see if either of them worked, but neither did).

1)Why does the =CurrentUser() not work as a default value on the table? (I have users who persist in adding rows to the table directly , "It's faster than using the form", I haven't had the heart to lock them out of the tables yet).

2)What is wrong with the syntax that I list above?

3)If the functions work in some applications, (i.e. as a form default), they must be installed, right? Could there be an installation issue?


I'm still quite perplexed.

Dave

 

[Modex]

Hi Ya,

Ive had this problem, the way I got around it was like this

aa = now()
[date] = aa

Simple, but that seemed to do the trick for me

Cheers

ModeX

 

[gusbrunston]

Hi:

I just tested the following and it works for new records and edited records:

I put it in the After Update event. I didn't try in the Before Update event:

Private Sub NOTE_AfterUpdate()

Me.

UID = CurrentUser()

Me.

DATEUP = Date
End Sub

Won't work for me without the "Me."

You're sure, aren't you, that:

The text box NOTE is bound to some field in your table/query.

The text box UID is of the type text and is bound to the field UID in your subform's table or query?

The text box DATEUP is of the type DateTime and is bound to the field DATEUP in your subform's table or query?

Then there's no reason the above won't work.

I hope this is helpful. Gus Brunston An old PICKer, using Access2000.

 

[thunderchicken]

Yeehah! I have success!

I tried Modex's trick to no avail, thanks though.

Gus's suggestion got me thinking though. My form is driven by the table directly (no query), and Note, UID and Dateup are all columns on the table.

With a change to the text box 'Note' (tied to the column Note on the table), I wanted to update the UID and Dateup columns on the table, presuming that I would not (ultimately) need text boxes on the form for those two columns.

I had created two temporary text boxes on the form for debugging those columns (default names 'text12' and 'text14') with their sources being UID and DATEUP respectively. So, I didn't really understand how a text box could have a different type than the table column, and since my update was being made to the table directly, I don't understand why the text box for those two columns even enters the equation. BUT IT DOES:

When I changed the Names of the two temporary text boxes to match the names of their source columns, (and don't ask me why), it all worked like a charm.

Maybe someone can explain the magic behind that to me?

Thanks Gus and Modex! May all your compiles be clean, and your boss generous!

Dave

 

[thunderchicken]

FYI- another lesson learned

Dropping the text boxes from the form for the UID and DATEUP columns fixes the problem as well. The event still updates the table columns even though they aren't referenced on the form at all.

So, do it right, or don't do it at all. Just don't do it wrong.


Tried doing this with a macro, defined it using the macro builder wizard. I select SetValue, selected the table and column, (the wizard constructed the reference as [DRAW_REV]![UID]), and entered the value CurrentUser(). DID NOT WORK, got an error to the effect that object DRAW_REV not an automation object.....

So, irritated now, I dropped the reference to the table so it reads simply [UID], and tried it. Works fine. Go figure.

I don't understand it, but I hope it saves somebody else's day.

Dave