[Solved] Inserting current date in field on new record

[Nocton]

I have a form with a date field. With a new record I want the current date to be inserted in the field. I have searched on the forums and seen this mentioned, but cannot find a solution that works. I am hoping/looking for something relatively simple, either adding to the default for the control on the form or defaulting in the table. Being used to Access, where this could be done simply, I hope Base can also do it simply, yet some of the 'solutions' I have seen look very complicated. Maybe a simple solution has already been given which someone could point me too?

Regards

Nocton

[RPG]

Hello

The most easy method is to assign a current_date to your field in your database. This must be done for your database. Maybe this link can help how to do.

I think the code for you is

Code: Select all

ALTER TABLE "TblWebsites" ALTER COLUMN "Added" SET DEFAULT CURRENT_Date;

Romke

[FJCC]

I just ran the SQL command suggested by RPG from the Tools -> SQL window and the default value works just like I expected it to. I noticed just now that I set the default to CURRENT_DATE (all caps). I'm not sure if the use of lower caps would make a difference.

[Villeroy]

More info: http://user.services.openoffice.org/en/ ... 575#p78467
After setting the default value in the underlying database and after turning off the mentioned setting you have 2 options on form level:
1) Do not show the auto-field or make it read only if the user is not supposed to override the time stamp.
2) Provide an editable field together with a short info that leaving the field blank inserts the current date/time since the auto-value won't be added until the record is actually written to disk (which makes perfect sense IMHO).

[Nocton]

Thank you RPG and Villeroy. I have run RPG's SQL code and it does what I want. Nice and simple, just as I wanted. One question regarding such code - Is there anywhere in the database where I can see what the default setting now is for the field?

I also wished to update the date when a modification is made and looking thorough the suggested links to other posts, I have settled on the following, which is slightly adapted from that given by DrewJensen. I give it here again for completeness in case it may be useful to others:

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.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

I assigned this to the 'Before record action event' of the form.

Thank you again for the help. This forum is a wonderful source of information.

Regards

Nocton

[RPG]

Hello

Is there anywhere in the database where I can see what the default setting now is for the field?

I don't know an easy method. But I remember Sliderule did wrote a query with which you can query each value from your database you want. The problem I cannot find on this moment a link. Maybe this link can help you.

Romke

[Villeroy]

SInce it is a Writer document you may insert a date-time field and update it with the same macro. This way it would show the value for the previously added record.