We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Add Default Record to SubForm For New Record MainForm

asked 2020-04-11 00:48:51 +0200

Ardee gravatar image

updated 2020-04-13 22:24:19 +0200

My environment: Windows 10, LibreOffice Version: 6.4.2.2 (x64) HSQLDB Version 2.3.2 Split Database

A Main Table has a sub Table in a one-to-many relationship. My objective is to force add a default record in the sub Table when a new record is added to the Main Table. I would like to do this with a macro triggered by the update event in the main table.

I'm stuck on finding the appropriate code and event trigger to get the ID (a text ID primary key) of the newly created record in a variable, I can handle the rest of the code from there. Thank you for any help.

EDIT: More Info

I tried Sub AddNewRecord on Before Updating of the Control and Sub AfterAddRecord on After Updating of Control, nothing happens (No MsgBox).

I tried Sub AddNewRecord on Before Record Change of the Form and Sub AfterAddRecord of the Form, this triggers the MsgBox with Null on opening, a value on clicking on any record, and a Null MsgBox after adding a new record.

Maybe fatigue has reduced me to not seeing the obvious.

EDIT 2020-04-13: Solved

For those interested here is the final working code (the ID to add as new record is called "TextRef"):

One could consider adding a lookup routine on the When Loading event of the form to check if there are any default records (ones with content "unspecified") in the subtable and if so provide a MessageBox alert naming the record that needs attention.

 Global sGlobalAftRecChange As String
Sub MacroAfterRecordChange
    REM this is attached to the forms --After Record Change-- event
    Dim oForm As Object
    Dim oField As Object
        sGlobalAftRecChange = ""
        oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
        oField = oForm.getByName("TextRef")
        sFieldData  = oField.Text
        If sFieldData = "" Then
        sGlobalAftRecChange = "NewRecord"
        End If
End Sub

Global sGlobalHold As String
Sub MacroBeforeRecordAction
    REM this is attached to the forms --Before Record Action-- event
    Dim oForm As Object
    Dim oField As Object
        sGlobalHold = ""
        oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
        oField = oForm.getByName("TextRef")
        sGlobalHold = oField.Text
End Sub

Sub MacroAfterRecordAction
    REM this is attached to the forms --After Record Action-- event
    REM Add default language records for a new TextRef
    Dim oForm As Object
    Dim sTextRef As String
    Dim sNewText As String
    Dim sSelect As String
            If sGlobalHold = "" Then
            Exit Sub
            End If
        If sGlobalAftRecChange = "NewRecord" Then
        sTextRef = sGlobalHold
        sNewText = "Unspecified"
        iCounter = 1
        Do While (iCounter < 7)
            sSQL = "INSERT INTO ""Text Fields Basic Details"" (""TextRef"",""Lingo"",""Text"") VALUES ('" & sTextRef & "','" & iCounter & "','" & sNewText & "')"
        RunSomeSQL (sSQL)
        iCounter = iCounter + 1
        Loop
        End If
End Sub

    Sub RunSomeSQL (sSqlTask As String)
        Dim oStatement As Object
              If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
              ThisDatabaseDocument.CurrentController.connect
              End If
              oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
              On Error GoTo ErrorSituation
              oStatement.execute(sSqlTask)
              Exit Sub
              ErrorSituation:
              MsgBox("Invalid Sql= " & Chr$(13) & Chr$(13) &  sSqlTask)
    End Sub
edit retag flag offensive close merge delete

Comments

Hello,

Do not quite understand. If the primary key is a text field (a text ID primary key) as stated it is not an auto increment field and the field is accessible as is any other field since it needed to be entered.

Could you please clarify?

Events are:

https://help.libreoffice.org/6.4/en-U...

Ratslinger gravatar imageRatslinger ( 2020-04-11 06:09:46 +0200 )edit

Hello,

I know about the various events, maybe I missed trying something, but every time I try to pick up the text ID field contents from the form after a trigger event, it's either empty or contains the pre-existing previous record.. Probably key to seeing why I meet this problem, both main and subform are a grid on the form, so there is no button clicked to save a new record. So when I enter a new text ID on the main table grid,, after the enter key it jumps to the next blank new record field ready for another one. I thought of running a macro "after update" on the form that iterates through all the text ID fields on both the main and sub table to find the new record in the main table and then force add the default in the sub table and refresh the ...(more)

Ardee gravatar imageArdee ( 2020-04-11 11:36:33 +0200 )edit

I know about the various events

The link was posted because in the question you stated:

I'm stuck on finding the appropriate code and event trigger

Ratslinger gravatar imageRatslinger ( 2020-04-11 16:26:40 +0200 )edit

OK fair enough, I should have been more explicit;

