Libreoffice Base: Need today's date as default value in a form field

Question

New to libreoffice Base. I've found how to set the default value in a table to the current date. Now I need to have the current date show up in the corresponding field in a form. I have tried inserting into "default value" TODAY() and CURRENT_DATE. They both give me Nov. 18, 1899 for some reason. And ideas how to do this?

score 2 · Answer 1 · answered Nov 03 '17 at 20:15

This macro writes the date of today to the Date Field myDateField:

Sub writeDate
    Dim today As New com.sun.star.util.Date
    today.Month = Month( Now )
    today.Day = Day( Now )
    today.Year = Year( Now )
    form = ThisComponent.DrawPage.Forms(0)  ' first form
    form.myDateField.BoundField.UpdateDate( today )
End Sub

To assign the action: Form Navigator > myForm > Form Properties > Events > e.g. When loading

score 1 · Answer 2 · answered Feb 02 '17 at 09:00

1

Use the following macro code. Here, the table's column (not the control name) is called "MyDate".

Sub DefaultDateInForm (oEvent As Object)
    oForm = oEvent.Source
    lDateCol = oForm.findColumn("MyDate")
    If oForm.getString(lDateCol) = "" Then
        dateStamp = Format(Now, "YYYY-MM-DD")
        oForm.updateString(lDateCol, dateStamp)
    End If
End Sub

Edit the form, and in form properties, assign the macro to the "after record change" event.

Now, whenever a record's date is empty, such as when a new record is started, the date field should default to the current date.

There are several discussions about this topic on the openoffice forum:

answered Feb 02 '17 at 09:00

Jim K

3,839
1
8
20

Thanks, but not much progress. Now I'm getting 25 Dec 1899. Hey, at least it's Christmas! Will check out your links. – bob.sacamento Feb 02 '17 at 16:13
Perhaps there is something different about your system's date settings, or the control? It worked on my machine. Try changing `"YYYY-MM-DD"` to a different format -- does it literally say "Dec. 25, 1899" in the date control? Mine shows the format "02/01/17". To find the problem, it may help to `MsgBox(dateStamp)` and `MsgBox(oForm.getString(lDateCol))`. – Jim K Feb 02 '17 at 16:34
Well, no matter how I format it, it is that date. Well, as I try a few different things, it changes. Actually got 1 Jan 0002 once and a few other weird ones. I'm new at this. Don't know what you mean by MsgBox(). But thanks! – bob.sacamento Feb 02 '17 at 17:13
uh-oh. Now I'm getting an error box, "Error inserting the new record / java.lang.IllegalArgumentException" – bob.sacamento Feb 02 '17 at 17:17
For example, add `MsgBox(dateStamp)` on the next line after `dateStamp = Format(Now, "YYYY-MM-DD")`. The error may indicate that the format isn't right yet. – Jim K Feb 02 '17 at 18:30
Join this chat room and hopefully we can figure out the problem: http://chat.stackexchange.com/rooms/52946/superuser-question-1174199 – Jim K Feb 02 '17 at 18:44

score 0 · Answer 3 · answered Nov 20 '22 at 08:41

From @Jim K answer:

discussions about this topic on the openoffice forum: https://forum.openoffice.org/en/forum/viewtopic.php?p=495533&sid=1543d4682d62ce59fc2850853366b38a#p495533):

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

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

replace "tbl" and "col" with the actual names of table and column. [Execute]

Checked and works perfectly:

Actually, the OP is asking for solution dealing with updating date field with CURRENT DATE right on the FORM not after adding a NEW RECORD in the TABLE. — ThN, Mar 11 '23 at 11:03

Libreoffice Base: Need today's date as default value in a form field

3 Answers3