Lock a column in Base

Creating tables and queries

Lock a column in Base

Postby mickstanyard » Wed Jun 26, 2013 6:11 pm

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
mickstanyard
 
Posts: 2
Joined: Wed Jun 26, 2013 5:52 pm

Re: Lock a column in Base

Postby Arineckaig » Thu Jun 27, 2013 12:20 pm

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
Arineckaig
Volunteer
 
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Lock a column in Base

Postby Villeroy » Thu Jun 27, 2013 12:52 pm

DummyPersons.odb
List of persons, unique by surname, forename and birth date.
(44.98 KiB) Downloaded 324 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lock a column in Base

Postby mickstanyard » Thu Jun 27, 2013 6:01 pm

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
mickstanyard
 
Posts: 2
Joined: Wed Jun 26, 2013 5:52 pm

Re: Lock a column in Base

Postby F3K Total » Thu Jun 27, 2013 8:30 pm

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 221 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
F3K Total
Volunteer
 
Posts: 964
Joined: Fri Dec 16, 2011 8:20 pm

Re: Lock a column in Base

Postby Villeroy » Sat Jun 29, 2013 12:18 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28220
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Lock a column in Base

Postby F3K Total » Sat Jun 29, 2013 3:57 pm

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
F3K Total
Volunteer
 
Posts: 964
Joined: Fri Dec 16, 2011 8:20 pm

Re: Lock a column in Base

Postby Ratslinger » Fri Jan 15, 2016 1:57 am

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 129 times
LibreOffice 6.1.3
OpenOffice 4.0.1
Ubuntu Mate 18.04
Mint 18.3
Ratslinger
 
Posts: 22
Joined: Sun Mar 01, 2015 3:34 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest