6

New to Libre Office 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.

Anyone have any ideas on how to do this?

Improve this question

4 Answers 4

Reset to default
3

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.

form properties

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:

Improve this answer
7
  • 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
    Commented Feb 2, 2017 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
    Commented Feb 2, 2017 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
    Commented Feb 2, 2017 at 17:13
  • uh-oh. Now I'm getting an error box, "Error inserting the new record / java.lang.IllegalArgumentException"
    – bob.sacamento
    Commented Feb 2, 2017 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
    Commented Feb 2, 2017 at 18:30
3

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

Improve this answer
1

From Jim K’s answer:

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:

enter image description here

Improve this answer
2
  • 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
    Commented Mar 11, 2023 at 11:03
  • Can someone pls tell me the code to set a form unbound Date Field control to the value of a ResultSet Date column? My idea is to have a form which has unbound "DateFrom" and "DateTo" conrtrols. Then set the form date controls initial values to the value of my SystemParam default rpt from and to dates in the Form WhenLoading events, also allowing the date controls to be changed if required. Then when the button to run the rpt is clicked, set a boolean "Selected" column to true for records in the date range before running the report. Then the report only has to select rows where Selected = 1.
    – Chris Good
    Commented Feb 21 at 2:27
1

Wouldn't you know it, I solved my problem shortly after asking for help.

Here is my solution for setting a default value from a database date column into an unbound date control. I'm sure there is a better way than using oCol.String, and I'd be glad to hear a better way.

Sub Form_WhenLoading

' Default control dteDateFrom to SystemParam.DfltRptFrDate
'     and control dteDateTo   to SystemParam.DfltRptToDate

dim oForm as object
dim oControl as object
dim oResultSet as object
dim oContext as object
dim oDB as object
dim oStmt as object
dim sSQL as string
dim oCol as object          ' Column of oResultSet
Dim aDate As New com.sun.star.util.Date

oForm = ThisComponent.DrawPage.Forms.getByIndex(0)
oController = ThisComponent.CurrentController   
oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext")
oDb = oContext.getByName("DatabaseName")
oConn = oDb.getConnection("","")
 
sSQL = "SELECT ""DfltRptFrDate"", ""DfltRptToDate"" from ""SystemParam"" WHERE ""SystemKey"" = '0';"

oStmt = oConn.createStatement()
oResultSet = oStmt.executeQuery(sSQL)

If Not IsNull(oResultSet) Then
    oResultSet.next
End If

' dteDateFrom
oCol = oResultSet.Columns.getByName("DfltRptFrDate")
oControl = oForm.GetByName("dteDateFrom")

' OCol.String is YYYY-MM-DD
aDate.year = left(oCol.String, 4)
aDate.month = mid(oCol.String, 6,2)
aDate.day = right(oCol.String, 2)
If IsEmpty(oControl.Date) Then 
    oControl.Date = aDate
    oControl.text = oCol.String
    oControl.Commit()
End If

' dteDateTo
oCol = oResultSet.Columns.getByName("DfltRptToDate")
oControl = oForm.GetByName("dteDateTo")
aDate.year = left(oCol.String, 4)
aDate.month = mid(oCol.String, 6,2)
aDate.day = right(oCol.String, 2)
If IsEmpty(oControl.Date) Then 
    oControl.Date = aDate
    oControl.text = oCol.String
    oControl.Commit()
End If

End sub
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .