Add Default Record to SubForm For New Record MainForm

English
, ,
#1

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
#2

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-US/text/shared/02/01170202.html?DbPAR=SHARED#bm_id3150499

#3

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 form, but that seems a rather laborious method for a table with over 500 records.

#4

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

#5

OK fair enough, I should have been more explicit;

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

#6

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
#7

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

#8

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

#9

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.

#10

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
#11

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

#12

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.

#13

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

#14

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

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

Simply verify the table update.

#16

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

#17

Have gone through this before. You do not need single quotes but rather surround with apostrophe:

sSQL = "INSERT INTO ""Text Fields Basic Details"" (""TextRef"",""Lingo"",""Text"") VALUES ('" & sTextRef & "','" & iCounter & "','" & sNewText & "')"
#18

Found that out before your comment and got it working just before seeing your reply as I came to report back. Yes you are right, gone through that before as I admitted in an earlier comment. So problem solved, will clean up the post with final working code. In summary, I focused on" record update" and “record change”, not giving much thought to “record action”, although do remember trying a “record action” before update but clearly not both before and after, and of course also omitted to use Form.IsNew. Thank you for your precious help.

#19

From a brief look, it appears you still have a problem with checking for new records. You are currently checking after the record has been updated. This could have moved the cursor from an existing record to potentially a new record and cause record(s) to be created when they shouldn’t.

The trigger appears to be a better method - less prone to error.

#20

I have tested some more and did not encounter any problems. I am reluctant to use the db trigger, another user may get into macros and expect insight, I have macro folders by formname for form-specific macros and try to keep it simple, visual and organized. Even so a macro must work reliably of course. Can you give a problem example?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.