Update Date/Time Field OnClick?

[thinair421]

Hey again,

Ok so I have a tabbed form with a subform under each tab. The date/time field on each subform has the default value: =Now(), however the user may take a while before entering data into the subform (due to them collecting the data first). Is there a way to update the date and time field using an OnClick event proceedure or something similar...AKA refill the field with an updated =Now()? Thanks in advance.

 

[boblarson]

Why not just add the date/time in the Form's BEFORE UPDATE event. Get rid of the default and then just add:

Me.YourTextBoxName = Now

in the Form's Before Update event, which then will give you the date/time that the record was saved.

 

[thinair421]

Ok so got rid of my default value and entered the code into "Before Update" on the form (altered for my textboxs name), which now leaves the date field blank when the form is opened and being filled out. So after I enter all of my other fields on the form, I go to create a new record, but it wont because the date is blank (and its my primary key). My understanding is that right "Before" the record is saved and a new one is created that the date would be entered automatically on the first record. Is my thinking correct or am I way off...any suggestions?

 

[boblarson]

Ok so got rid of my default value and entered the code into "Before Update" on the form (altered for my textboxs name), which now leaves the date field blank when the form is opened and being filled out. So after I enter all of my other fields on the form, I go to create a new record, but it wont because the date is blank (and its my primary key). My understanding is that right "Before" the record is saved and a new one is created that the date would be entered automatically on the first record. Is my thinking correct or am I way off...any suggestions?

Okay, first of all -

1. I would just add an Autonumber to your table as a primary key.

2. You can then deal with the date like this.

3. Why do you have date as a primary key?

 

[thinair421]

This is my first database, so I'm sure that my design is off a bit...When I started designing the database, I knew we wanted all of the data tables to be sorted by date, so thats why I used it. Also didnt like the fact that autonumber doesnt readjust all of the autonumbers if a record is deleted (aka..if record #3 is deleted it wont slide #4 up to 3)...just saw this as annoying (and possibly confusing down the road for the people analyzing the database), considering our autonumber isnt a customerID or anything.

Anyways, I guess I could autonumber the form, shouldnt be a problem. Only other question I have is whether or not when the user goes back to a previous days record, will the dates be overwritten?

 

[boblarson]

Okay, let's start by giving you this information.

1. Primary Keys are something for the DATABASE to use to maintain referential integrity of the data and the relationships. They are something that the users do not need to see or use, really.

2. An autonumber guarantees a unique number. They do not have to be consecutive or even positive. But if you let people see them then it causes great consternation when they see things out of order and all. It is something they should not concern themselves with. It is for the system to do its thing and not for them to do theirs.

3. You can sort your data any way you want it. Just remember that tables do NOT store data in any particular order so if you want to see it in a particular order then you would need to use a query and sort on it. Having a date/time stamp for data entry is good so you can see exactly the order of when data was input. But that date/time stamp should be applied right at the time the record is updated, hence my suggestion for the Before Update event.

4. If you want to have only one date or date/time at any one point you can set an index with No Duplicates specified. That will keep multiples for that date or date/time from happening and yet it does not need to be the primary key in order to accomplish this.

5. You can decide to replace the date/time stamp if a user goes back and modifies the record later. Personally, I have two fields in my tables when I want to track the last update. I have the Record Creation Date stored and the Last Update Date stored. When the Before Update event occurs, it checks to see if there is anything in the Creation Date field and if so, it doesn't write anything to it. And then it always, when an update occurs, writes in the date/time in the Last Update field. You might choose to do something similar.

I hope that helps.

 

[thinair421]

Bob,

OK your original coding the Before Update box worked perfectly, I just had it updating a different field (not the date) on accident. It works great now, my only concern is still if the user goes back to previous records, will they be updated to the current days date?

 

[thinair421]

Sorry about the above post...didnt know you replied already.

Anyhow I just read your post, and everything makes perfect sense, however I am pretty new to access and slightly confused by #5...I just want to ensure that if the user goes back on a record that the date is not overwritten. I'm going to try and read #5 a few more times and see if I can get something working. Thanks so much for the help Bob.

EDIT: In my attempt to answer my own question I found that if I just skim through the records without changing any fields, the database knows I am not updating anything, and the dates are fine. However, if I update any values then the date is changed (understandably). This could be extremely bad for the database's integrity if the user finds a mistake in a previous record and attempts to correct it. I would really like to not have to create 2 date/time fields (ie...one for time record created and one for time record updated). Any suggestions?

 

[boblarson]

If you have the text box for the original date, let's say it is named txtRecDate and that is bound to the field in your table. The code in the Before Update event would be:

If Len(Me.txtRecDate & "") = 0 Then
   Me.txtRecDate = Date
End If

That will only populate it on the first time and ignore it subsequently.

 

[thinair421]

Bob,

Genius work man. It's doing exactly what I wanted it to. Many many thanks.

 

[boblarson]

Bob,

Genius work man. It's doing exactly what I wanted it to. Many many thanks.

Glad we could help.