Timestamp/Username for Different Fields at Different Times

[mrmmickle1]

I would like to have something of this sort:

Sample Recieved (Short Text):
Person Received Sample (Environ("Username"):
DateTime Sample Received (MM/DD/YY hh:mm AM/PM):


Sample Signed Out (Short Text):
Person Signed Sample (Environ("Username"):


DateTime Sample Signed Out (MM/DD/YY hh:mm AM/PM):

Is there a way to do this with calculated fields or a macro that updates only specified fields. For Example Sample Received is edited and then the Environ("UserName") is automatically filled (Person Received Sample) in, as well as the DateTime Sample Received. I found a few references online that will update a DATE TIME field when the record is created but was unable to find an example for a multiple fields in the same record.

Any help with this issue would be much appreciated.

[CJ_London]

in your form before update event put

person=environ("username")
updatetime=now()

the format MM/DD/YY hh:mm AM/PM is just that, a format (which is a text datatype) - which you would use to display time in that format e.g.

format(myfield,"MM/DD/YY hh:mm AM/PM") returns now() as a text value '05/28/15 02:59 PM'

or use the control format property

datetime is actually a decimal number - the value before the decimal point is the day as a number (today is 42152) and the fractional part is the time expressed as a percentage of 24 hours - so 12 noon is 0.5, 6:30pm is 0.771

[mrmmickle1]

Thank you for the information. I am not the best with forms. I understand the Format as well as the way datetime is stored as a serial number.

So if I assign those variables (person, updatetime) how do I attribute them to the specific record being edited? Do I need to do a Dlookup on an ID and then insert them somehow based on the ID Value? I know this is probably a very rudimentary question... I'm just not all that familiar with how to do this sort of operation.

Also:

Could you use something like this in a calculated field:

IIF([Record1] IS NOT NULL, NOW(), NULL) or IIF([Record1] IS NOT NULL, NOW(), "") or IIF([Record1] IS NOT NULL, DATE(), "")

Any help would be appreciated.

[ItsMe]

I am confused where these fields are that you wish to update. I think I understand that you wish to use the same value (type Date) to update multiple fields. Are all of these fields in the same Row/Record or are they within a single column and include multiple records?

[mrmmickle1]

ItsMe,

You are correct, I may have misstated my goal (@ Ajax, I apologize for the confusion). I think I've made it to broad:

Goal:

I have one record that has multiple fields for Users to initial "tasks":

When the User puts their initials in one field I would like for a second field to be Date/Time stamped:

Example Record (Color Coded Based on Time Accomplished, Numbered Fields) :

#1 Sample Number,#2 Entered Sample Initials,#3 Entered Sample Timestamp,#4 Task 1 Completed (Initials to be placed Here),#5 Timestamp for Task 1 Completion (DateTime Stamp Here),#6 Task 2 Completed (Initials to be placed Here),#7 Timestamp for Task 2 Completion (DateTime Stamp Here),#8 Sample Completed (Initials to be placed Here),#9 Timestamp for Sample Completion (DateTime Stamp Here).

When one field is initialed I would like the associated field to be datetime stamped. A Calculated field is my preferable method.

Sorry for being a little ambiguous earlier. I was overthinking things when I added the Environ("UserName") Part...

[ItsMe]

It is best to use a form as an interface for the user. You can place code behind to insert a Timestamp. If use a form that is bound to a table or an updatable query. It would be as simple as assigning the Timestamp value to the field. You do not even need to place a control on the form to represent the Timestamp field.

In an AfterUpdate Event for a control bound to your Entered Sample Initials field, you could place
Me![Entered Sample Timestamp] = Now()

I do not recall if you need to place the parenthesis for the Method Invocator or you can simply type ... Now.

BTW The Date Function does not include the time (It will default to midnight or 0 - 100 hrs, don't recall exactly) and Now will get the system date and time.

[mrmmickle1]

Thank you both! I appreciate the assistance!

[ItsMe]

You bet ...

[NightWalker]

I know this is an older thread but I was wanting to use this for my database. Would I add (UserNameID) and (TimeEntered) fields to ALL of my data tbls? Or would there be an easier way to track which user entered what records and when?


Thank you for your help.

Walker

[mrmmickle1]

You will need to add these fields to the tables you intend to timestamp. The form will need to be bound to these table fields as ItsMe mentions.

[CJ_London]

Or would there be an easier way to track which user entered what records and when?

as mrmmickle1 says - that is the easiest way. An alternative and not so easy is to have one table with fields for user, timestamp, table updated and record updated - this is called a log. A log means two (or more) record entries (the record updated/created and the log), rather than one so requires additional coding. You can add another three fields to this table if you want - field updated, oldvalue and new value if you want to know what the user added/changed - so if user changes 10 fields - that is 10 entries in the log. And to then interpret it to provide a history of changes is even more work. It all depends on what you want to do.

[NightWalker]

Thank you both. After thinking about my question it only makes sense to add the extra fields to the existing tables. Thank you both for your help. I have another similar question but I am not sure if I need to start a new thread for it. It is about checking the username to a table that tells what level of security they are allowed and then limiting them to only certain forms and reports or locking them out of being able to edit anything in the FE or BE. I was trying to figure out what would be good level names for the user groups. Please let me know if I should ask this in another area. This is the first forum I have ever been in so I am not completely sure of what I am doing.

Again Thank you both for your help.

[CJ_London]

best to start a new thread - but when you do, look to the bottom for thread on the same subject. What you are asking is a common question