Page 1 of 1
[Solved] Increment value in column of Table Control
Posted: Wed May 09, 2018 5:34 pm
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.
Re: Increment value in column of Table Control
Posted: Thu May 10, 2018 10:47 am
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?
Re: Increment value in column of Table Control
Posted: Thu May 10, 2018 12:36 pm
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
Re: Increment value in column of Table Control
Posted: Thu May 10, 2018 6:15 pm
by Villeroy
HSQL2 has a ROWNUM() function.
Code: Select all
SELECT ROWNUM() AS "ROW", "Table".* FROM "Table"
ROW
1 ...
2 ...
3 ...
4 ...
5 ...
Re: Increment value in column of Table Control
Posted: Thu May 10, 2018 8:18 pm
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).
Re: Increment value in column of Table Control
Posted: Fri May 11, 2018 10:08 am
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!