Spread form over 2 monitors

[Yumi]

I have two monitors of different size. My form is filling the larger one and I would like to extend it. Is it possible to have one form with two windows or two forms linked to access the same data?

[Zizi64]

My first tips:
- Use monitors with same pixel size
- Resize (rework) the form to the smaller monitor

Your signature is:

OOo 2.4.X on Ubuntu 8.x + WindowsXP

Please update yout signature in this forum.

[RoryOF]

Try reducing the OpenOffice window from full screen, then pulling the border to the other screen. You may need to adjust the height also.

[Yumi]

Yes, this was my initial approach. It works, I can extend the window as suggested. But this way lots of space is lost esp. as the two monitors are not quite on the same level.

[RoryOF]

You have to arrive at a suitable compromise that suits you. You can adjust as best suits your requirement. It may be time to acquire a second monitor of the same resolution as your existing highest resolution monitor.

[Yumi]

I found this very old article in the forum. It mentions storing an ID in a temporary table. But I can not figure out how the form gets the value from one table and puts it in the temp. table.

In my case I have a list box with all my articles SKU and Name for selection. The actual article information is displayed in subforms. My trade is quite complex and the screen is quite full of fields. But it works nicely. Just scrolling up and down the list box, selecting the articles and all the changes can be done.

Now I want to extend to display the purchase information for the exact article visible in form1. If the second form could know what I am looking at, displaying the info would be easy. The problem is how does form2 know the SKU selected in form1

[UnklDonald418]

I found this very old article in the forum. It mentions storing an ID in a temporary table. But I can not figure out how the form gets the value from one table and puts it in the temp. table.

I appears you are referring to a Filter table. A Filter table is a special type of table that always has just one row of data.
I uploaded a demonstration of how you might use a Filter table to display data on the form document Form2 based on a selection on the form document Form1.

In the demo open both Form1 and Form2.
On Form1 use the Listbox to select a Studio name.
For the next step there are 3 options, press the Enter key, or the Save push button or select the Save icon on the navigation bar, any one of them will save the ID of the selected studio in the F1 field of the table FILTER.
Still on Form1 select the Refresh button to display the address information of the selected studio.
On Form2 use the Refresh button to read the value stored in the F1 field of the FILTER table and display a list of cartoon characters associated with that studio.

Demo40_FilterTable.odb

(23.14 KiB) Downloaded 191 times

[Yumi]

Thank you. That Filter table intrigued me, but I have to go a different way then shown in Form1.

Form 1 uses a List field linked to the Filter Table. The data is extracted from another table through an sql command select . . . . . . This is ok for a small number of records, but in my case is becomes too unwieldy.

I have the Article ID in one field in the form. If I could use sql insert into table filter that would be the solution. But I can not get the syntax right or am completely on the wrong track with "insert".

All I need is to insert the value from the textbox ID into the table filter. How to do it?

The other question I have how does base keep only one record in the table filter and not add a new line every time a value is stored?

[Yumi]

I searched further and discovered the advice "Alternatively, you could permanently store the value that is supposed to be the subject of the filter in a separate table/row.". Unfortunately it did not say howto.

My guess is that I have to use a macro which is executed when the value of the text box changes.

[Villeroy]

Recipe for a working filtering form storing criteria in a distinct row of a filter table:
Create a table with one numeric primary key (no auto-value) and some integer, text and date fields plus a description field for documentation.

Code: Select all

CREATE TABLE "Filter" ("ID" TINYINT PRIMARY KEY, INT1 INT, INT2 INT, TXT1 VARCHAR(100), TXT2 VARCHAR(100), D1 DATE, D2 DATE,"DESCR" VARCHAR(100));
INSERT INTO "Filter" VALUES(0, NULL, NULL, NULL, NULL, NULL, NULL,'Criteria set for a form named "Foo".');

The second statement inserts a first criteria record with ID 0 and a descriptive text about the purpose of this criteria set.

Source of filtering main form: SELECT * FROM "Filter"WHERE "ID"=0 [select this particular row only]
Allow modification = yes, everything else = no.
You don't want a navigation bar for this form nor do you want to delete this dedicated record nor move into the insert row when you tab beyond the last control.
But you may want to use subsequent rows with ID=1 for completely different purposes which is because I never give talking names to criteria fields.

The depending form may be a subform of this one. It could also be a separate main form on a different form document, a stand-alone Writer document or even a form on a spreadsheet. If you put it on the same document, add a refresh button belonging to the subform. WHen you push the button, it moves the focus away from the filtering main form saving the main form before performing the refresh action on the filtered subform.

When using an independent form, the filter form should have a save button with property Default=yes so you can save the filter criteria by hitting the Enter key.

A simple method to link a filtered independent form with its stored criteria goes like this:

Code: Select all

