Help with alternative to "Now()"

[bon]

I'm using Access 2000 at work, designing a simple database to keep track of incoming messages. I will eventually have the secretaries entering messages as they come in.

In my table, I have a field called "Time." (To work like a time stamp, saying when the message came in.) I've set the Data Type to "Date/Time" and the Default Value to "Now()".

As soon as I start entering information on a record in the table (Line 1), the record below (Line 2) pops up and the "Time" field populates with the current time. If it takes 10 minutes to when I actually enter information on the new record (Line 2), Line 2 will still show the time of 10 minutes earlier, not the time that information is entered.

Any help would be appreciated ... as you can tell, I'm pretty much a beginner at this ... so talk slowly (and gesture with your hands).

Thanks!
-Bon

 

[930driver]

A few comments:

First 'Time' is a reserved word in VBA and you should never ever use it as a field name. Use 'CallTime' or 'EntryTime' or something like that.

Second, it is a poor practice to do data entry directly into a table. Better is to use a form as an interface. You can customize the look, you have access to form events for coding in the future, and your default value problem will go away if you set the default value in the control for your time control on the form rather than in the table. That way you won't get a default value until you go to a new record in the form.

If you wanted the time to be even more specific you could place a button on your form that, it it's click event, would stamp the time with a piece of code something like this:

Me.txtCallTime = Now()

Then the user could click it and get the actual time, or at least what the computer believes the time to be
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[JimHorton]

Bon,
Access, without triggers, makes this tough--you can't set a field in Before_Update of the form. If you knew, say, the last field the user would enter before hitting 'Save' or whatever, then you could set the CallTime field in the AfterUpdate of that *control* (as opposed to Form_AfterUpdate), but the only sure option to get this completely accurate is to do a behind-the-scene's sql update in the Form.

It is essentially a double write to the table. But in Access, even on a network, this isn't that bad in the big picture--if you're spending 10 minutes in a form entering the data--what's another few milliseconds to write a timestamp to the same record in the afterupdate event? If it's a super busy db, and you're spending 5 seconds on the record, with 50 users entering 10 records a minute, then the default Now() should be ok, because conversly--if you're only spending 5 seconds on a record, how important is that 5 seconds for that record's stame? And what about the difference in each pc's system time?

So, the double-edge sword works in our favor here--spend the extra time on the double-write where data-entry is time consuming and infrequent, and use the Default where speed and concurrency is an issue--finally--a win-win situation!
--Jim

 

[bon]

Thanks, guys. Both of your answers have helped. I will try the Form and Time Stamp that 930 has suggested, but I originally wasn't going to use a form because the secretaries (distributing the messages) need to see who has gotten the most recent messages, to assign the next message, and you can see that by looking at the Table.

-Bon

 

[930driver]

You can use a 'continuous forms' form to view a bit from each record and then open a 'details' form when more information is needed, or when adding a new record.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"