insert timestamp in a base field pressing a button in a form

[Uhradone]

Sounds simple but seems to be complicated.
I have a nice base form with two tables a addesses and a filter table.
On the addresses layer I have a button that triggers a macro. That works.
But how can I now insert in the active field of my address tabel in a form insert a timestamp.

Thanks for any hint!

set → timestamp into → active → LETZTER KONTAKT

UPDTAE 20200530:
so far it is not working with 1. Answer so I will add some more information:

• System: Mac

• Libre Version: 6.3.6.2

• internal database HSQLDB embedded

• the base database is called: Kontakte.odb

• Table called: Adressen

• Field called: KONTAKT_ZEIT (Date/Time[TIMESTAMP])

• Form called: F_Adressen
  Form has on main level: the “Filter” Table
  Form has as sub of Filter the: “Adressen” Table
  The “set” Button is part of the “Adressen” Layer as is the target field “Adressen->KONTAKT_ZEIT”

Filter and filtering works as described in the manual with a assistance table to give the filter values.

The insert-timestamp-macro is under:
Kontakte.odb->Standard->_ insertTimestamp → DtTime
it shows also under:
My Macros & Dialogs → Standard → _ Module1 → DtTime

The “set” button on the form “F_Adressen” is next to the field “KONTAKT_ZEIT” that is part of the filter results (Adressen as sub of filter, all on Form) → the button has in the Events:
“Key pressed”: Standard.insertTimestamp.DtTime(document, Basic)

This is the macro now:

Option Explicit

Sub DtTime
    Dim oForm As Object
    Dim oDocCtl As Object
    Dim oField As Object
    Dim oCtlView As Object
    Dim oBoundField As Object
    oForm =ThisComponent.Drawpage.Forms.getByName("F_Adressen")
    Dim aTD As New com.sun.star.util.DateTime
    oField = oForm.getByName("KONTAKT_ZEIT")
    aTD.Day = DAY(Now())
    aTD.Month = MONTH(Now())
    aTD.Year = YEAR(Now())
    aTD.Hours = HOUR(Now())
    aTD.Minutes = MINUTE(Now())
    aTD.Seconds = SECOND(Now())
    oBoundField = oField.BoundField
    oBoundField.updateTimestamp(aTD)
 End Sub

The database file with only two data-sets:

Kopie.odb

[Ratslinger]

Hello,

The information you present is extremely limited - No LO version, OS what database used or field type used.

Edit:

Removed original post as it dealt with two controls on the form. Here it is one.

Use a timestamp field in the table. The test form used a Formatted field control.

Here is the macro code used:

Option Explicit

Sub DtTime
    Dim oForm As Object
    Dim oDocCtl As Object
    Dim oField As Object
    Dim oCtlView As Object
    Dim oBoundField As Object
    oForm =ThisComponent.Drawpage.Forms.getByName("YOUR_FORM_NAME")
    Dim aTD As New com.sun.star.util.DateTime
	oField = oForm.getByName("YOUR_FIELD_NAME")
    aTD.Day = DAY(Now())
    aTD.Month = MONTH(Now())
    aTD.Year = YEAR(Now())
    aTD.Hours = HOUR(Now())
    aTD.Minutes = MINUTE(Now())
    aTD.Seconds = SECOND(Now())
    oBoundField = oField.BoundField
    oBoundField.updateTimestamp(aTD)
End Sub

Here is the result - form and table:

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

Edit 2020-06-03:

Here is a rough working sample ------- PasteTimestamp.odb

There is only one form and the field is st_formatted with the button to execute the macro to its right. Used you posted macro in comment.

It is probable the mentioned SQL would not be a problem either.

Edit #2 on 2020-06-03:

Attached is the repaired Base file ------- KopieFixed.odb

[Uhradone]

should it be for “oField” the table plus the field? something like “Adressen.KONTAKT_ZEIT” ?

[Ratslinger]

@Uhradone,

oField is referring to the control on the form. The table is referenced by the internal form and withing the control the property is set to a bound field.

Do not understand why you basically duplicated my answer in your original question?

[Uhradone]

Sorry if I made some confusion while duplicating the form, I just wanted to refer to the code I have right now (including my specific names and values) and that is for some reason is not working.

In my case the form top layer is the “filter” table and the sub-layer is my “Adressen” table is this the problem? But the button and the bound field are still on the “Adressen” layer.

[Ratslinger]

@Uhradone,

This is why information is so important. The field you are dealing with is on a sub form and not a form. The location of the button is not important.

Replace these lines:

