Register your database if it is not registered yet.
Tools>SQL...
Code: Select all
CREATE TABLE "Filter"(FID INT PRIMARY KEY, ID1 INT);
INSERT INTO "Filter" VALUES(1,Null);
Query qFilterRow1:
Code: Select all
SELECT * FROM "Filter" WHERE "FID"=1
Query qFiltered:
Code: Select all
SELECT "table".* FROM "table", "Filter" WHERE "Filter"."FID"=1 AND ("table"."ID"="Filter"."ID1" OR "Filter"."ID1" IS NULL)
Query qListBox:
Code: Select all
SELECT "some_name"AS "Visible Field", "ID" FROM "table" ORDER BY "Visible Field"
Add a list box to the spreadsheet.
Form Properties: Link the form to query qFilterRow1 of your registered database, disallow deletion, disallow insertion, hide navigation bar.
Control Properties: Link the list box to query qListBox, linked field is "ID1" of the underlying filter table, bound field is 1 (second field, 0 is the visible one).
Add an OK button with action "Save"
Get the data source window (F4 in OpenOffice, Ctrl+Shift+F4 in LibreOffice).
Browse to the queries of your registered database and drag the icon "qFiltered" into your sheet.
##############################################################################################
Usage: Select an item from the list box, OK, click any cell in the list and call menu:Data>Refresh. You may define a shortcut or toolbar button instead of the menu command, however the cell cursor needs to be within or adjacent to the import range.
###############################################################################################
For the refresh-by-macro solution:
Get the form navigator and add a dummy form with no binding.
Move the OK button to that form and remove its action property.
Assign the execute event to the one line macro ThisComponent.DatabaseRanges.getByName("Import1").refresh where "Import1" is the name of the wanted database range.
- Macro button on separate dummy form.
- Navi_OKButton.png (11.04 KiB) Viewed 14050 times