Getting date field auto filled with current date

[BryanW]

Hi- Trying to get a form to auto fill a date field with the current date. Have searched both google and this forum and another and what I found went way over my head or it referenced a SQL database which I am pretty sure I am not using unless it always uses SQL. I am VERY new to base(or anything to do with db's).

In my table under the field of the entry date, I tried to use CURDATE() as the default value but that just inputs 01/01/1800 as the date in my form. I did not setup any SQL stuff(this is VERY simple db just to learn abit about db's and to track my freebie/samples that I order). I read quite a few suggestions that mention macros, but I feel that is way over my head. It just seems that there should be an easy way to set up a date field to use the current date as a default but maybe my expectations are to high. Any ideas/tips/macros for dummies topics you could point me to?

Thank you

[r4zoli]

I am VERY new to base(or anything to do with db's). ...

[Solved] Automatic timestamp assignment how?

[Ccamel]

Hi, this used to be my nightmare since I discovered a BoundField through Xray...

Here is my code (used for a cell of table grid):

Code: Select all

Sub FillToday(oev As Object)
	If oev.source.model.BoundField.Long=0 Then
		Dim d As new com.sun.star.util.Date
		d.Day=Day(Date())
		d.Month=Month(Date())
		d.Year=Year(Date())
		oev.source.model.BoundField.Value=d
	End If
End Sub

Explanation:

• oev - event object of a form element
• BoundField has a WriteOnly property Value which is a Date struct in DateField, so U need to send to it the same struct -

  com.sun.star.util.Date

  . To read a BoundField value U can use every possible cast - Long, Int, String, whatever...

This took 2 years for me to discover ...
Have much fun with it.

[talessx]

Hi! after a lot of blanqe nights I've find this very low level solution:

=========================================================


'from italian date to dateserial for form inserting
Function datait_serial(data_it As String) As Integer
Dim arr As Variant
arr = split(data_it, "/")
If UBound(arr)<2 Then Exit Function
datait_serial = dateserial(arr(2),arr(1),arr(0))
End Function


'from italian date to SQL date (ie 05/02/1999 -> 1999-02-05)
Function datait_sql(data_it As String) As String
Dim arr As Variant
arr = split(data_it, "/")
'se già in formato sql esci
If Ubound(arr)=0 Then
arr = split(data_it, "-")
If Ubound(arr)=2 Then
datait_sql=data_it
Exit Function
Endif
Endif
datait_sql = arr(2) & "-" & arr(1) & "-" & arr(0)
End Function



Sub sticazzi(e)
.....
dtNsc="10/04/1985"
.....
Form=e.source.model.parent
ctl = Form.getByName("datdata_nascita")
ctl.boundfield.updateint(int(datait_serial(dtNsc)))
ctl.boundfield.updateString(datait_sql(dtNsc))
End Sub


EVVIVA !!!!!!!!!!!!!!!!!!!!!!!!!

[Villeroy]

Without any macro you can define the current date as default value for the table. It is inserted when you save a new record with the date missing.
menu:Tools>SQL...

Code: Select all

ALTER TABLE "tbl" ALTER COLUMN "col" DATE DEFAULT CURRENT_DATE

replace "tbl" and "col" with the actual names of table and column.
[Execute]
then activate the tables section and call menu:>View>Refresh Tables

For times:

Code: Select all

ALTER TABLE "tbl" ALTER COLUMN "col" DATE DEFAULT CURRENT_TIME

For time stamps:

Code: Select all

ALTER TABLE "tbl" ALTER COLUMN "col" DATE DEFAULT CURRENT_TIMESTAMP

This will _not_ insert any data into the form which would be pointless when using a time stamp field because there may be minutes or hours between loading the form and actually storing its content.
You can omit the date/time column on the form and leave it up to the database.
You can add some label such as "Date (current date when missing)"

[talessx]

Ok thanks!

However sometimes it's useful to insert dates different from the present time, fetching dates from external data.
For exemple the fiscal code DNDMSM66P04H501M means you are born in Rome on 04/09/1966 and this can be automatically obtained using a macro script.