SELECT "Data".* FROM "Data", "Filter" 
WHERE "Filter"."ID"=0 
  AND ("Filter"."D1"<="Data"."Date" OR "Filter"."D1" IS NULL)
  AND ("Filter"."D2">="Data"."Date" OR "Filter"."D2" IS NULL)
  AND ("Filter"."INT1"="Data"."XID" OR "Filter"."INT1" IS NULL)
  AND (UPPER("Data"."TextField") LIKE UPPER("Filter"."TXT1")||'%' OR "Filter".TXT1" IS NULL)

The above example selects all rows from a table "Data" and from single row 0 of the filter table where Data.Date is between 2 filter dates, Data.XID equals criterion INT1 and where Data.TextField starts with criterion TXT1.
All the criteria are optional. If you put a NULL value in a criteria field (leaving a form control empty) the ORed condition returns TRUE for any value in the data column.
-------------------------------------------------
This allows you to store optional and mandatory filter criteria, combine them with arbitrary functions and operators and use an ordinary refresh button to refresh an independent form.
The filtered data are read-only because we select from 2 tables. If this is a problem, if you need the filtered data in ediable form, a simple solution could be a subform which selects the parent form's record for editing. Just bind them through the common primary key, so the parent form selects a unique record in the editable subform.
--------------------------------------------
In case of a form/subform pair on the same form document, you get editable filter results with a parameterized row set from a single table:

Code: Select all

SELECT "Data".* FROM "Data"
  WHERE (:pD1<="Data"."Date" OR :pD1 IS NULL)
  AND (:pD2 >="Data"."Date" OR :pD2 IS NULL)
  AND (:pINT1="Data"."XID" OR :pINT1 IS NULL)
  AND (UPPER("Data"."TextField") LIKE UPPER(:pTXT1)||'%' OR :pTXT1 IS NULL)

In the subform properties you map parent form's column D1 to subform's parameter name pD1, D2 to pD2, INT1 to pINT1, TXT1 to pTXT1. The query selects filtered records from a single table "Data" substituting the named parameters with the mapped fields of the parent form. Records from a single table including its primary key are editable.
-----------------------------------------
All this works very well without a single line of macro code. If you have these queries and forms up and running, you may want some very simple macro to save this or that click on a button.

[UnklDonald418]

I have the Article ID in one field in the form. If I could use sql insert into table filter that would be the solution. But I can not get the syntax right or am completely on the wrong track with "insert".

The only time you INSERT into a filter table is during initialization just after creating the table.
In my demonstration the SQL command to change the value stored in FILTER is

Code: Select all

UPDATE "FILTER" SET "F1" = Some  ID Value

You would need to execute that command at Tools>SQL.

We know virtually nothing about your database tables and forms, but generally speaking, if you have more than will display on a single form document you are probably trying to do too much on that form document. If there are multiple actions being performed it is often better to split those actions into different form documents, i.e. one for customer entry/edit and a different form document for inventory item entry/edit and perhaps another for sales transaction entry/edit.

The preferred method of accessing information from a Base database is to use a linked MainForm/SubForm combination. A filter table can allow one form to share selected information with other forms and form documents similar to a linked MainForm/SubForm combination

In the event there is no way to simplify the form document(s) then one option would be to use an SQL command to update the filter table or another option would be to use pop-up Dialogs to display extra information. But dialogs or automating SQL commands both require coded macros. The filter table SQL approach would require retrieving the selected textbox ID from form1 and using that in an SQL statement. The Base module API does have 2 types of statement services that can be used in a macro but that API is quite opaque, often making macro coding difficult to write and debug.
Macro's are often form specific so even a slight change to a form or table can result in a macro that no longer works. For those reasons it is best to use the available Base tools and consider a macro as a last resort. If after that you are still interested pursuing a coded macro I would recommend this book
http://www.baseprogramming.com/
Be sure to look at the documents page. While the book is more than a decade old, the Base module hasn't changed appreciably during that time.

The other question I have how does base keep only one record in the table filter and not add a new line every time a value is stored?

On a Base form the Allow additions and Allow deletions properties are both set to No.
Also, note that "ID" the Primary Key field in FILTER is a Boolean type which also limits the number of rows.

[Villeroy]

When you open the table named "Filter" and append a new record with ID=0 and some descriptive text you do exactly the same as:

Code: Select all

INSERT INTO "Filter" VALUES(0, NULL, NULL, NULL, NULL, NULL, NULL,'Criteria set for a form named "Foo".');

When you open the table named "Filter", right-click the record with ID=0 and choose "delete record" you do exactly the same as

Code: Select all

DELETE FROM "Filter" WHERE "ID"=0;

When you open the table named "Filter", edit the record with ID=0 and you do exactly the same as:

Code: Select all

UPDATE "Filter" SET "DESCR" = 'some other text';

a record is saved when you hit some save button or move to another record.
It makes no difference wether you edit/delete/update the table in the table view or in a form which is linked to (a subset of) the table or if you execute an SQL command.

INSERT, DELETE and UPDATE belong to the "data manipulation language" (DML) of the structured query language (SQL). They affect the content of a table but not the structure.
https://en.wikipedia.org/wiki/Data_mani ... n_language
http://www.hsqldb.org/doc/1.8/guide/ch09.html