LibreOffice logo
BASEDOCUMENTER
The software tool for documenting your LibreOffice Base applications
 
Database file/home/jean-pierre/Documents/BaseDocumenter/www/databases/LODoc/Example_Combobox_Listfield.odb
File actual save date2018-06-26 17:10:26
Scanning done on2018-08-21 16:59:36
Documentation generated on2018-08-21 16:59:47
Table of contents
Example_Combobox_Listfield
Procedures by module
Library Module name Procedure name Language Used by Number of code lines Procedure code
Standard Comboboxes ColumnSize Basic TextSelectionSaveValue (Procedure) 22
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
Standard Comboboxes GenerateRecordAction Basic name|com_postcode (Control)
name|com_street (Control)
7
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
Standard Comboboxes ShowText Basic name (Form) 54
SUB ShowText(oEvent AS OBJECT)
REM Macro should be connected to 'Form' → 'After Rowchange'
REM Macro is only needed if the content of the form isn't defined by a query, which contains the content of the comboboxes also.
REM which contains the content of the comboboxes also.
DIM oForm AS OBJECT
DIM inCom AS INTEGER
DIM oFieldList AS OBJECT
DIM stFieldID AS STRING
DIM stQuery AS STRING
DIM stFieldValue AS STRING
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)
oFieldList = oForm.getByName(Trim(aComboboxen(inCom)))
stFieldID = oForm.getString(oForm.findColumn(oFieldList.Tag)) 'Value of foreignkey - string, because default of integer is '0'
oFieldList.Refresh() REM Refresh() for showing new values, which could be inserted directly before.
REM Value of ID-field
IF stFieldID <> "" THEN
REM Only if ID isn't empty there will be connected to database
stQuery = oFieldList.ListSource 'Query of the combobox
IF InStr(stQuery,"order by") > 0 THEN
stSql = Left(stQuery, InStr(stQuery,"order by")-1)
ELSE
stSql = stQuery
END IF
REM Create for the SQL-code, which should describe the text, which should be connected to the foreignkey
IF InStr(stSql,"where") THEN
st = Right(stSql, Len(stSql)-InStr(stSql,"where")-4)
IF InStr(Left(st, InStr(st,"=")),".""ID""") THEN
a() = Split(Right(st, Len(st)-InStr(st,"=")-1),".")
ELSE
a() = Split(Left(st, InStr(st,"=")-1),".")
END IF
stSql = stSql + "AND "+a(0)+".""ID"" = "+stFieldID
ELSE
stSql = stSql + "WHERE ""ID"" = "+stFieldID
END IF
oDatasource = ThisComponent.Parent.CurrentController
IF NOT (oDatasource.isConnected()) THEN
oDatasource.connect()
END IF
oConnection = oDatasource.ActiveConnection()
oSQL_Command = oConnection.createStatement()
oResult = oSQL_Command.executeQuery(stSql) 'Save result in an object
WHILE oResult.next
stFieldValue = oResult.getString(1) 'First field of the row
WEND 'go on, if there are more fields - no more in this special case
REM Set combobox to the text
oFieldList.Text = stFieldValue
ELSE
oFieldList.Text = ""
END IF
NEXT inCom
END SUB
Standard Comboboxes String_to_SQL Basic TextSelectionSaveValue (Procedure) 7
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
Standard Comboboxes TextSelectionSaveValue Basic name (Form)
name_correlated_subquery (Form)
name_tablecontrol (Form)
263
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