Field auto-generation.

[DocZayus]

Hi, I'm new around here, but you should be seeing quite a bit of me on the Forums.

I have a fairly basic Knowledge of the Office Suite. So I will be begging for help. Quite often. But don't worry, I learn fairly fast. My only problem is that my main language is French but I am am fluently Bilingual. I just lose track sometimes.

Anyhow, here's my 1st Question:

Users need to login to use the database, and I make a weekly update.
I have a Form, with many fields, some locked, others not.
I need for 2 fields to update when one field changes.
i.e.:
when a name gets entered in field1, have field2 automatically enter the users login name, and have field3 auto-add the Date().

My Office Suite is in French, but I should be able to figure it out.
Thx!

-Doc

 

[Pat Hartman]

The usual place for this code is the BeforeUpdate event of the FORM. However, if you only want to log who changed a single field, you can put the code in the AfterUpdate event of that control.

Me.LastUpdateBy = CurrentUser()
Me.LastUpdateDate = Now()

If you are not using Access security, you need to replace CurrentUser() with the user's login however you get it. I use Now() rather than Date() to record updates because Now() includes time of day as well as date.

 

[DocZayus]

thanks!

Me.LastUpdateBy = CurrentUser()
Me.LastUpdateDate = Now()

Me. <- does that mean the current field?
Because I need 2 other fields to be modified when the first is changed.

and I don't need time of day, it's just to inform us of who last made changes and when. but I will try the Now() command, to see the outcome.

Thx!

(I'm home now, but I will check it out tomorrow at work.)

 

[Pat Hartman]

Me. is a reference to the current form or report object. It is a way of helping VBA resolve the variable reference. It also has the benefit of providing intellisense. Try it in a form's class module to see what intellisense is.

Now() returns the current date and time - 6/22/04 9:44 PM (don't worry about format, you can set it to whatever you want). Date() only returns the date - 6/22/04

 

[DocZayus]

Thanks, going to try that out.

 

[DocZayus]

ccess can't find the macro called ME
Me.DATE MOD = Now()

I've also tried
="DATE MOD = now()"
="[DATE MOD] = now()"
=(DATE MOD = now())
=[Me].[DATE MOD]=Maintenant()
=[Me.DATE MOD]=Maintenant()

and many other light variations with " " and () and []

any ideas?

(ps: maintenant() and now() are the same french/english)

 

[DocZayus]

field names are
[MODIFICATION] (for the name - currentuser() )
[DATE MOD] (for the date - now() )
[MATRICULE] (the modified field)

-doc

 

[Lilgreenfrog]

im hvaing exactly the same problem as this at the moment, and wondered if you had managed to resovle it, and if so, how?

Also, weirdly I'm leaving Britain for Quebec in 3 weeks time to live there for a year! Hope its all everyone says it is!

 

[Pat Hartman]

When someone gives you "code", you need to put it in a module. You can't just type it in the box next to the event name - you need to click on the builder button at the right edge of the property field. Chose code from the dialog and then place the code WITHIN the procedure's header and footer lines which are generated by the builder.

Sorry Doc somehow I missed your final question:
If you ONLY want the two fields updated when the [MATRICULE] field changes, place the following two lines of code in the [MATRICULE] control's AfterUpdate event. If you want the modification date and person stored no matter what field on the form is updated, then the code should be placed in the FORM's BeforeUpdate event. Notice that different events would be used if you want to associate the update to ONE field or ALL.

[MODIFICATION] = CurrentUser()
[DATE MOD] = Now()