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

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

• System: Mac
• Libre Version: 6.3.6.2
• internal database HSQLDB embedded
• the base database is called: Kontakte.odb
• 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
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:

C:\fakepath\Kopie.odb

edit retag close merge delete

Sort by » oldest newest most voted

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:

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

more

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

( 2020-05-30 14:54:31 +0100 )edit

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?

( 2020-05-30 19:08:19 +0100 )edit

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.

( 2020-05-30 19:25:33 +0100 )edit

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")
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")

( 2020-05-30 19:43:26 +0100 )edit

So new lines should most likely be:

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


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

( 2020-05-30 23:22:40 +0100 )edit

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 ?

( 2020-06-02 13:32:35 +0100 )edit

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

( 2020-06-03 13:05:46 +0100 )edit

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

( 2020-06-03 14:05:45 +0100 )edit

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.

( 2020-06-03 18:47:17 +0100 )edit

Here is my sample file with only two data-sets

https://transfer.zazu.berlin/index.ph...

(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!

( 2020-06-03 21:31:10 +0100 )edit