Page 1 of 1

Lock a column in Base

PostPosted: Wed Jun 26, 2013 6:11 pm
by mickstanyard
I am trying to lock the 'Name' column on my database so that it is always visible when I'm scrolling across the page but having no luck. Any ideas?


Re: Lock a column in Base

PostPosted: Thu Jun 27, 2013 12:20 pm
by Arineckaig
Welcome to the forum.

I do not think that facility, typical in spreadsheets, is available to Base when the latter simply displays a database table.

If absolutely necessary, a crude work-around is possible by creating a form document containing a main form and a sub-form. Both forms should be sourced from the same data table and each to contain a single grid/table form control. The grid/table in the main form displays the 'Name' field as a single column: the grid/table in the sub-form contains column(s) to display any of the other fields required from the source table.

The two data forms should be linked by the Primary Key field even though this field need not necessarily be displayed in either form. If the two grid/tables are formatted alongside side each other in the form document and are kept in sync, a comparable effect to that readily available in Calc, or most spreadsheets, can be achieved.

Re: Lock a column in Base

PostPosted: Thu Jun 27, 2013 12:52 pm
by Villeroy
List of persons, unique by surname, forename and birth date.
(44.98 KiB) Downloaded 324 times

Re: Lock a column in Base

PostPosted: Thu Jun 27, 2013 6:01 pm
by mickstanyard
Ok, cheers for help. I can do it on MS Access so assumed it could be done on Base, no worries maybe something for a future version.

Re: Lock a column in Base

PostPosted: Thu Jun 27, 2013 8:30 pm
by F3K Total
i think, i have a solution for that:
If you use a Split-Database - be sure to have adjusted the Java-Class-Path in Tools/Options/Java - it's possible to grant roles to users.
With these roles you can lock e.g. the first table "Persons" and keep a second table "Persons_Data" unlocked for a specific user.
Find attached an example, uncompress the folder, but keep the content together. Enable macros, to set the link to the external database-files automatically on opening the .odb-file.
Log in User: U1, Password 1111
Then you're able to change datas in "Persons_Data" but can't change datas in "Persons".
If you like to have both in one window, use a query like "qPersons".
To understand, how the role is granted to U1, have a look on file /database/GR.script using a simple editor.

Re: Lock a column in Base

PostPosted: Sat Jun 29, 2013 12:18 pm
by Villeroy
F3K Total wrote:Hi,
i think, i have a solution for that:

Well, yes and no. Your solution works well on my system where I can set my own hsqldb.jar per class path or per document configuration. The default configuration can not access your extracted database because it is too stupid to use the built-in hsqldb.jar with an external hsql database.

Anyhow, I think your example database solves another problem than the requested one which was about the feature known as "column freeze" (Window>Freeze) in Calc.

Re: Lock a column in Base

PostPosted: Sat Jun 29, 2013 3:57 pm
by F3K Total
Villeroy wrote: which was about the feature known as "column freeze" (Window>Freeze) in Calc.

OK, i totaly missunderstood the question. :knock: , but the tryout was a challenge for me.

Re: Lock a column in Base

PostPosted: Fri Jan 15, 2016 1:57 am
by Ratslinger
I know this is an old post but I have a potential solution using one or two small macros (depending upon key vs button) some might find useful.

The attached sample provides two similar solutions; one using buttons to scroll through fields and the second uses the F11 & F12 keys.

There are a couple of minor problems:
1) Field size can cause the 'locked' column to scroll left. Careful planning eliminates this.
You may also read the column 'Width' property of the field to be hidden and apply it to the column to be displayed.
This will keep the 'locked' filed from scrolling but resize columns in maybe an unwanted way.
2) Tabbing through the last displayed column proceeds to the next record and not the next field
and tabbing back goes to the 'locked' column.
I believe a key handler may resolve this but have not yet tried. May not work - Tab key ignored by handler!

Note: When using the key method, the table control must have focus.

The button method uses the 'Execute action' event and the key method uses the 'Key pressed' event on the table control.

When in design mode for the form, place all wanted fields (as columns) on the table control. Then after determining how many to display, hide all the rightmost fields. In the sample, 13 columns were on the control, five were to be displayed and the rightmost 8 were set to Hide.

The macro(s) are set to lock the leftmost column. This may be easily changed.