Add items to combo box in edit mode Openoffice Base

Question

I am trying to add a couple of options to a combobox in openoffice base. The combobox will only ever have two options that can be selected so it's rediculous to have a table for specifying the items in the list. How do I add the options to the combobox in edit mode? I know that in visual studio I can achieve this by simply creating a combobox control and opening the properties, scrolling to the "list content" property and adding typing in each item, separated by a line break. How can I do this in openoffice base? After scouring the internet, I cannot find the answer anywhere.

Answer 1

Edit the properties of a combo box. In the Data tab for Type of list contents, specify SQL [Native]. For List content, specify the following query:

SELECT 'No' FROM DUAL UNION SELECT 'Yes' FROM DUAL

That is if the engine is Oracle. For MySQL, only this is needed:

SELECT 'Yes' UNION SELECT 'No';

For the embedded HSQLDB engine, version 1.8 requires a special table to be created.[1] Create a table called MY_DUMMY with only one column and one row. This will behave like Oracle's DUAL table. Then specify the query like this:

SELECT 'No' FROM MY_DUMMY UNION SELECT 'Yes' FROM MY_DUMMY

[1] In newer versions of HSQLDB, it is possible to use (VALUES(0)) as a dummy table, but LO uses the older version by default.

[2] UNION combines two SELECT statements.

EDIT:

Here are step by step instructions using Apache OpenOffice 4.1.2. All tables and fields are upper case to make SQL easier.

1. In OpenOffice, go to File -> New -> Database.
2. Radio Create a new database.[3] Next.
3. No, do not register the database.
4. Finish. Save the file somewhere.
5. Create Table in Design View.
6. Field Name: ID. Field Type: Integer [ INTEGER ].
7. Right-click to the left of ID and specify Primary key.
8. Save as MY_DUMMY and then close the table.
9. Create Table in Design View.
10. Again create an ID field as primary key.
11. Also create a field called VALUE as type Text [ VARCHAR ].
12. Save as MY_TABLE1 and close.
13. Double-click on table MY_DUMMY and add one record with value 0.
14. Press enter to save the record, then close the table.
15. Double-click on table MY_TABLE1 and add two records with ID of 0 and 1.
16. Set the VALUE field to No for both records and close the table.
17. In the Forms area, Use Wizard to Create Form...
18. Tables or queries: Table: MY_TABLE1.
19. Click >> to move ID and VALUE to the right side.
20. Press Finish, then close the form.
21. Right-click on the MY_TABLE1 form and choose Edit.
22. With the Combo Box tool, drag a large square to create the combo box.
23. Press Cancel to close the wizard.
24. Right-click on the combo box and choose Control.
25. In the Data tab, for Data field, specify VALUE.
26. For Type of list contents, specify Sql [Native].
27. For List content, specify SELECT 'No' FROM MY_DUMMY UNION SELECT 'Yes' FROM MY_DUMMY.
28. Save and close the form.
29. Double-click to open the form.
30. In the combo box, click Yes.
31. At the top of the combo box, click to the right of Yes to move the cursor there.
32. Press Enter to accept the value of the combo box.

Now you should see the value in the table change to Yes.

[3] This option uses an embedded database, which is not recommended for production environments, only for testing and development. The engine is HSQLDB 1.8.