Default a date field to CURRENT_DATE?

Creating and using forms

Default a date field to CURRENT_DATE?

Postby JWB23 » 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
OpenOffice 4 on Windows 10
JWB23
 
Posts: 9
Joined: Wed Jun 20, 2018 12:02 am

Re: Default a date field to CURRENT_DATE?

Postby Villeroy » 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26564
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Default a date field to CURRENT_DATE?

Postby Nocton » 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   Expand viewCollapse view
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.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 495
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests