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?

Giacomo1968
  • 58,727
bob.sacamento
  • 741

4 Answers4

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:

Jim K
  • 4,439
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

Salvador
  • 131
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

Giacomo1968
  • 58,727
David
  • 119
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
Chris Good
  • 113