The macro below worked OK before, throws now a different error in Linux and Win 10

[hermanv]

LibreOffice 7.0.4.2 on Linux Mageia 7 and Windows 10, both fully updated.
The macro below has been working with previous versions of LO, but now fails

Sub insertarch
Dim DatabaseContext As Object
Dim DataSource As Object
Dim opand as object
Dim olid as object
Dim ostartdat as object
Dim spand as integer
Dim slid as integer
Dim sstartdat as String
Dim oForm as object
Dim oConn as object
Dim oQuery as object

oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
olid = oForm.getByName("fmtLidnr")
opand = oForm.getByName("fmtPandidnr")
ostartdat=oForm.getbyname("fmtStartdat")
spand = opand.getCurrentValue()
slid = olid.getCurrentValue()
sstartdat=ostartdat.getCurrentValue()
theDay = MID(sstartdat, 1, 2)
theMonth = MID(sstartdat, 4, 2)
theYear = MID(sstartdat, 7, 4)
goodDate = theYear & "-" & theMonth & "-" & theDay

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("volks") Rem My DB name
oConn = DataSource.getConnection("","")
oQuery = oConn.CreateStatement()

msgbox ("sstartdat",0,"Datumwaarde")

If goodDate = "--" Then
SQL = "INSERT INTO ""Pandenarchief"" (""pandidnr"",""lididnr"") values ('" & spand & "','" & slid & "')"
oQuery.executeUpdate(SQL)
Else
SQL = "INSERT INTO ""Pandenarchief"" (""pandidnr"",""lididnr"",""Startdatum"") values ('" & spand & "','" & slid & "','"& goodDate &"')"
oQuery.executeUpdate(SQL)
Endif
REM EVENT.SOURCE.ACTIVECONNECTION.Parent.flush

End Sub

On Linux it stops after the line “oForm = ThisComponent.Drawpage.Forms.getByName(“MainForm”)” with error NoSuchElementException. Switching the lines that follow between each other makes no difference.

On Win10, it goes on, but throws a formatting erro on the second sql Insert (thus after the “else” clause) . The error is "conversion error from string -01-43. The date field in the form reads 01/01/18. This seems like it takes again the date as a number. The code to get the gooddate I got from another question on the forum here some time ago.

[Edit - Opaque] Formatted code as preformatted text for readability

[Ratslinger]

Hello,

I see no reason for the code to stop working unless something else has changed. The Linux problem should be the same in Windows - if the internal form name is not found then error. Are there two different Base files? In the noted past question regarding dates your comment stated a problem with blank dates. Do not see any code here to check for that situation. Is this part of the Windows problem?

Please post a sample Base file (edit original question to add) which contains the error. Do not include any personal or confidential information.

[hermanv]

To make sure, I copied the involved odb and odt (it is a standalone form) from the Win10 to the Linux machine, and I hit exactly the same NoSuchElementException on Linux.
The blank date issue has been solved by the if statement on goodDate.
I uploaded the complete application in bug 140453, The odt in question here is Toewijzingenarchiveren.odt

[hermanv]

There is something wrong in the system installed. I explain:
First an apology: The Libreoffice version I run on Mageia Linux 7 is 6.7.4.2.
But I run the same files in PCLinuxOS which has 7.0.4.2 and have the same error.
Running Mageia 8 which has 7.0.4.2 and have the same error.
But the error message points to: com.sun.star.container and at the end has
Message: xxxxxxxxxxxxxx/forms.source.misc.InterfaceContainer.cxx:695.The xxxxxxxx-part is different in the three cases.

[Ratslinger]

Hello,

Will attempt to give you something to use.

First, please be considerate when presenting information. Had asked for a sample which you provided. However, you did not state the code in this post was in one of 16 standalone documents. Nor did you state which document it may be in. Turned out to be the last one in the list. Wasted time just searching.

Had referenced in the comment about internal form names. Turns out this form name was correct - MainForm. The problem was that there was two internal forms with the same name:

Changing the second one to MainForm2 corrected one issue.

Without the form would not know that the field you were using was a Formatted field. The data from this field can be extracted with the following:

myDate=ostartdat.Text
goodDate = Year(myDate) & "-" & Month(myDate) & "-" & Day(myDate)

which replaces:

theDay = MID(sstartdat, 1, 2)
theMonth = MID(sstartdat, 4, 2)
theYear = MID(sstartdat, 7, 4)
goodDate = theYear & "-" & theMonth & "-" & theDay

Also change:

If goodDate = "--" Then

To:

If sstartdat = "" Then

There is a multitude of things to cover when dealing with macros. You should reference Andrew Pitonyaks book OpenOffice Macros Explained. PDF here → www.pitonyak.org/oo.php

There is a section in there on dates.

[hermanv]

Implemented your code and that seems to do the trick in Linux. Tx. Checked that in Win 10 I had the same problem with the MainForm. It had the double, so why it did not throw that error, beats me. Anyway, copied the odt from the Linux to the Win 10 and checked it performed OK.
I bookmarked the Macros book