Ask Your Question
0

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

asked 2020-05-29 22:17:05 +0100

Uhradone gravatar image

updated 2020-06-03 21:32:56 +0100

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!

image description

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

image description

image description

image description

The database file with only two data-sets:

C:\fakepath\Kopie.odb

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-05-29 23:26:28 +0100

Ratslinger gravatar image

updated 2020-06-03 21:59:09 +0100

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:

image description

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

edit flag offensive delete link more

Comments

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

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

@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?

Ratslinger gravatar imageRatslinger ( 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.

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

@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 gravatar imageRatslinger ( 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")
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.

Ratslinger gravatar imageRatslinger ( 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 ?

Alex Thurgood gravatar imageAlex Thurgood ( 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

Uhradone gravatar imageUhradone ( 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
Uhradone gravatar imageUhradone ( 2020-06-03 14:05:45 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 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!

Uhradone gravatar imageUhradone ( 2020-06-03 21:31:10 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-05-29 22:17:05 +0100

Seen: 141 times

Last updated: Jun 03 '20