oForm =ThisComponent.Drawpage.Forms.getByName("F_Adressen")
Dim aTD As New com.sun.star.util.DateTime
oField = oForm.getByName("KONTAKT_ZEIT")

with these:

Dim oSubForm as Object
oForm =ThisComponent.Drawpage.Forms.getByName("Filter")
oSubForm =oForm.getByName("F_Adressen")
Dim aTD As New com.sun.star.util.DateTime
oField = oSubForm.getByName("KONTAKT_ZEIT")

If you continue to have problems please post a scrubbed sample of your Base file.

Edit:

Looking at the screen shots again (not the best way for information - sample is better), it may be a sub form name is incorrect in the code you listed:

oSubForm =oForm.getByName("F_Adressen")

possibly should be:

oSubForm =oForm.getByName("Adressen")

[Ratslinger]

So new lines should most likely be:

Dim oSubForm as Object
oForm =ThisComponent.Drawpage.Forms.getByName("Filter")
oSubForm =oForm.getByName("Adressen")
Dim aTD As New com.sun.star.util.DateTime
oField = oSubForm.getByName("KONTAKT_ZEIT")

Don’t know where you got "F_Adressen" from.

[iplaw67]

Just my personal preference perhaps, but would it not be easier just to use a prepared SQL statement to inject or create the timestamp value in the db itself, and which fires when you click on the button, and then simply refresh the form ?

[Uhradone]

Thanks Alex for your additional advice, that’s what I tried to start in the first place, but then I don’t know how I get the selected field in the selected dataset on a form output. What I see on the screen is a selected set of data from all my data I can reach there on the form in many ways but I have no idea how I could target this with SQL exept by typing in the ID and do some find, where, ID. But then I also can typ in the date.

macro not working yet, still try to target the field

[Uhradone]

Now I made things very simple:
Table: TestTable (has Fields: ID, Vorname, KONTAKT_ZEIT as Timestamp)
Form: TestForm (has all Fields plus one Push Button 1)
Push Button 1 has: “Action”: Refresh form; Events Key pressed: macro setTimestamp.DtTime (document, Basic)
The Macro is stored in Test.odb

It is still not working!

Option Explicit

Sub DtTime
    Dim oForm As Object
    Dim oSubForm AS Object
    Dim oDocCtl As Object
    Dim oField As Object
    Dim oCtlView As Object
    Dim oBoundField As Object
    oForm =ThisComponent.Drawpage.Forms.getByName("TestForm")
    Dim aTD As New com.sun.star.util.DateTime
    oField = oForm.getByName("KONTAKT_ZEIT")
    aTD.Day = DAY(Now())
    aTD.Month = MONTH(Now())
    aTD.Year = YEAR(Now())
    aTD.Hours = HOUR(Now())
    aTD.Minutes = MINUTE(Now())
    aTD.Seconds = SECOND(Now())
    oBoundField = oField.BoundField
    oBoundField.updateTimestamp(aTD)
End Sub

[Ratslinger]

@Uhradone,

You have only said it is not working but did not explain what is happening such as if the is an error message and what it is specifically.

I have also noted in a previous comment:

If you continue to have problems please post a scrubbed sample of your Base file.

but don’t see that either. Have created a rough sample. Will place in edited answer shortly.

[Uhradone]

Here is my sample file with only two data-sets

Kopie.odb

(also above in my initial text)

Didn’t get any error messges. Is there a special log-file or something similar to check error codes?
What is strange, is that my very simple test file also doesn’t work.

Sorry for making so much trouble!

[Ratslinger]

@Uhradone,

No need to be sorry. Instead be a bit more cautious in the future. The main problems were that you did not follow the instructions given and you did not use correctly cased/spelled names. Also found a problem with the control on the form and replaced the control. All now works.

Shortly will post repaired file in answer.

[Uhradone]

Thank you very much!
Yehh it works.

Now I got it: In forms it is realy the “name” of the field and not the name of the data base entity that is targeted by the macro.
I thought the “name” is just something for the caption and not something that you would target with a macro. So best practice is to use the same “name” for the form field as the name of the data entity and of course same capital or small letters.
And it is not with the button under Events “Key pressed” but “Execute action”.

So, it works, problem solved.

My next target would be that the field “ANGELEGT” meaning data set created is getting automatically the timestamp on creation, but this is not so important. Would be just nice to have.

[Ratslinger]

No, using the same name for the control and for the field name will cause further confusion and more problems troubleshooting. It is a bad idea. You should have different conventions for each.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.