Default a date field to CURRENT_DATE?

Creating and using forms
Post Reply
JWB23
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Default a date field to CURRENT_DATE?

Post by JWB23 »

I have a date field on my form that I want to default to today’s date when doing data entry. I took three approaches when going at this but all were unsuccessful:

1. Tried changing the field in question at the form level by setting the “Default date” on the Properties pop-up menu (by right-clicking the field and selecting Control), but the little calendar that came up didn’t have a “Today” button on it like it does when actually running the form and doing data entry. So that method was a dead end.

2. Tried changing the Default Value at the Table Design level (near the bottom of the screen) to CURRENT_DATE. It seemed to like this okay but it didn’t “take” because when I came back to it later, it had disappeared. Strike two.

3. I ran the following command on the SQL screen: ALTER TABLE "MyTbl" ALTER COLUMN "MyDateFld" SET DEFAULT CURRENT_DATE. It also liked this okay because I got “command successfully executed” but when I went into my form the field there displayed the date “01/01/1800”. Not good.

So now I’m stumped. Any ideas?

- thx
OpenOffice 4 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Default a date field to CURRENT_DATE?

Post by Villeroy »

You can display today's date as a text field (menu:Insert>Fields>Date) but you can not easily prefill a form control with a value that is not yet stored in the database.
3. I ran the following command on the SQL screen: ALTER TABLE "MyTbl" ALTER COLUMN "MyDateFld" SET DEFAULT CURRENT_DATE. It also liked this okay because I got “command successfully executed” but when I went into my form the field there displayed the date “01/01/1800”. Not good.
You have to call menu:View>RefreshTables to tell Base that it should re-read the whole database.
The form displays values that are either stored in the database or values modified by you in this editing session. Then you either cancel your changes or store the new record without any date given. The database will substitute the missing date with the current date. The default value won't affect existing records after some modification. Only new records get a default value.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Default a date field to CURRENT_DATE?

Post by Nocton »

I use this Basic code called from the 'Before record action' event of the form. The updated field is DateUpdated:

Code: Select all

Sub onUpdateRecord( oEvent as object )
' to update date field with current date
dim oDataForm, oStatement, oResultSet as variant
if oEvent.Source.SupportsService( "com.sun.star.form.component.DataForm" ) then
	if oEvent.Action = com.sun.star.sdbc.ChangeAction.INSERT or oEvent.Action = com.sun.star.sdbc.ChangeAction.UPDATE then
    	oDataForm = oEvent.Source
        oStatement = oDataForm.ActiveConnection.CreateStatement
        oStatement.EscapeProcessing = False
        oResultSet = oStatement.executequery( "CALL CURRENT_TIMESTAMP" )
        oResultSet.next
        oDataForm.updateTimeStamp( oDataForm.FindColumn( "DateUpdated" ), oResultSet.getTimestamp( 1 ) )
	End if
End if
End Sub
You should be able to adapt it for your needs.
OpenOffice 4.1.12 on Windows 10
Post Reply