SUB DatabaseBackup(inMax AS INTEGER)
	REM A copy of the databasefile *.odb would be created in the Backup-folder. 
	REM The amount is limited to 5 copies. After it has reached 5 copies, the oldest copy would be replaced by the new copy.
	REM This procedure doesn't solve the problem, that input of data in the opened HSQLDB-database couldn't be solved.
	REM Data of the internal HSQLDB were written in the *.odb-file, when the file will be closed.
	REM The procedure "WriteDataFromCache" helps to write the data into the opened *.odb-file.
	DIM oPath AS OBJECT
	DIM oDoc AS OBJECT
	DIM sTitel AS STRING
	DIM sUrl_Target AS STRING
	DIM sUrl_Start AS STRING
	DIM i AS INTEGER
	DIM k AS INTEGER
	oDoc = ThisComponent
	sTitel = oDoc.Title	'Name of the document, for example Media_with_Macros.odb
	sUrl_Start = oDoc.URL	'Path to the title
	oPath = createUnoService("com.sun.star.util.PathSettings")
	FOR i = 1 TO inMax + 1
		IF NOT FileExists(oPath.Backup & "/" & i & "_" & sTitel) THEN
		REM Saved in the backup-path of the database-user of LO, beginning with the number, followed by a underscore and the title of the database.
			IF i > inMax THEN	
			REM If i bigger than 5, 5 backupcopies do exist. Now we have to look, which copy is the oldest.
				FOR k = inMax -1 TO 1 STEP -1
					IF FileDateTime(oPath.Backup & "/" & k & "_" & sTitel) <=  FileDateTime(oPath.Backup & "/" & k+1 & "_" & sTitel) THEN
					REM File with number 4 must be created before number 5. If this is right, searchung is going on.
					REM Searching begins with 4, counting downward. The number is matched with the number raised by one.
					REM If 1 is reached and created before 2, 1 would be replaced by the new copy.
						IF k = 1 THEN
							i = k
							EXIT FOR
						END IF
					ELSE
					REM If a number of a file is found, where the file is created later as the file with the number raised by one, the file with the number raised by one would be replaced.
						i = k+1
						EXIT FOR
					END IF
				NEXT
			END IF
			EXIT FOR
		END IF
	NEXT
	sUrl_Target = oPath.Backup & "/" & i &"_" & sTitel
	REM The path for the targetfile is created and the document will be copied to this targetfile.
	FileCopy(sUrl_Start,sUrl_Target)
END SUB

FUNCTION ColumnSize(Tablename AS STRING, Fieldname AS STRING) AS INTEGER
	REM There could be the content of 2 fields shown in the comboboxes by '||'. GUI doesn't know the maximal length of this fields.
	REM The systemtable INFORMATION_SCHEMA.SYSTEM_COLUMNS is used to get the size of the columns.
	REM Connect to datasource	
	DIM oDatasource AS OBJECT
	DIM oConnection AS OBJECT
	DIM oResult AS OBJECT
	DIM oSQL_Command AS OBJECT
	DIM i AS INTEGER
	oDatasource = ThisComponent.Parent.CurrentController
	If NOT (oDatasource.isConnected()) Then
		oDatasource.connect()
	End If
	oConnection = oDatasource.ActiveConnection()
	oSQL_Command = oConnection.createStatement()
	stSql="SELECT ""COLUMN_SIZE"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_COLUMNS"" WHERE ""TABLE_NAME"" = '"+Tablename+"' AND ""COLUMN_NAME"" = '"+Fieldname+"'"
	oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
	WHILE oResult.next
		i = oResult.getInt(1)	'First field of the row
	WEND 'go on, if there are more fields
	ColumnSize = i
END FUNCTION

SUB GenerateRecordAction(oEvent AS OBJECT)
	REM Macro is only needed if datasource of the form isn't a query.
	REM The form doesn't recognize any changing in fields, which aren't connected to the datasource.
	DIM oForm AS OBJECT
	oForm = oEvent.Source.Model.Parent	'Form will be determined by the event.
	oForm.isModified = TRUE	'Changing of content will be recognized.
END SUB

REM " ' " must be masked for SQL-Code sometimes. For dBase it's essential.
FUNCTION String_to_SQL(st AS STRING)
	IF InStr(st,"'") THEN
		st = Join(Split(st,"'"),"''")
	END IF
	String_to_SQL = st
END FUNCTION

SUB DatabaseCompressing
	REM If changing much content of the database, the database would expand a lot. This procedure will conpress the database.
	REM With LO 3.6 the compression is running, when closing the database. This procedure isn't necessary
	DIM stMessage AS STRING
	oDataSource = ThisComponent.Parent.CurrentController	' Access from form
	IF NOT (oDataSource.isConnected()) THEN
		oDataSource.connect()
	END IF
	oConnection = oDataSource.ActiveConnection()
	oSQL_Command = oConnection.createStatement()
	stSql = "SHUTDOWN COMPACT"		' Database would be zipped and closed
	oSQL_Command.executeQuery(stSql)
	stMessage = "Database has been compressed." + CHR(13) + "The form will be closed."
	stMessage = stMessage + CHR(13) + "After closing the form you should close the whole database."
	stMessage = stMessage + CHR(13) + "You could work with the database again, when reopening the database."
	msgbox stMessage	
	ThisDatabaseDocument.FormDocuments.getByName( "Maintenance" ).close
	REM Closing of the database document is possible with the following command. But if you start LO new, it will start with recovery for the database file.
'	ThisDatabaseDocument.close(True)	
END SUB

SUB DialogTableAdjustmentLB1selected
	STATIC stContent AS STRING	'Definition as STATIC keeps the value of the variable
	STATIC inPos AS Integer
	DIM oCtlList1 AS OBJECT
	DIM oCtlList2 AS OBJECT
	DIM oCtlText AS OBJECT
	DIM oCtlCheck1 AS OBJECT
	rem If new, the content should be written to ListBox2.
	IF Len(stContent) >0 THEN
		oCtlList2 = oDlg.GetControl("ListBox2")
		oCtlList2.addItem(stContent,inPos)
	END IF
	rem Read chosen content for ListBox2
	oCtlList1 = oDlg.GetControl("ListBox1")
	inPos = oCtlList1.selectedItemPos
	stContent = oCtlList1.getSelectedItem()	'necessary for write back content, when chosen from ListBox1 as new.
	rem Enable fields in the dialog
	oCtlText = oDlg.GetControl("TextField1")
	oCtlText.Model.Enabled=True
	oCtlList2 = oDlg.GetControl("ListBox2")
	oCtlList2.removeItems(inPos,1)	'remove content from ListBox2
	oCtlList2.Model.Enabled=True
	oCtlCheck1 = oDlg.GetControl("CheckBox1")
	oCtlCheck1.Model.Enabled=True
END SUB

SUB DialogTableAdjustmentTextSelected
	DIM oCtlList2 AS OBJECT
	DIM oCtlCheck1 AS OBJECT
	REM Disable other fields
	oCtlList2 = oDlg.GetControl("ListBox2")
	oCtlList2.Model.Enabled=False
	oCtlCheck1 = oDlg.GetControl("CheckBox1")
	oCtlCheck1.Model.Enabled=False
END SUB

SUB TableAdjustment(oEvent AS OBJECT)
	REM Adjust of tables, which aren't main tables in the form. Orthographic mistakes and redundant content could be cleared.
	REM 0: form, 1: subform, 2: subsubform, 3:combo box or field of tablecontrol, 4: foreignkey in form, empty when there is a tablecontrol field,
	REM 5: tablename second table, 6: field1 second table, 7: field2 second table, if any 8: tablename second table for field2
	DIM oCtlList1 AS OBJECT
	DIM oCtlList2 AS OBJECT
	DIM oCtlText AS OBJECT
	DIM oCtlCheck1 AS OBJECT
	DIM oCtlCheck2 AS OBJECT
	DIM oCtlLabel1 AS OBJECT
	DIM oFieldList AS OBJECT
	DIM oFieldID AS OBJECT
	DIM aContent()
	DIM aColumns()
	DIM aForeignTables(0, 0 to 1)
	DIM aForeignTables2(0, 0 to 1)
	DIM stTag AS STRING
	DIM stFieldList AS STRING
	DIM stCondition AS STRING
	DIM stText AS STRING
	DIM stContent1 AS STRING
	DIM stContent2 AS STRING
	DIM stFieldHeader AS STRING
	DIM stSideTable AS STRING
	DIM stForeignIDTab1Tab2 AS STRING
	DIM stForeignIDTab2Tab1 AS STRING
	DIM inRepeat AS INTEGER
	DIM inLB1 AS INTEGER
	DIM inLB2 AS INTEGER
	DIM inCounter AS INTEGER
	DIM i AS INTEGER
	DIM inK AS INTEGER
	DIM inID AS INTEGER
	DIM inUnique AS INTEGER
	DIM inRow AS INTEGER
	stTag = oEvent.Source.Model.Tag 'Input of the tag in "Additional information" of the control
	aTable() = Split(stTag, ", ")	'Ordered see above, max. 9 entries. Must be clarified, if enty 8 and entry 9 does exist.
	FOR i = LBound(aTable()) TO UBound(aTable())
		aTable(i) = trim(aTable(i))
	NEXT
	REM Clearing relations of the base-table to other tables
	REM All relations will be cleared by the system tables.
	REM Foreignkeys will be saved in this multidimensional array.
	oDataSource = ThisComponent.Parent.CurrentController
	If NOT (oDataSource.isConnected()) THEN
		oDataSource.connect()
	END IF
	oConnection = oDataSource.ActiveConnection()
	oSQL_Command = oConnection.createStatement()
	stSql = "SELECT ""FKTABLE_NAME"", ""FKCOLUMN_NAME"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_CROSSREFERENCE"" WHERE ""PKTABLE_NAME"" = '" + aTable(5) + "'"
	inCounter = 0	'Counter for array, where values of the other table would be saved.
	stForeignIDTab1Tab2 = "ID"	'Standard primarykey is called "ID"
	stForeignIDTab2Tab1 = "ID"
	stSideTable = aTable(5)	'Standard side table, which is connected to the main table. Example: Postcode and town - table postcode.
	oResult = oSQL_Command.executeQuery(stSql)
	WHILE oResult.next
		ReDim Preserve aForeignTables(inCounter,0 to 1)	'Dimensioning of array, former content must be saved (Preserve)
		aForeignTables(inCounter,0) = oResult.getString(1)	'Read the first field with name of the tablename containing the foreignkey.
		aForeignTables(inCounter,1) = oResult.getString(2)	'Read the second field with name of the foreignkey.
		IF UBound(aTable()) = 8 THEN
			IF aTable(8) = aForeignTables(inCounter,0) THEN
				stForeignIDTab2Tab1 = aForeignTables(inCounter,1)
				stSideTable = aTable(8)
			END IF
		END IF
		inCounter = inCounter + 1	'Extension of counter to new dimension
	WEND
	IF UBound(aTable()) = 8 THEN
		stSql = "SELECT ""FKTABLE_NAME"", ""FKCOLUMN_NAME"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_CROSSREFERENCE"" WHERE ""PKTABLE_NAME"" = '" + aTable(8) + "'"				
		oResult = oSQL_Command.executeQuery(stSql)
		inCounter = 0	'Counter for array, where values of the table 2 would be saved.
		WHILE oResult.next
			ReDim Preserve aForeignTables2(inCounter,0 to 1)	'Dimensioning of array, former content must be saved (Preserve)
			aForeignTables2(inCounter,0) = oResult.getString(1)	'Read the first field
			aForeignTables2(inCounter,1) = oResult.getString(2)	'Read the second field
			IF aTable(5) = aForeignTables2(inCounter,0) THEN
				stForeignIDTab1Tab2 = aForeignTables2(inCounter,1)
			END IF
			inCounter = inCounter + 1	'Extension of counter to new dimension
		WEND
	END IF
	oDoc = thisComponent
	oDrawpage = oDoc.drawpage
	oForm = oDrawpage.forms.getByName(aTable(0))
	inRow = -1	'Set row to an impossible value - form would not be reloaded
	IF aTable(1) <> "" THEN
		oSubForm = oForm.getByName(aTable(1))		
		IF aTable(2) <> "" THEN			
			oSubSubForm = oSubForm.getByName(aTable(2))	
			IF aTable(4) <> "" THEN
				oFieldList = oSubSubForm.getByName(aTable(3))
				oFieldID = oSubSubForm.getByName(aTable(4))
				inRow = oSubSubForm.Row
			END IF	
		ELSE
			IF aTable(4) <> "" THEN
				oFieldList = oSubForm.getByName(aTable(3))
				oFieldID = oSubForm.getByName(aTable(4))
				inRow = oSubForm.Row
			END IF
		END IF
	ELSE
		IF aTable(4) <> "" THEN
			oFieldList = oForm.getByName(aTable(3))
			oFieldID = oForm.getByName(aTable(4))
			inRow = oForm.Row
		END IF
	END IF
	inID = -1	'ID is set to a value, which isn't used by any ID with auto-value. Otherwise empty integer fields will get value 0.
	IF aTable(4) <> "" THEN
		IF oFieldID.getCurrentValue() THEN	'If field is empty, nothing will be assigned.
			inID = oFieldID.getCurrentValue()
		END IF
	END IF
	DO	'Loop, for the dialog must exist up to unchecked the check box.
		REM Read all fields of the second table. At the same time have a look, if 2 fields must be shown.
		IF UBound(aTable()) = 6 THEN
			stSql = "SELECT """ + aTable(6) + """ FROM """ + aTable(5) + """ ORDER BY """ + aTable(6) + """"
		ELSEIF UBound(aTable()) = 7 THEN
			stSql = "SELECT """ + aTable(6) + """||' > '||""" + aTable(7) + """ FROM """ + aTable(5) + """ ORDER BY """ + aTable(6) + """"
		ELSE			
			stSql = "SELECT """ + aTable(5) + """.""" + aTable(6) + """||' > '||""" + aTable(8) + """.""" + aTable(7) + """ FROM """ + aTable(5) + """, """ + aTable(8) + """"
			stSql = stSql + " WHERE """ + aTable(8) + """.""" + stForeignIDTab2Tab1 + """ = """ + aTable(5) + """.""" + stForeignIDTab1Tab2 + """ ORDER BY """ + aTable(6) + """"
		END IF
		oResult = oSQL_Command.executeQuery(stSql)
		inCounter = 0	'Counter for the array, where values of the second table would be saved.
		WHILE oResult.next
		    ReDim Preserve aContent(inCounter)	'Dimensioning of array, former content must be saved (Preserve)
		    aContent(inCounter) = oResult.getString(1)	'Read the first field
		    inCounter = inCounter+1	'Extension of counter to new dimension
		WEND
		REM Connect to the fist list box
		DialogLibraries.LoadLibrary("Standard")
		oDlg = CreateUnoDialog(DialogLibraries.Standard.Dialog_TableAdjustment)
		oCtlList1 = oDlg.GetControl("ListBox1")
		oCtlList1.addItems(aContent(),0)	'Read values of the array to the list
		oCtlList2 = oDlg.GetControl("ListBox2")
		oCtlList2.addItems(aContent(),0)	'Read values of the array to the list
		oCtlLabel1 = oDlg.GetControl("Label1")
		oCtlLabel1.setText(aTable(5))	'Write the header for the dialog
		oCtlCheck1	= oDlg.GetControl("CheckBox1") 'Connect to check box 1 for deleting all redundant entries
		oCtlText = oDlg.GetControl("TextField1")	'Connect to text field for changing the value
		IF inRepeat = 1 THEN
			oCtlCheck2 = oDlg.GetControl("CheckBox2")
			oCtlCheck2.SetState(1)
		END IF
		REM Dialog starten
		Select Case oDlg.Execute()
		Case 1	'Case 1, if button "OK" is pressed
			stContent1 = oCtlList1.getSelectedItem() 'Reda values of the list box ...
			REM ... and connect to the corresponding ID-value.
			IF UBound(aTable()) = 6 THEN
				stSql = "SELECT ""ID"" FROM """ + aTable(5) + """ WHERE """ + aTable(6) + """='" + stContent1 + "'"
			ELSEIF  UBound(aTable()) = 7 THEN
				stSql = "SELECT ""ID"" FROM """ + aTable(5) + """ WHERE """ + aTable(6) + """||' > '||""" + aTable(7) + """='" + stContent1 + "'"
			ELSE			
				stSql = "SELECT """ + stSideTable + """.""ID"" FROM """ + aTable(5) + """, """ + aTable(8) + """"
				stSql = stSql + " WHERE """ + aTable(8) + """.""" + stForeignIDTab2Tab1 + """ = """ + aTable(5) + """.""" + stForeignIDTab1Tab2 + """"
				stSql = stSql + " AND """ + aTable(5) + """.""" + aTable(6) + """||' > '||""" + aTable(8) + """.""" + aTable(7) + """='" + stContent1 + "'"
			END IF
			oResult = oSQL_Command.executeQuery(stSql)
			WHILE oResult.next
				inLB1 = oResult.getInt(1)
			WEND
			stText = oCtlText.Text	' Read the vaule of the field.
			REM If text field isn't empty, only the entry in this field will be worked out. Neither thr list field (other value) nor the check box (delete redundant) will be regarded.
			REM Its sown in the dialog by disabling these controls, when there is an input in the text field.
			IF stText <> "" THEN	' Isn't the text field empty?
				REM Replace the old value with the new value at the readed ID-field
				REM 2 entries must be possible, as they are possible for list boxes. Separator is '>'
				aText() = Sting_to_SQL(Split(stText, ">"))	
				IF UBound(aTable()) = 6 OR UBound(aText()) = 0  THEN
					stSql = "UPDATE """ + aTable(5) + """ SET """ + aTable(6) + """='" + trim(stText) + "' WHERE ""ID""='" + inLB1 + "'"
				ELSEIF  UBound(aTable()) = 7 THEN
					stSql = "UPDATE """ + aTable(5) + """ SET """ + aTable(6) + """='" + trim(aText(0)) + "', """ + aTable(7) + """='" + trim(aText(1)) + "' WHERE ""ID""='" + inLB1 + "'"
				ELSE					
					stSql = "UPDATE """ + aTable(5) + """ SET """ + aTable(6) + """='" + trim(aText(0)) + "' WHERE "
					stSql = stSql + " ""ID""= (SELECT """ + stForeignIDTab2Tab1 + """ FROM """ + stSideTable + """ WHERE ""ID"" = '" + inLB1 + "');"
					REM Two updates at the same time, separated by ';'
					stSql = stSql + "UPDATE """ + aTable(8) + """ SET """ + aTable(7) + """='" + trim(aText(1)) + "' WHERE "
					stSql = stSql + " ""ID""= (SELECT """ + stForeignIDTab1Tab2 + """ FROM """ + stSideTable + """ WHERE ""ID"" = '" + inLB1 + "')"
				END IF
				oSQL_Command.executeQuery(stSql)
			ELSEIF oCtlList2.getSelectedItem() <> "" THEN	'If text field is empty and Listbox2 isn't ...
				stContent2 = oCtlList2.getSelectedItem()	' ... read the value of the list box.
				REM Look for the ID connected to the value of the list box.
				IF UBound(aTable()) = 6 THEN
					stSql = "SELECT ""ID"" FROM """ + aTable(5) + """ WHERE """ + aTable(6) + """='" + stContent2 + "'"
				ELSEIF  UBound(aTable()) = 7 THEN
					stSql = "SELECT ""ID"" FROM """ + aTable(5) + """ WHERE """ + aTable(6) + """||' > '||""" + aTable(7) + """='" + stContent2 + "'"
				ELSE			
					stSql = "SELECT """ + stSideTable + """.""ID"" FROM """ + aTable(5) + """, """ + aTable(8) + """"
					stSql = stSql + " WHERE """ + aTable(8) + """.""" + stForeignIDTab2Tab1 + """ = """ + aTable(5) + """.""" + stForeignIDTab1Tab2 + """"
					stSql = stSql + " AND """ + aTable(5) + """.""" + aTable(6) + """||' > '||""" + aTable(8) + """.""" + aTable(7) + """='" + stContent2 + "'"				
				END IF
				oResult = oSQL_Command.executeQuery(stSql)
				WHILE oResult.next
					inLB2 = oResult.getInt(1)
				WEND
				IF stSideTable = aTable(5) THEN
					FOR i = LBound(aForeignTables()) TO UBound(aForeignTables())
						REM Replace the old ID-value by the new ID-value.
						REM Problematic with n:m-relationships, because the same value for ID could just exist.
						REM This must be avoided, if foreignkey of the table is part of a primary key in the other table.
						REM Example: Table "rel_Media_Author" couldn't connect a medium for the same author two times.
						REM Primary key of "rel_Media_Author" are Media_ID and Author_ID together.
						REM With this query all key fields with property UNIQUE will be examined.
						stSql = "SELECT ""COLUMN_NAME"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_INDEXINFO"" WHERE ""TABLE_NAME"" = '" + aForeignTables(i,0) + "' AND "
						stSql = stSql + """NON_UNIQUE"" = False AND ""INDEX_NAME"" = (SELECT ""INDEX_NAME"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_INDEXINFO""" 
						stSql = stSql + "WHERE ""TABLE_NAME"" = '" + aForeignTables(i,0) + "' AND ""COLUMN_NAME"" = '" + aForeignTables(i,1) + "')"	
						oResult = oSQL_Command.executeQuery(stSql)	
						REM If the field name of the foreign key in primary key or index with property UNIQUE in the second table, the name of the other keys should be evaluated.
						REM Isn't there another field, no update could be executed.
						stFieldHeader = ""
						inUnique = 0
						inCounter = 0
						WHILE oResult.next
							stFieldHeader = oResult.getString(1)
							IF aForeignTables(i,1) = stFieldHeader THEN
								inUnique = 1
							ELSE 
								ReDim Preserve aColumns(inCounter)	'Dimensioning of array, former content must be saved (Preserve)
							    aColumns(inCounter) = oResult.getString(1)	'Read the first field
							    inCounter = inCounter + 1	'Extension of counter to new dimension
							END IF
						WEND
						IF inUnique = 1 THEN
							stSql = "UPDATE """ + aForeignTables(i,0) + """ AS ""a"" SET """ + aForeignTables(i,1) + """='" + inLB2 + "' WHERE """ + aForeignTables(i,1) + """='" + inLB1 + "' AND "
							stSql = stSql + "( SELECT COUNT(*) FROM """ + aForeignTables(i,0) + """ WHERE """ + aForeignTables(i,1) + """='" + inLB2 + "' )"
							IF inCounter > 0 THEN
								stFieldgruppe = Join(aColumns(), """||  ||""")	'Connect more fields with a space for a SQL-group, if there is more than one primary key.
								stFieldHeader = ""
								FOR ink = LBound(aColumns()) TO UBound(aColumns())	
									REM A new counter for lower limit and upper limit must be created.,
									REM because this loop is inside the loop with counter i.
									stFieldHeader = stFieldHeader +  "AND """ + aColumns(ink) + """ = ""a"".""" + aColumns(ink) + """ "	'Connect for a correlating subquery
								NEXT
								stSql = Left(stSql, Len(stSql) - 1)	' Delete the right bracket
								stSql = stSql + stFieldHeader + "GROUP BY (""" + stFieldgruppe + """) ) < 1"	
							END IF
						ELSE
							REM If the field name of the foreign key isnt connected with the primary key the content of the foreign key could be doubled withot any problem.
							stSql = "UPDATE """ + aForeignTables(i,0) + """ SET """ + aForeignTables(i,1) + """='" + inLB2 + "' WHERE """ + aForeignTables(i,1) + """='" + inLB1 + "'"
						END IF		
						oSQL_Command.executeQuery(stSql)
					NEXT
					rem The old value will be deleted from the list box, because it isn't connected to any table with it's foreign key.				
					stSql = "DELETE FROM """ + aTable(5) + """ WHERE ""ID""='" + inLB1 + "'"
					oSQL_Command.executeQuery(stSql)
				END IF
				IF  UBound(aTable()) = 8 THEN
					IF stSideTable = aTable(8) THEN
						FOR i = LBound(aForeignTables2()) TO UBound(aForeignTables2())
							REM Replace the old ID-value by the new ID-value.
							REM Problematic with n:m-relationships, because the same value for ID could just exist.
							REM This must be avoided, if foreignkey of the table is part of a primary key in the other table.
							REM Example: Table "rel_Media_Author" couldn't connect a medium for the same author two times.
							REM Primary key of "rel_Media_Author" are Media_ID and Author_ID together.
							REM With this query all key fields with property UNIQUE will be examined.
							stSql = "SELECT ""COLUMN_NAME"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_INDEXINFO"" WHERE ""TABLE_NAME"" = '" + aForeignTables2(i,0) + "' AND "
							stSql = stSql + """NON_UNIQUE"" = False AND ""INDEX_NAME"" = (SELECT ""INDEX_NAME"" FROM ""INFORMATION_SCHEMA"".""SYSTEM_INDEXINFO""" 
							stSql = stSql + "WHERE ""TABLE_NAME"" = '" + aForeignTables2(i,0) + "' AND ""COLUMN_NAME"" = '" + aForeignTables2(i,1) + "')"		
							oResult = oSQL_Command.executeQuery(stSql)
							REM Must the field be UNIQUE or UNIQUE together with other fields?
							stFieldHeader = ""
							inUnique = 0
							inCounter = 0
							WHILE oResult.next
								stFieldHeader = oResult.getString(1)
								IF aForeignTables2(i,1) = stFieldHeader THEN
									inUnique = 1
								ELSE 
									ReDim Preserve aColumns(inCounter)	'Dimensioning of array, former content must be saved (Preserve)
								    aColumns(inCounter) = oResult.getString(1)	'Read the first field
								    inCounter = inCounter + 1	'Extension of counter to new dimension
								END IF
							WEND
							IF inUnique = 1	THEN
								REM If the field name of the foreign key in primary key or index with property UNIQUE in the second table, the name of the other keys should be evaluated.
								REM Isn't there another field, no update could be executed.	
								stSql = "UPDATE """ + aForeignTables2(i,0) + """ AS ""a"" SET """ + aForeignTables2(i,1) + """='" + inLB2 + "' WHERE """ + aForeignTables2(i,1) + """='" + inLB1 + "' AND "
								stSql = stSql + "( SELECT COUNT(*) FROM """ + aForeignTables2(i,0) + """ WHERE """ + aForeignTables2(i,1) + """='" + inLB2 + "'"
								IF inCounter > 0 THEN
									stFieldgruppe = Join(aColumns(), """||  ||""")	'Connect more fields with a space for a SQL-group, if there is more than one primary key.
									stFieldHeader = ""
									FOR ink = LBound(aColumns()) TO UBound(aColumns())	
										REM A new counter for lower limit and upper limit must be created.,
										REM because this loop is inside the loop with counter i.
										stFieldHeader = stFieldHeader + " AND """ + aColumns(ink) + """ = ""a"".""" + aColumns(ink) + """ "	'Connect for a correlating subquery
									NEXT
									stSql = Left(stSql, Len(stSql) - 1)	' Delete the right bracket
									stSql = stSql + stFieldHeader + "GROUP BY (""" + stFieldgruppe + """) ) < 1"	
								END IF
							ELSE
								REM If the field name of the foreign key isnt connected with the primary key the content of the foreign key could be doubled withot any problem.
								stSql = "UPDATE """ + aForeignTables2(i,0) + """ SET """ + aForeignTables2(i,1) + """='" + inLB2 + "' WHERE """ + aForeignTables2(i,1) + """='" + inLB1 + "'"
							END IF			
							oSQL_Command.executeQuery(stSql)
						NEXT
						rem The old value will be deleted from the list box, because it isn't connected to any table with it's foreign key.		
						stSql = "DELETE FROM """ + aTable(8) + """ WHERE ""ID""='" + inLB1 + "'"
						oSQL_Command.executeQuery(stSql)
					END IF
				END IF
				IF inID = inLB1 THEN
					inID = inLB2
					REM Content of the field could be indifferent, therefore it has been changed by the former updates.
				END IF
				REM The table must be reloaded, because there has been changed more than one value.
				REM The row counter will be changed to another value - not the default -1.
				inRow = 1
			ELSEIF oCtlCheck1.State = 1 THEN	'If text field and Listbox2 are empty and all redundant values should be deleted
				stCondition = ""
				IF stSideTable = aTable(5) THEN
					FOR i = LBound(aForeignTables()) TO UBound(aForeignTables())
						stCondition = stCondition + """ID"" NOT IN (SELECT """ + aForeignTables(i,1) + """ FROM """ + aForeignTables(i,0) + """) AND "
					NEXT
				ELSE
					FOR i = LBound(aForeignTables2()) TO UBound(aForeignTables2())
						stCondition = stCondition + """ID"" NOT IN (SELECT """ + aForeignTables2(i,1) + """ FROM """ + aForeignTables2(i,0) + """) AND "
					NEXT
				END IF
				stCondition = Left(stCondition, Len(stCondition) - 4)	' Last "AND" has to be cut.
				stSql = "DELETE FROM """ + stSideTable + """ WHERE " + stCondition + ""
				oSQL_Command.executeQuery(stSql)
				TableIndexDown(stSideTable)
			ELSE
				msgbox "Nothing to do - nothing done!"
			END IF
			oCtlCheck2 = oDlg.GetControl("CheckBox2") 'The dialog will restart
			IF oCtlCheck2.State = 1 THEN
				inRepeat = 1
			ELSE 
				inRepeat = 0
			END IF
		Case 0	'If button "Cancel" is pressed
			inRepeat = 0
		End Select
	LOOP WHILE inRepeat = 1  	
	rem Content of the list box in the main form should be refreshed
	IF aTable(3) <> "" THEN
  		oFieldList.refresh()
  	END IF
 	IF inID <> "" AND aTable(4) <>"" AND inRow <> -1 THEN
  		REM Looking for the content of the list box corresponding to the ID-value.	
 		IF UBound(aTable()) = 6 THEN
			stSql = "SELECT """ + aTable(6) + """ FROM """ + aTable(5) + """ WHERE ""ID""='" + inID + "'"
 		ELSEIF  UBound(aTable()) = 7 THEN
			stSql = "SELECT """ + aTable(6) + """||' > '||""" + aTable(7) + """ FROM """ + aTable(5) + """  WHERE ""ID""='" + inID + "'"
		ELSE	
 			stSql = "SELECT """ + aTable(5) + """.""" + aTable(6) + """||' > '||""" + aTable(8) + """.""" + aTable(7) + """"
 			stSql = stSql + " FROM """ + aTable(5) + """, """ + aTable(8) + """ WHERE """ + aTable(8) + """.""" + stForeignIDTab2Tab1 + """ = "
 			stSql = stSql + """" + aTable(5) + """.""" + stForeignIDTab1Tab2 + """ AND """ + stSideTable + """.""ID""='" + inID + "'"
		END IF
  		oResult = oSQL_Command.executeQuery(stSql)
		WHILE oResult.next
  			stFieldList = oResult.getString(1)
 		WEND				
	    REM Combo box will changed to the content, detected by the query
 		oDocCrl = ThisComponent.getCurrentController()
		oCtlView = oDocCrl.GetControl(oFieldList)
  		oCtlView.setText(stFieldList)
 	ELSE
		REM Table must be reloaded and set to the current row.
		REM If only the current field is set to the new value, all other fields with the same value will not be changed.
		REM The content of the old form is still cached.		
		IF aTable(2) <> "" THEN	
			inRow = oSubSubForm.Row
 			oSubSubForm.reload()
 			oSubSubForm.absolute(inRow)
 		ELSEIF aTable(1) <> "" THEN	
			inRow = oSubForm.Row
 			oSubForm.reload()
 			oSubForm.absolute(inRow)
 		ELSE	
			inRow = oForm.Row
			oForm.reload()
			oForm.absolute(inRow)
		END IF
	END IF
	oDlg.endExecute()	'	End the dialog ...
	oDlg.Dispose()	'	... and delete it from memory
END SUB

SUB MainformOpen
	ThisDatabaseDocument.FormDocuments.getByName( "Media" ).open
END SUB

SUB ToFormFromFormWithFolder(oEvent AS OBJECT)
	REM Form, that should be opened, ist the first
	REM Is the form in a separate folder, this folder is defined with "/".
	REM So the subfolder could be found
	DIM stTag AS STRING
	stTag = oEvent.Source.Model.Tag 'Input of the tag in "Additional information" of the control
	aForms() = Split(stTag, ",")	'First is written the name for the new form, followed by the old form
	aForms1() = Split(aForms(0),"/")
	aForms2() = Split(aForms(1),"/")
	IF UBound(aForms1()) = 0 THEN
		ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).open
	ELSE
		ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).getByName( Trim(aForms1(1)) ).open
	END IF
	IF UBound(aForms2()) = 0 THEN
		ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).close
	ELSE
		ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).getByName( Trim(aForms2(1)) ).close
	END IF
END SUB

SUB LoanRefresh
	DIM oDoc AS OBJECT
	DIM oDrawpage AS OBJECT
	DIM oForm AS OBJECT
	DIM oSubForm AS OBJECT
	DIM oSubSubForm AS OBJECT
	DIM oField AS OBJECT
	oDoc = thisComponent
	oDrawpage = oDoc.drawpage
	oForm = oDrawpage.forms.getByName("Filter")
	oSubForm = oForm.getByName("Choose_Reader")
	oSubSubForm = oSubForm.getByName("Show_Reader")
	oSubSubSubForm = oSubSubForm.getByName("Loan")
	oSubSubSubSubForm = oSubSubSubForm.getByName("Input")
	oField = oSubSubSubSubForm.getByName("Listfield 1")
	oField.commit()
	oSubSubSubSubForm.insertRow()	
	oField.refresh()
	oSubSubSubForm.reload()
END SUB