Lock a column in Base

Creating tables and queries
Post Reply
mickstanyard
Posts: 2
Joined: Wed Jun 26, 2013 5:52 pm

Lock a column in Base

Post by mickstanyard »

Hi,
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?

Mick
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Lock a column in Base

Post 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.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lock a column in Base

Post by Villeroy »

DummyPersons.odb
List of persons, unique by surname, forename and birth date.
(44.98 KiB) Downloaded 533 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mickstanyard
Posts: 2
Joined: Wed Jun 26, 2013 5:52 pm

Re: Lock a column in Base

Post 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.
OpenOffice 3.4.1 on Windows 7
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Lock a column in Base

Post by F3K Total »

Hi,
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.
R
Attachments
Grant_Role_Test.zip
(74.08 KiB) Downloaded 421 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lock a column in Base

Post 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Lock a column in Base

Post 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.
R
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
Ratslinger
Posts: 34
Joined: Sun Mar 01, 2015 3:34 am

Re: Lock a column in Base

Post 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.
Attachments
SimulatedLock.odb
Sample
(15.2 KiB) Downloaded 343 times
LibreOffice 7.0.2.2
OpenOffice 4.0.1
Ubuntu Mate 20.04.1
Mint 20.3
Post Reply