I'm stuck on finding the appropriate code and appropriate event trigger

Ardee gravatar imageArdee ( 2020-04-11 22:00:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-04-11 16:37:51 +0200

Ratslinger gravatar image

Hello,

There are choices. For one, on the before record action, save the key to a global variable. Then after the record update use the global variable information to create your other record.

There is also the option to use a database trigger. Here is a similar one which can easily be adapted to your situation:

CREATE TRIGGER INSERT_TRIG
AFTER INSERT ON TEST1

REFERENCING NEW ROW AS NEWROW
FOR EACH ROW WHEN (newrow.TESTID > 1)
BEGIN ATOMIC INSERT INTO PUBLIC."CREATEDTOTALS"("TOTALID","NAME","TTLAMOUNT1","TTLAMOUNT2")VALUES(NEWROW.TESTID,NEWROW.NAME,0,0);END

When a new record is entered into "TEST1", the ID and Name fields are used in creating a new record in table "CREATEDTOALS".

To remove the trigger from the database, use:

DROP TRIGGER PUBLIC.PUBLIC.INSERT_TRIG
edit flag offensive delete link more

Comments

@Ratslinger thank you for your suggestions. I edited the question to show what I was doing. I already did some trials with before record action but maybe missed something and will test again. Will also test your database trigger suggestion. Thank you.

Ardee gravatar imageArdee ( 2020-04-11 22:25:18 +0200 )edit

Regarding the database trigger, where do I put this? The remove line, is this a function to add to the other macros? Thank you

Ardee gravatar imageArdee ( 2020-04-11 22:38:42 +0200 )edit

First, did a Q&D with Before & After record action and had not problems. In your code did not see global variable defined. Nor is there any code to verify if you are dealing with a new record.

As for the trigger, the CREATE statement in entered into the database via Tools->SQL. The DROP statement is only used (again via Tools->SQL) if you want the trigger removed from the database.

Refer to you database documentation for information on triggers and procedures.

Ratslinger gravatar imageRatslinger ( 2020-04-11 23:17:27 +0200 )edit

The Global variable is defined elsewhere, didn't think of including it in the post thinking its name would make it obvious. Well I got it working indeed I needed to add in a Form.IsNew. Now there's a syntax problem on the sql, ughhh, do you see it?

Sub RunSomeSQL (sSqlTask As String)
    Dim oStatement As Object
          If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) then
          ThisDatabaseDocument.CurrentController.connect
          End If
        oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
        On Error GoTo ErrorSituation
        oStatement.execute(sSqlTask)
        Exit Sub
        ErrorSituation:
        MsgBox("Invalid Sql= " & Chr$(13) & Chr$(13) &  sSqlTask)
End Sub
Ardee gravatar imageArdee ( 2020-04-12 00:26:32 +0200 )edit

Sorry abt sql syntax, we've been through that before for a similar routine, will check it out.

Ardee gravatar imageArdee ( 2020-04-12 00:43:35 +0200 )edit

Can't see how I can possibly answer as you have not actually presented the SQL used here. It may be the SQL from the original post but that is a guess as this routine is different also. Possibly even with the SQL it may not be enough as there is no definition of the table itself.

Ratslinger gravatar imageRatslinger ( 2020-04-12 00:56:15 +0200 )edit

Fixed the sql (and edited the question that now shows the correct syntax), however still a small problem. The subTable has an auto-increment field called "Detail ID", I did not include this in the insert, is that right or wrong thing to do?. Thank you

Ardee gravatar imageArdee ( 2020-04-12 01:01:20 +0200 )edit

Fyg the bad syntax sql in my origonal posting that has incorrect syntax for variables was as follows:

sSQL = "INSERT INTO ""Text Fields Basic Details"" (""TextRef"",""Lingo"",""Text"") VALUES (sTextRef,iCounter,sNewText)"
Ardee gravatar imageArdee ( 2020-04-12 01:07:45 +0200 )edit

As it is the key (and an auto increment field), should not be needed in the SQL statement.

Simply verify the table update.

Ratslinger gravatar imageRatslinger ( 2020-04-12 01:11:22 +0200 )edit

After some tests I discover the syntax is still wrong: The routine's MsgBox error response is

Invalid Sql= 
INSERT INTO "Text Fields Basic Details" ("TextRef","Lingo","Text") VALUES ("zzz","1","Unspecified")

Running this in an sql window I discover the error is the values, they should be in single quotes, not double quotes, in order to work.

So can you tell me how to syntax the sql line with the variables in order to get single quotes on the values? Thank you

Ardee gravatar imageArdee ( 2020-04-12 01:54:15 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-04-11 00:48:51 +0200

Seen: 101 times

Last updated: Apr 13 '20