SUB TextSelectionSaveValue(oEvent AS OBJECT)
	REM Macro should be connected to 'Form' → 'Before Rowchange'
	DIM oDatasource AS OBJECT
	DIM oConnection AS OBJECT
	DIM oResult AS OBJECT	
	DIM oSQL_Command AS OBJECT
	DIM NameIDField AS STRING
	DIM NameTableField1 AS STRING
	DIM NameTableField2 AS STRING
	DIM stFieldSeparator AS STRING
	DIM NameTable1 AS STRING
	DIM NameTable2 AS STRING
	DIM NameTab12ID AS STRING
	DIM Position AS INTEGER
	DIM oFieldList AS OBJECT
	DIM oField AS OBJECT
	DIM oForm AS OBJECT
	DIM stQuery AS STRING
	DIM stContent AS STRING
	DIM i AS INTEGER
	DIM stContentField2 AS STRING
	DIM a_stParts() AS STRING
	DIM stmsgbox1 AS STRING
	DIM stmsgbox2 AS STRING
	DIM inID1 AS INTEGER
	DIM inID2 AS INTEGER
	DIM LengthField1 AS INTEGER
	DIM LengthField2 AS INTEGER
	
	IF InStr(oEvent.Source.ImplementationName,"ODatabaseForm") THEN
	REM 'Before Rowchange' fires two different events. 
	REM Only one event is needed. With this event the form could be detected.	
		oForm = oEvent.Source
		aComboboxen() = Split(oForm.getByName("combofields").Tag,",") 'Name of comboboxes is written down in the tag of a hidden control
		FOR inCom = LBound(aComboboxen) TO UBound(aComboboxen)			
			NameTable2 = ""	'If there are more comboboxes and one is related to more than one table NameTable2 must be set empty before looking for next combobox.
			a() = Split(Trim(aComboboxen(inCom)),">")	'Split only needed for tablecontrol to get contact to the fields
			IF Ubound(a) > 0 THEN
				oFieldList = oForm.getByName(a(0)).getByName(a(1))	'Field in a tablecontrol
			ELSE	
				oFieldList = oForm.getByname(a(0))
			END IF
			stQuery = oFieldList.ListSource	'SQL-Code of combobox will be read.
			aFields() = Split(stQuery, """")
			stContent = ""
			FOR i=LBound(aFields)+1 TO UBound(aFields)
				REM All what is not needed for creating a new query will be cut. 
				REM Parts will be joined again with a unusual combination of chacaters (§§) as a new array.
				REM FROM separates fields, the sohwn content in the combobox, from tables.
				REM WHERE separates relations from tables. Joins are not supported by this macro.
				IF Trim(UCASE(aFields(i))) = "ORDER BY" THEN
					EXIT FOR
				ELSEIF Trim(UCASE(aFields(i))) = "FROM" THEN
					stContent = stContent+" §§ "
				ELSEIF Trim(UCASE(aFields(i))) = "WHERE" THEN
					stContent = stContent+" §§ "
				ELSE
				stContent = stContent+Trim(aFields(i))
				END IF
			NEXT i
			aContent() = Split(stContent, " §§ ")
			REM The shown content of teh combobox would be split to the content of different fields if necessary.
			aFirst() = Split(aContent(0),"||")
			IF UBound(aFirst) > 0 THEN
				IF UBound(aContent) > 1 THEN
					REM First part contains 2 fields. Both are written down as "table"."field" .
					REM Second part contains 2 tables, which could be detected also by analyzing the first part.
					REM Third part contains a relation to a foreignkey, separated by '=' .
					aTest() = Split(aFirst(0),".")
					NameTable1 = aTest(0)
					NameTableField1 = aTest(1)
					Erase aTest
					stFieldSeparator = Join(Split(aFirst(1),"'"),"")
					aTest() = Split(aFirst(2),".")
					NameTable2 = aTest(0)
					NameTableField2 = aTest(1)				
					Erase aTest
					aTest() = Split(aContent(2),"=")
					aTest1() = Split(aTest(0),".")
					IF aTest1(1) <> "ID" THEN
						NameTab12ID = aTest1(1)
						IF aTest1(0) = NameTable1 THEN
							Position = 2
						ELSE
							Position = 1
						END IF
					ELSE
						Erase aTest1				
						aTest1() = Split(aTest(1),".")				
						NameTab12ID = aTest1(1)				
						IF aTest1(0) = NameTable1 THEN
							Position = 2
						ELSE
							Position = 1
						END IF
					END IF
				ELSE
					REM First part contains 2 fields without tablenames. Could contain also a separator.
					REM Second part contains 1 table. 
					REM No third part available
					IF UBound(aFirst) > 1 THEN
						NameTableField1 = aFirst(0)					
						stFieldSeparator = Join(Split(aFirst(1),"'"),"")
						NameTableField2 = aFirst(2)
					ELSE				
						NameTableField1 = aFirst(0)
						NameTableField2 = aFirst(1)
					END IF
					NameTable1 = aContent(1)
				END IF
			ELSE
				REM Only one field of one table
				NameTableField1 = aFirst(0)
				NameTable1 = aContent(1)
			END IF
			LengthField1 = ColumnSize(NameTable1,NameTableField1)
			IF NameTableField2 <> "" THEN
				IF NameTable2 <> "" THEN
					LengthField2 = ColumnSize(NameTable2,NameTableField2)
				ELSE
					LengthField2 = ColumnSize(NameTable1,NameTableField2)
				END IF
			ELSE
				LengthField2 = 0
			END IF
			stContent = oFieldList.getCurrentValue()
			REM Spaces at beginning and end of text will be cut
			stContent = Trim(stContent)
			IF stContent <> "" THEN
				IF NameTableField2 <> "" THEN
				REM If there is a second field of a table the content of the combobox must be splitted.
					a_stParts = Split(stContent, stFieldSeparator, 2)
					REM Maximum are 2 parts
					IF Position = 2 THEN
						stContent = Trim(a_stParts(0))
						IF UBound(a_stParts()) > 0 THEN
							stContentField2 = Trim(a_stParts(1))
						ELSE
							stContentField2 = ""
						END IF
					ELSE			
						stContentField2 = Trim(a_stParts(0))
						IF UBound(a_stParts()) > 0 THEN
							stContent = Trim(a_stParts(1))
						ELSE
							stContent = ""
						END IF
					END IF	
				END IF
				IF (LengthField1 > 0 AND Len(stContent) > LengthField1) OR (LengthField2 > 0 AND Len(stContentField2) > LengthField2) THEN
					stmsgbox1 = "Field "+NameTableField1+" must not exceed "+LengthField1+ " characters in length."+CHR(13)
					stmsgbox2 = "Field "+NameTableField2+" must not exceed "+LengthField2+ " characters in length."+CHR(13)
					IF (LengthField1 > 0 AND Len(stContent) > LengthField1) AND (LengthField2 > 0 AND Len(stContentField2) > LengthField2) THEN
						msgbox ("The entered text is too long."+CHR(13)+stmsgbox1+stmsgbox2+"Please shorten it.",64,"Invalid entry")
					ELSEIF (LengthField1 > 0 AND Len(stContent) > LengthField1) THEN
						msgbox ("The entered text is too long."+CHR(13)+stmsgbox1+"Please shorten it.",64,"Invalid entry")
					ELSE
						msgbox ("The entered text is too long."+CHR(13)+stmsgbox2+"Please shorten it.",64,"Invalid entry")
					END IF
				ELSE
				REM Content of the field should be masked for input in SQL
					stContent = String_to_SQL(stContent)
					IF stContentField2 <> "" THEN
						stContentField2 = String_to_SQL(stContentField2)
					END IF
					REM The foreignkey of the combobox would be completely ignored. 
					REM Changing of the content should not change the content in other fields connected to the foreignkey.
					REM Example: '48431 Rheine' has ID 14. Value would be changed to '48429 Rheine'. If ID 14 would change the postcode to 48429
					REM all rows would be changed from '48431' to '48429'. Former correct addresses would be wrong, because there are more than one postcode for 'Rheine'.
					inID1 = -1	'Set ID to a value no ID would get, because standard for integer variables is 0 and could also be value for ID	
					inID2 = -1	'inID2 isn't known in the dotasource of the form.
					REM Connect to datasource
					oDatasource = ThisComponent.Parent.CurrentController
					If NOT (oDatasource.isConnected()) Then
						oDatasource.connect()
					End If
					oConnection = oDatasource.ActiveConnection()
					oSQL_Command = oConnection.createStatement()
					IF NameTableField2 <> "" AND NOT IsEmpty(stContentField2) AND NameTable2 <> "" THEN
						REM If a second field exists the second rlation must be cleared first. First: Does exist the entry of the field?
						stSql = "SELECT ""ID"" FROM """+NameTable2+""" WHERE """+NameTableField2+"""='"+stContentField2+"'"
						oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
						WHILE oResult.next
							inID2 = oResult.getInt(1)'First field of the row
						WEND 'go on, if there are more fields
						REM If inID2 > -1 the query got an existing content. Otherwise new value must be inserted.
						IF inID2 = -1 THEN
							stSql = "INSERT INTO """+NameTable2+""" ("""+NameTableField2+""") VALUES ('"+stContentField2+"') "
							oSQL_Command.executeUpdate(stSql)
							REM And read out the new ID	
							stSql = "CALL IDENTITY()"
							oResult = oSQL_Command.executeQuery(stSql)	'Save result in an object
							WHILE oResult.next
								inID2 = oResult.getInt(1)	'First field of the row
							WEND 'go on, if there are more fields
						END IF
						REM Test, if combination of inID2 and stContent exists in first table. 
						REM If a combobox is empty and a new value is inserted oField is empty before.
						REM Whithout this query the existing entries of the combobox would be duplicated while inserting new values.
						stSql = "SELECT ""ID"" FROM """+NameTable1+""" WHERE """+NameTab12ID+"""='"+inID2+"' AND """+NameTableField1+""" = '"+stContent+"'"
						oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
						WHILE oResult.next
							inID1 = oResult.getInt(1)'First field of the row
						WEND 'go on, if there are more fields
						REM If there isn't any primarykeythe combination must be saved and the new key must read out.
						REM The foreignkey of the combobox would be completely ignored.
						REM Changing of the content should not change the content in other fields connected to the foreignkey.
						IF inID1 = -1 THEN
							stSql = "INSERT INTO """+NameTable1+""" ("""+NameTableField1+""","""+NameTab12ID+""") VALUES ('"+stContent+"','"+inID2+"') "
							oSQL_Command.executeUpdate(stSql)	
							REM Und die entsprechende ID direkt wieder auslesen		
							stSql = "CALL IDENTITY()"
							oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
							WHILE oResult.next
								inID1 = oResult.getInt(1)	'First field of the row
							WEND 'go on, if there are more fields
						END IF
					END IF
					IF NameTableField2 <> "" AND NameTable2 = "" THEN			
						stSql = "SELECT ""ID"" FROM """+NameTable1+""" WHERE """+NameTableField1+"""='"+stContent+"' AND """+NameTableField2+"""='"+stContentField2+"'"
						oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
						WHILE oResult.next
							inID1 = oResult.getInt(1)	'First field of the row
						WEND 'go on, if there are more fields
						IF inID1 = -1 THEN
							REM ... if there exists no second table ...
							stSql = "INSERT INTO """+NameTable1+""" ("""+NameTableField1+""","""+NameTableField2+""") VALUES ('"+stContent+"','"+stContentField2+"') "
							oSQL_Command.executeUpdate(stSql)
							REM Read out the new ID	
							stSql = "CALL IDENTITY()"
							oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
							WHILE oResult.next
								inID1 = oResult.getInt(1)	'First field of the row
							WEND 'go on, if there are more fields
						END IF
					END IF
					IF NameTableField2 = "" THEN
						stSql = "SELECT ""ID"" FROM """+NameTable1+""" WHERE """+NameTableField1+"""='"+stContent+"'"
						oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
						WHILE oResult.next
							inID1 = oResult.getInt(1)	'First field of the row
						WEND 'go on, if there are more fields
						IF inID1 = -1 THEN
							REM ... if there isn't a second field
							stSql = "INSERT INTO """+NameTable1+""" ("""+NameTableField1+""") VALUES ('"+stContent+"') "
							oSQL_Command.executeUpdate(stSql)
							REM Read out the new ID		
							stSql = "CALL IDENTITY()"
							oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
							WHILE oResult.next
								inID1 = oResult.getInt(1)	'First field of the row
							WEND 'go on, if there are more fields
						END IF
					END IF
				  	REM ID-Value must be saved in the datasource of the form
				  	oForm.updateLong(oForm.findColumn(oFieldList.Tag),inID1)
				END IF
			ELSE
				oForm.updateNULL(oForm.findColumn(oFieldList.Tag),NULL)
			END IF		
		NEXT inCom
	END IF
END SUB