Automatic insert and commit

English
,
#1

Hi.

The attached database includes two tables, with a few test lines:

CREATE TABLE T_PERSONS (
	PRS_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	PRS_TYPE TINYINT NOT NULL,
	PRS_LASTNAME VARCHAR (32) NOT NULL,
	PRS_FIRSTNAME VARCHAR (32) NOT NULL,
	CONSTRAINT PK_PRS_ID PRIMARY KEY (PRS_ID),
	CONSTRAINT CC_PRS_TYPE CHECK (PRS_TYPE BETWEEN 1 AND 2)
);

CREATE TABLE T_RELATIONSHIPS (
	MBR_ID INTEGER NOT NULL,		-- Member
	AST_ID INTEGER DEFAULT NULL,	-- Assistant	
	CONSTRAINT PK_MBR_ID PRIMARY KEY (MBR_ID),
	CONSTRAINT FK_MBR_ID FOREIGN KEY (MBR_ID) REFERENCES T_PERSONS (PRS_ID),
	CONSTRAINT FK_AST_ID FOREIGN KEY (AST_ID) REFERENCES T_PERSONS (PRS_ID)
);

INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (1, 1, 'Smith', 'Albert');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (2, 1, 'Walsh', 'Christopher');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (3, 1, 'Dutton', 'Carol-Ann');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (4, 1, 'Evans', 'Jenny');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (5, 2, 'Taylor', 'Peter');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (6, 1, 'Deckard', 'Will');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (7, 1, 'Anderson', 'Charlotte');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (8, 2, 'Williams', 'Tracy');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (9, 1, 'Harrigan', 'James');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (10, 1, 'Spencer', 'Oliver');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (11, 1, 'Fergusson', 'Alexander');
INSERT INTO T_PERSONS (PRS_ID, PRS_TYPE, PRS_LASTNAME, PRS_FIRSTNAME) VALUES (12, 2, 'Miller', 'Helena');

INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (1, NULL);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (2, NULL);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (3, 8);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (4, NULL);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (6, 5);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (7, NULL);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (9, NULL);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (10, 5);
INSERT INTO T_RELATIONSHIPS (MBR_ID, AST_ID) VALUES (11, 12);

Members and assistants are in the same table, with a PRS_TYPE column to distinguish them (1 member, 2 assistant).
They are linked in the second table, where each member must be listed with its assistant, or marked as NULL if it has none.

Main rules

  • 1 member can have 0 or 1 assistant.
  • 1 assistant can assist 1 or more members (1 assistant only exists if he assists at least 1 member).

What I want to do is automatically copy PRS_ID to MBR_ID when a new member is created. To do this, I’ve cobbled together this code:

Sub InsertMbrId(objEvent)

	Dim objMainForm As Object
	Dim objSubForm As Object
    Dim objControl As Object
	Dim objConnection As Object
	Dim objStatement As Object
	Dim objResult As Object
	Dim strSQL As String
	Dim intField1 As Integer
	Dim strField2 As String

	objMainForm = ThisComponent.Drawpage.Forms.GetByName("MainForm")
	objSubForm = ThisComponent.DrawPage.Forms.GetByName("MainForm").GetByName("SubForm")
	intField1 = objMainForm.Columns.GetByName("PRS_ID").Value
    objControl = ThisDatabaseDocument.CurrentController

    If Not objControl.IsConnected Then objControl.Connect

    objConnection = objControl.ActiveConnection
    objStatement = objConnection.CreateStatement
    
    strSQL = "SELECT PRS_ID FROM T_PERSONS WHERE PRS_ID = " & intField1 & ""
    objResult = objStatement.ExecuteQuery(strSQL)

    While objResult.Next
    	strField2 = objResult.GetString(1)
    Wend

	objSubForm.UpdateString(objSubForm.FindColumn("MBR_ID"), strField2)
	
End Sub

It works, but it’s incomplete.

  • At the moment, I’m running it by clicking on a button and I’d like it to do this automatically when I enter a new record.
  • I don’t know how to place a “commit” because once the value is copied into the table, it’s only remembered if I change the record.

Thanks for your help.

dbPrimus.odb (15,9 Ko)

#2

not sure where your insert is in all this … :wink:

Macros/Basic/Base - The Document Foundation Wiki #Insert_data_with_SQL

#3

It may not be the best method, but the macro is functional.

Create a new record in the form.
Click on the macro run button.
Return to the previous record to validate (I don’t know how to do it automatically).
Close the form.
Open T_RELATIONSHIPS and you’ll see that the PRS_ID number of T_PERSONS has been correctly entered to MBR_ID.

