Apache OpenOffice Community Forum

Posted: **Sat Jun 23, 2018 6:58 pm**

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

Posted: **Sat Jun 23, 2018 8:20 pm**

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.

Posted: **Mon Jul 02, 2018 10:05 am**

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.

Apache OpenOffice Community Forum

Default a date field to CURRENT_DATE?

Default a date field to CURRENT_DATE?

Re: Default a date field to CURRENT_DATE?

Re: Default a date field to CURRENT_DATE?