Current date/time fields update on first focus; no subsequent updates

[krenzc]

I apologize up front for my inexperience; I am decent with computers, but I am not experienced with Access and do NOT know VBA. Any help would be appreciated. I searched the forums, but sorry if I missed this topic.

A bit of background: I am putting together a form for telephone interviewers to use for a study we are doing. I want to make it as user friendly as possible (for their sake, as well as to justify the use of an electronic format to reduce additional data entry). Here’s one thing I would like to do…

For the call log portion of the form, they need to enter the current date and time. When they’re tabbing through (or click these fields), how can I make it so that the current date and time automatically appears (so they don’t need to enter anything), and they can just continue tabbing through? It can NOT be done such that if they put the focus back into that field at a later time that the value will be updated (overwriting the actual time the call was made). It needs to pop up once the first time they enter the field and then stay that value forever unless they manually modify it. If you have alternative ideas for accomplishing the same thing, I’m all ears. If this can only be done with VBA, I would greatly appreciate someone providing the code with an explanation of how to implement it. Any help at all appreciated though!

(Bonus Question: Why the **** when you’re entering new fields into an Access table, does it shoot the cursor back to the first column after entry? It’s incredibly annoying. Any work-around?)



Many thanks,
Chris

[ItsMe]

The only way I know to handle this would be to use VBA. You can add some VBA to an "AfterUpdate' event of the control. You would need an event handler for each field you want a time stamp.

In your table, you will need a timestamp field for each relative field. For example, Field1 would have a sister field named Field1TS. This timestamp field will be of the fate time type.

[krenzc]

Thanks ItsMe. That does help, though if anyone has more detail or an example of the VBA that would be awesome. If not, I'll look into this some more and see if I can figure it out. I should really take a little time to learn VBA...

[ItsMe]

To keep things simple you can place controls on your form that are bound to your timestamp field. You can set their visble property to = No.

You will need to type the correct names of your controls in the code. Here I am using the example name of the timestamp. You need to create an event handler.

You can do this by going to one of the controls on your form, while in design view, and double clicking the control.

You want to add code to the event handler for the control taht will be receiveing the update from the user. Double clicking one of these controls will open the Properties window for that control. Click on the event Tab of the Properties window. In the field "After Update" click the elipses (...) button. Then choose "Code Builder". This will create a new event handler where you can place your code. Use the following code

Me.Field1TS.Value = Now()

With that, if the first control gets updated the other will receive the current date and time, as soon as the user navigates away from the control or to a new record. You need to make sure the fields you create to store the timestamp have the data type Date/Time.

[June7]

Fields and data controls have a DefaultValue property. This will cause field to automatically populate when a value is entered into any other field of the record (record must be initiated to trigger the DefaultValue property). DefaultValue runs only for new record, it will not modify existing record. But if you want a timestamp for each info element and only if that info is entered, use code.

VBA code to populate date value would be like:

If IsNull(Me!fieldname) Then Me!fieldname = Now()

The real trick is figuring out what event to put the code in.


I am not sure what you mean by the description of 'returning to first column'. You are entering directly into table, not through a form? What do you want to happen?

[krenzc]

Great; thank you both. That all helps quite a bit. I'll try it out later today or over the weekend.

(Regarding the other minor question, I think I was wrong--it may only be with renaming fields in a table. Regardless, I was renaming a large number of consecutive fields and each time the focus jumps back to the first column/first record, which is very annoying. Not a big deal; just frustrating.)

[ItsMe]

(Regarding the other minor question, I think I was wrong--it may only be with renaming fields in a table. Regardless, I was renaming a large number of consecutive fields and each time the focus jumps back to the first column/first record, which is very annoying. Not a big deal; just frustrating.)

Can not relate. I don't believe I have ever experienced this.

[June7]

Do the renaming in Design view instead of Datasheet view.