Question Auto date update

[Arnold]

New in Ms Access and working on a database. I'm having a problem with date fields that I would like to automatically update to current date each time I open my database.I have used the Now expression as my default value thinkg it will work. Unfortunately the date doesn't auto update rather it remains the same.

 

[vbaInet]

[B]=[/B]Date()

... in the Default Value property of your textbox.

This only works when you create a New Record.

 

[CBrighton]

If the form which this control is on is bound, then the stored value overrides the default value (assuming the control is bound too).

If it's an unbound control and/or form then it should be calculated whenever the form is opened (i.e. when the control is loaded).

Of course, if you have the database open over midnight it will then be correct unless you have soemthing like a timer event refreshing the form or setting the value of the combobox to =Date() again.

 

[vbaInet]

If the form which this control is on is bound, then the stored value overrides the default value (assuming the control is bound too).

Just to clarify on this point, the Default Value doesn't affect current records. The Default Value property applies only on newly created records so it's not really overriden.

Of course, if you have the database open over midnight it will then be correct unless you have soemthing like a timer event refreshing the form or setting the value of the combobox to =Date() again.

Not a good idea to set a timer to update this record automatically. The overhead is not worth it and the constant update to the field may cause problems.

 

[CBrighton]

Just to clarify on this point, the Default Value doesn't affect current records. The Default Value property applies only on newly created records so it's not really overriden.

Both pieces of data can potentially be displayed on that form, but it has been specifically told that the default value is =Date().

If something else is coming in and saying "I know you've been told you should display that normally, but as I'm here you will display my value" then it sounds like overridding to me.

Not a good idea to set a timer to update this record automatically. The overhead is not worth it and the constant update to the field may cause problems.

*shrug*

Depends on the frequency of the timer event imo.

I use databases created by someone else that refresh their =Now() field every second, but I wouldn't use anything like that frequency.

In fact, even when looking for a specific hour my timers are rarely more than 15-30 minutes. When looking for a day it could be hours between events. Refreshing a control holding =Date() once per hour isn't exactly an awful overhead.

I take your point about constantly updating it if it's recorded data rather than an unbound field though.

 

[vbaInet]

I was just making a clear distinction between how the Default Value behaves for current records and new records. No pun intended

 

[Arnold]

If the form which this control is on is bound, then the stored value overrides the default value (assuming the control is bound too).

If it's an unbound control and/or form then it should be calculated whenever the form is opened (i.e. when the control is loaded).

Of course, if you have the database open over midnight it will then be correct unless you have soemthing like a timer event refreshing the form or setting the value of the combobox to =Date() again.

....but my form is obtaining its expressions from the table, where the date(), or Now() has been set

 

[CBrighton]

If you just want a date field in a record (or all records in a table) to be updated each time someone opens the database then create an update query to set the field to =Date() and use an AutoExec macro (or just in the on open event of your switchboard) to run it each time the database is opened.

 

[Arnold]

I also want to have my access 2010 post records done say monthly by creating monthend procedures (say Zip folders). How do I do this on my PC?

 

[vbaInet]

I also want to have my access 2010 post records done say monthly by creating monthend procedures (say Zip folders). How do I do this on my PC?

Every new question requires a new thread so that searches are relevant.

 

[tquinn]

I've also had this problem. I used this function in an access database (Access 97) for years to fill a field with the current date. Then one day it just stopped working.

Maybe this is Microsofts way of saying I need an upgrade.

If anyone has heard of this and a fix, I'd appreciate their feedback.

Terry

 

[vbaInet]

Terry, can you give us some more information.

 

[tquinn]

Terry, can you give us some more information.

Sure. I have a form for inputing data into records. At one spot in the form I have the command =Now()

That command for years would insert the current day's date into the form when I started a new record.

Sometime back (within the last two years) it stopped filling in the date. When I open the form for making the record, instead of filling in the current date in that field, instead the field is filled with this #Name?

This occurred on all my databases starting on that day, and I assumed it had something to do with a limitation of Microsoft Access 97, in that it didn't support that function after a certain date.

As soon as I type anything into any field in the form, the #Name? disappears, and it allows me to fill the three fields of the date manually in this format xx/xx/xx. For the year field, I need to put in 11, and then it will fill that field with 2011.

Terry

 

[vbaInet]

Sounds like corruption has crept in somehow. My advise is you could re-create the form or create a new database shell and import all your objects into it.

By the way, if you put that command in the Default Value of the field and create a new record directly on the table, does it work?

Also, where exactly is the code currently? In what property or event?

 

[tquinn]

Sounds like corruption has crept in somehow. My advise is you could re-create the form or create a new database shell and import all your objects into it.

By the way, if you put that command in the Default Value of the field and create a new record directly on the table, does it work?

Also, where exactly is the code currently? In what property or event?

I don't think it is a corruption of just this particular database. I use this in a group of databases where I keep service records on all my cars (one database for each). When the function stopped working, it stopped on all of them at the same time.

I tried your suggestion to put in a new record in the table. It showed nothing in that field.

Not sure of the meaning of your question about code, property, event.

Thanks for the suggestions.

Terry

 

[vbaInet]

It worked before and it suddenly stopped working + you (probably) didn't change anything that would have affected it. What does that tell you? Corruption or not? Or do you think that Access databases never get corrupted?

Your table, form or database is corrupt. Follow my first advice of creating a new database shell.

Are these tables linked tables?

 

[tquinn]

It worked before and it suddenly stopped working + you (probably) didn't change anything that would have affected it. What does that tell you? Corruption or not? Or do you think that Access databases never get corrupted?

Your table, form or database is corrupt. Follow my first advice of creating a new database shell.

Are these tables linked tables?

Well, it could be as I originally suggested, that this old version of Access just "timed" out on some function that made the date function die. Or some other software .dll overwrote the Access .dll, rendering the date function useless.

While those might be possibilities, you are no doubt suggesting more likely alternatives.

There is no relationship or linkage between the 10 different databases that I had been using for maintenance histories. Those that I've accessed since have all ceased to work on the date function at the same time. In fact, I just opened one that had probably not been accessed in many years, because it was just an archive of an old car I haven't owned in years. It's date function doesn't work either for a new record I just tried to create.

These should be thought of more as flat file databases, because in fact, when I wrote them, I was just trying to replicate the capability of a previous flat file database that ran on an old CP/M system, that had one file for each history log database.

Now all 10 of these forms are in the same Access .mdb file, called "History Logs.mdb", but they are each using their own unique data entry form with its own titles, its own set of reports, and its own table. Is it possible for all 10 data entry forms to corrupt in the same manner all at once, including ones that hadn't even been opened?

Or is there a way for something to break down in the History Logs.mdb file that would affect the date function in all 10 of the forms contained within it? If so, is there a way to transfer these 10 forms, tables, and twenty reports into a new .mdb file that might fix the problem?

Terry

 

[vbaInet]

Linked tables means is your database split into Front End (containing forms, reports, macros, code and queries) and Back End (i.e. table only). Unsplit databases can lead to corruption over time.

Linked tables can also mean that the table are linked to an Excel spreadsheet or an SQL Server or some other db server for example.

Corruption can happen in a table (which is mostly the case), form or report or even controls within the the form and report objects.

Access 97 should have an Import/Export utility which you can use to import all tables, forms, reports, macros, modules/classes and custom menubars/toolbars from the old into the new. I don't have 97' handy so I can't confirm this but look through the menu options.

In the meantime, test the Date() function in the Immediate window and check your References to see if any is missing.