Dates

[Amit112]

Hi Everyone,
I have a form I am making and I was wondering if there is a way to have a date field there that is automatically filled in for that date when it is opened and stays at that date. Here is an example:

Date: Jan. 6th 2005 (this would automatically fill in)

- now if I opened it 3 days from now it will still say
Date: Jan. 6th 2005

Thanks guys

 

[ACCtuary]

Why not set a default value =DATE() in your text entry box?

In the form, right click on properties, open the data tab, and you should see a default value entry.

This will keep the date at the value it was when the data was input. It will not automatically update once the record is updated.

HTH

 

[Norie]

For the date field enter =Now() for the Default Value property.

You can do this in a table and form.

 

[Amit112]

Thanks a lot guys its really appreciated.

Amit112

 

[Amit112]

Hey guys
there is a problem with this. The Date stays there but if I put another date in, it updates to that. So if the date is June 5th, 2005, it will stay in that field. But if i update it to june 6th 2005, that will become the new date for all. Please help me with this.

Thanks a lot.

 

[ACCtuary]

The real issue is not dates - it is how can you lock a field so that it can never be updated once it is created. And then ask, is this what you really want, or is it just a means to another end?

I would love to see what more experienced people have done with this issue.

 

[ACCtuary]

Another way you can play with this is as follows:
First, you can select the LOCK property of a form - along with default = DATE() this will do what you want.

Set the DATE with the default property as before. But then in all queries don't display the date directly. Instead, display a string formatted version of the date, which can't be modified.

SELECT Format([date],"mmmm d, yyyy") AS datef, tblAmit.Description
FROM tblAmit;

Probably the best solution is not to have the date entry on your form anywhere. If they can't change it, why tempt them? When the record is updated, use an the AfterUpdate event to set the date to the current date. Queries will still show the date (see above), but it can't be updated anywhere (except on Table view - if you really need to fudge something, you always have a back door).

I suspect there is an even better way.

I guess you are probably trying to create some sort of audit trail and not let people modify parts of records. Why not keep track of each record and when it was entered *IN A SEPARATE TABLE*? Then you can lock the tblAudit table so that new entries can be added but existing entries cannot be edited. You can continue to edit the data itself by editing your main data table (tblData in example below) and not touch tblAudit.

Such a solution is more expandable, and it relies only on the table organization, not on the GUI features. This way you can expand it to a larger SQL server when the time comes.

I do not have time to work out the rest of the details right now. I think this is the right direction you want to head, though.

Example:
Record 1: Enter in "Hello There" on 1/6/05.

Access stores
1,"Hello There" in tblData

and

1,#1/6/2005# in tblAudit

and now you can refuse edits to the Audit table. Of course, this also refuses deletion if you have referential integrity turned on. I assume that if you want to put a record out of commission in an auditable way, you may wish to just mark it on the audit table as inactive rather than deleting it.
After all, if you refuse to edit dates after they're entered, there must be an equally strong reluctance to delete records.

 

[mdmilner]

Yes, there is a simple way to manage this.
Control the editing of the field during the new record creation process on the form. For starters, if you make the field visible, lock it (on the form). This only locks edits to the field (via that form)

Second is a question. How are you creating new records?

My suggestion is, to remove the recordnavigation buttons (the default items at the bottom of the form) and create your own buttons. A simple way to do this is to create a button and use the wizard to create a 'new record' button. After it's created, open up the VBA code behind it and add a line that edits the field value of the current record. Something like:

Me.fieldname1 = Date()

This would stamp the field with the date time as you clicked the new record button which may or may not be what you want. Actually creating a full-scale audit trail is a bit more complex than this.

I would suggest this as an excellent demo:
http://www.candace-tripp.com/_pages/access_downloads.asp



Mike

 

[Amit112]

Hi Mike,
Thanks for your help but this is not working....
any other suggestions. I want the date to only save per record.
So for record 1, the date automatically fills in as todays date.
When I switch to record 2, and say I do it the next day, the date would switch to that. Thanks

 

[mdmilner]

Hey guys
there is a problem with this. The Date stays there but if I put another date in, it updates to that. So if the date is June 5th, 2005, it will stay in that field. But if i update it to june 6th 2005, that will become the new date for all. Please help me with this.

Thanks a lot.

I have a suspicion there is a technique/problem approach issue here.
What do you mean when you say "...that will become the new date for all."
When your code updates the date, does it update the date for ALL records?

This sounds like you're using an UPDATE query without any parameters to uniquely idenfity your single record to update a field.
--
Related to my suggestion, in order to reference table data with syntax such as Me.fieldname1, you have to base the form on the table or a recordset. once you do, you gain the ability to reference the currently selected record by using the same fieldnames that exist in the table.

As a suggestion to demonstrate.
Create a new form, base it on any table. Allow the wizard to throw onto the form however many fields from that table that you'd like to see.

Create a button - but do not use the wizard to make code.
Open up the form in design mode, open up properties on the button, then go to Events and create a Click event.

In the subroutine that shows up, put something like:

MsgBox Me.any_field_name_from_table & " is the information"

All this does is throw up a message box with whatever information is in the field you type in. If you navigate to a different record, and then click the button, the message should change.

Mike