[Solved] Increment value in column of Table Control

Creating and using forms
Post Reply
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

[Solved] Increment value in column of Table Control

Post by Nocton »

I have a table control on a sub form named subForm1 linked to table GameResults . The Field Names of the table are GameNo, HomePoints, AwayPoints which are associated with column fields of the same name, plus MatchID which links to the main form and GameID which is the unique identifier for the game. Data is entered as game numbers from 1 to 12 and the corresponding points scored for each game.

I should like the Game number to automatically increment, i.e. go 1, 2, 3, etc. as the user tabs/moves to each row. I cannot make GameNo an autovalue in the table as that is used for GameID which is the primary key for the table. Is it possible to auto-increment GameNo without using a macro? I thought the Incr./decrement value of the Column Numeric Field might be what I wanted, but it does not seem to do anything.
Last edited by Hagar Delest on Fri May 11, 2018 4:47 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.12 on Windows 10
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Increment value in column of Table Control

Post by Nocton »

Having had no reply, I have created the following code:

Code: Select all

Sub UpdateGameNo 
dim oForm1,oForm2,oForm3 as object
dim sText1 as string 
dim N1,N2 as integer 

oForm1=ThisComponent.Drawpage.Forms.getByName("FilterForm") 
oForm2=oForm1.getByName("MatchResultsForm")
oForm3=oForm2.getByName("subForm1")
N1=oForm3.Columns.findColumn("GameNo")
sText1 = oForm3.getString(N1)
N2=CSng(sText1)+1
oForm3.Columns.getByName("GameNo").updateString(Cstr(N2)) 

End Sub
The problem with it is that is sText1 = oForm3.getString(N1) always returns the first row, not the current row, so I always get N2 as 2. Does anyone know how to find the current row?
OpenOffice 4.1.12 on Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Increment value in column of Table Control

Post by RPG »

I want make clear I do not have a clear solution for your problem. First I think it is good to start with some thinking about parameters in a form as you see in the form navigator. I must also make clear I do not use it much to only real little.

Villeroy and also Arineckaig did explain it real often on this forum. So I think it is good to search for it. I want tell some thing about the parameters what comes real slowly clear to me:

When you use a parameter in a form and a sub form then you can use the parameter in both sides of the function
:Parameter = :Parameter

When you make a parameter in a query what you use in a subform then the name of the parameter without the colon can also be used and OpenOffice does add the colon to the name so it a Parameter.

I do not know what you can do with it for your problem but I remember you are a good programmer so it is maybe possible you knew it otherwise it is possible it is new for you and you can work with it. I think the form and subforms in combination with slave- and masterfield is a programming tool.

I hope you can find a solution with the idea above. When you want continue with macro's then you must work with an event after record change.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment value in column of Table Control

Post by Villeroy »

HSQL2 has a ROWNUM() function.

Code: Select all

SELECT ROWNUM() AS "ROW", "Table".* FROM "Table"
ROW
1 ...
2 ...
3 ...
4 ...
5 ...
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
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Increment value in column of Table Control

Post by chrisb »

Nocton,
can you guarantee that data is always input in the correct order?
is saving a few keystrokes worth the risk?
if you cannot answer yes to both questions then forget about it.

you have an identity column "GameID" from which "GameNo" can be calculated.
if "GameID" is sequential ie. start to finish step 1 without gaps then this should work:

Code: Select all

select g.*, 1+ mod("GameID", 12) "GameNo" from "GameResults" g
if "GameID" is sequential ie. start to finish step 1 with gaps then this should work:

Code: Select all

select g.*,
1+ mod((select count(*) from "GameResults" where "GameID" < g."GameID"), 12) "GameNo"
from "GameResults" g
i have assumed that "GameID" begins with 0 (zero).
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Increment value in column of Table Control

Post by Nocton »

Thank you for all replies. I have decided to take Chrisb's advice:
can you guarantee that data is always input in the correct order?
is saving a few keystrokes worth the risk?
if you cannot answer yes to both questions then forget about it.
The background to my query is that I wrote the program to manage (croquet) leagues results 6 years ago. It has been modified and improved over that time and has been used by two league managers. I did look at the option of automating the Game Numbers once before, but decided it was too difficult. This year a new league manager has taken over and asked the question again - hence my post. However, chrisb, I think you are right as currently one can enter game numbers out of order or make a mistake and then correct it - as soon as the form is refreshed the numbers are sorted correctly. Even implementing some sort of 'correct' code using the suggested ideas, if data entry does not go smoothly and correctly then it is easy to generate an unexpected game number. The screen seen by the user is complex with a Filter form to select league and year, a Match subform, then the results subform with several other sub forms to show other information. So I am going to leave well alone as everything currently works smoothly and correctly and as you say it is only a few keystrokes more. Some things are better not programmed/automated!
OpenOffice 4.1.12 on Windows 10
Post Reply