#4

Still, this macro seems more “academic”.

Sub InsertMbrId

	Dim objForm As Object
	Dim intColumn As Integer
	Dim objControl As Object
	Dim objConnection As Object
	Dim objStatement As Object
	Dim strSQL As String

	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	intColumn = objForm.Columns.GetByName("PRS_ID").Value
	objControl = ThisDatabaseDocument.CurrentController

	If Not objControl.IsConnected Then objControl.Connect

	objConnection = objControl.ActiveConnection
	objStatement = objConnection.CreateStatement

	strSQL = "INSERT INTO T_RELATIONSHIPS (MBR_ID) SELECT PRS_ID FROM T_PERSONS WHERE PRS_ID = " & intColumn & ""

	objStatement.ExecuteUpdate(strSQL)

	objForm.Reload()
	objForm.Last()

End Sub

But it only works when the registration of a new person is validated. This validation should take place when PRS_FIRSTAME’s focus is lost, after keyboard tab action.

#6

@Primus,
for something like this you really need to use 2 macros and a global variable:

  1. fired by the forms before record action.
    I used iAction% as my global variable to read the the value of action, 1 = insert, 2 = update, 3 = delete.
  1. fired by the forms after record action.
    if iAction = 1 then a new record has been inserted into the T_PERSONS table.
    a check is made to ascertain whether we have added a member or assistant.
    if we have a new member then we use the statement service to retrieve the identity (most recent auto value) i.e. (PRS_ID) and insert it into T_RELATIONSHIPS.MBR_ID.

I used my own form with a table control which is probably better suited to the task and aids clarity.
I created the module Module2, it contains 3 small macros, 2 as above, the other to reload the form when changing type.
it should be easy to adapt the macros to suit your needs.
hope there are no silly coding errors, do not have time for thorough testing.
BeforeAfterFormEvents.odb (26.6 KB)

#8

I’ll take a close look. Thanks. :slightly_smiling_face:

#10

@Primus,
if you are happy with your form design and are determined to use it then download this attachment.
I have added a copy of your form named “F_PERSONS_2” and a module called “Module3” which contains 2 tiny macros.
uses your tables and form, seems to work perfectly, tab order is important.
PrimusFormFix.odb (36.4 KB)

#11

It’s even better with simpler macros. Thanks. :wink:

#5

After the MBR_ID string is updated in objSubForm, add these lines.

If objSubForm.isNew() then
objSubForm.insertRow()
Else
objSubForm.updateRow()
Endif

  • This is my simplified interpretation of the macro:

Sub InsertMbrID()
Dim objA, objB, objC 'Objects on single line, comma seperated.
Dim intA% 'symbol % = as integer
objA= thisComponent.drawPage.forms.getByName(“MainForm”)
objB= objA.getByName(“SubForm”)
objC= objB.columns.getByName(“MBR_ID”) 'Subform MBR_ID data field
intA=objA.columns.getByName(“PRS_ID”).value() 'Mainform PRS_ID value
objC.value() = intA
If objB.isNew() then
objB.insertRow()
Else
objB.updateRow()
Endif
End sub

#7

This macro returns a function error at objB.insertRow(). This makes sense to me, since we’re trying to enter the MBR_ID before the PRS_ID is saved in the table.

#9

At the moment, I’ve only found a solution with two macros, since I can’t merge them into one.

I enter the datas in a new record and execute with a button. And here, it’s ok.

Sub Full()
	Call UpdateForm
	Call InsertMbrId
End Sub


Sub UpdateForm

	Dim objForm As Object

	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	
	If objForm.IsNew Then
		objForm.InsertRow()
	Else
		objForm.UpdateRow()
	End If

End Sub


Sub InsertMbrIdSave

	Dim objForm As Object
	Dim intColumn As Integer
	Dim objControl As Object
	Dim objConnection As Object
	Dim objStatement As Object
	Dim strSQL As String

	objForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
	intColumn = objForm.Columns.GetByName("PRS_ID").Value
	objControl = ThisDatabaseDocument.CurrentController

	If Not objControl.IsConnected Then objControl.Connect

	objConnection = objControl.ActiveConnection
	objStatement = objConnection.CreateStatement

	strSQL = "INSERT INTO T_RELATIONSHIPS (MBR_ID) SELECT PRS_ID FROM T_PERSONS WHERE PRS_ID = " & intColumn & ""

	objStatement.ExecuteUpdate(strSQL)

	objForm.Reload()
	objForm.Last()

End Sub

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