[Solved] TimeField doesn't default to system time

[joris]

Hi ,

A small NGO, where i sometimes do volunteer work, is planning to switch to linux/ubuntu, because of that they want to switch the Access database to Base. The tables were all ready stored in MySQL. So Access was front end to MySQL.

So far so good. I connected Base to MySQL with the mysql-connector-java-5.0.8. Worked fine. When making queries i got into trouble because of the some date values in the Mysql DATE table were '0000-00-00 00:00:00' and the Java connector refuses to make connections. I could get round this error by changing the JDBC string in jdbc:mysql://localhost:3306/mySqlDatabase?zeroDateTimeBehavior=convertToNull

But when making forms i got in trouble again, because the Date Field wouldn't default to the system time when making a new record. After googling a lot, I finally found out that the date will default to system date, when you add TODAY() in the 'Default date' field in the properties from theDate Field (right mouseclick on the date field-> control)

I hoped TODAY() would work for the Time Field, but it doesn't. When adding a new record it defaults to 00:00. You can change this to the right time, so it is not a major issue, but people working with the form might forget this when adding a new record. So i want it to default to the system time.

Because i noticed not many people seem to have this issue, i thought it might have something to do with the mysql-connector-java-5.0.8, but removing the zeroDateTimeBehavior=convertToNull doesn't change anything.

Does anybody know a solution for this?

[probe1]

You can set date/time to a control via a macro (DrewJensen posted example here in the forum) - or (with MySQL as enigne) set the database column (ALTER definition) to ON UPDATE CURRENT_DATE

Do a search - it was handled in detail earlier....

[joris]

Thanks for your fast reply Winfried. Your post pointed me in the right direction. When i realized that MySQL can be handling the timestamp, there is simply no need for a special time and date field created by Open Office. A simple text field with a link to the MySQL date table should work and that is the case.

Now i understand that the Open Office Timefield cannot default to the system time and is even blocking MySQL to do. Happy i figured that out.

I did do searching on this subject, but expected the answer be Form related, the answer is database or macro related and i didn't expect that.

[Villeroy]

You did not tell us why you need a default date.

Code: Select all

ON UPDATE CURRENT_DATE

Changes the field whenever you change anything.
I believe that ...

Code: Select all

ON INSERT CURRENT_DATE

... is also possible in MySQL in case you want to set the date only for a new record.
You could also set up a default value for the field, which has the effect that the value is put into the field only if the user did not provide one.

A simple text field with a link to the MySQL date table should work and that is the case.

Yes, this will show the old previously stored value or none in case it's a new record. I believe that the first two methods will override the user's input anyway as soon as the form submits the record. The third method (default value) will accept the form input and applies the default if no input was given.
[Sorry for two times saying "I believe". I don't have any database installed since years.]