change sequential numbering

[Bhoc]

Help please
One of my forms has a text box which is bound to a field called teenumber. This form is set up that "on current" has code
If Me.teenumber = 0 Or IsNull(Me.teenumber) Then
Me.teenumber = Nz(DMax("teenumber", "tblteeofftimesshotgun"), 0) + 1
this enables the text box teenumber to be auto filled with sequential numbers growing by 1 for each record. This database is for a golf tournament and this form enables user to set up tee times for shotgun start for the players.
When the user gets to tee number 18 or whatever the last hole on the course is the teenumber field needs to be reset to 1. With the above code I a unable to do this. Can anyone help me on this problem. I hope I have explained it sufficiently

 

[gemma-the-husky]

you need a function for this. see below

note that the argument teenumber has to be a variant to allow for a null, which is why it is not specified as a datatype. If you had a default valuie of zero the table, then you possibly wouldn't get nulls.

now thinking again, however, after the first 18 holes, every call of your basic dmax function will generate 18, and therefore the function will always returned 1 (or 19) - so you actually also need a cycle indicator to record which block of 18 you have, and increment this when you get to the 19th, and start at 1 again.

it becomes tricky to get a single function to return 2 values (ie the cycle and the hole) so you will need to test how you use this carefully.

function starttee(teenumber, cycle as long) as long
dim tmp as long
 
if nz(teenumber,0)>0 then 
   starttee=teenumber 'already set
else
   cycle = nz(dmax(("cycle", "tblteeofftimesshotgun"),0)
   if cycle=0 then cycle=1
[COLOR=black]tmp = Nz(DMax("teenumber", "tblteeofftimesshotgun","cycle = " & cycle), 0) + 1[/COLOR]
[COLOR=black]if tmp>18 then [/COLOR]
[COLOR=black]     tmp = 1 'back to the beginning[/COLOR]
        cycle = cycle+1
   end if
[COLOR=black]starttee = tmp[/COLOR]
[COLOR=black]end if[/COLOR]
[COLOR=black]end function[/COLOR]

----

thinking again, another proabably much simpler way is to just let the numbers carry on rising, so after 18, you do not go back to 1, you just carry on 19 20 etc. A starttee of 19 is tee 1 in the second group

now the starttee is given by teenumber mod 18 - and the cycle is given by
teenumber\18 [or 1+(teenumber\18)] if you do not want to be zero based.

 

[Mihail]

Or only show the number that you already have MODULO 18.
From the user view point should be no difference.

 

[Bhoc]

Thanks to both of you for the assistance. Will give them a try over the next few days and get back to you.

 

[nanscombe]

If you had a "round" number as well you could increment the tee number, using DMax(), based on the "round" number and the player.

When "tee" hits, or surpasses, the maximum value increment the "round" and start "tee" from 1 again.

 

[Bhoc]

If you had a "round" number as well you could increment the tee number, using DMax(), based on the "round" number and the player.

Nigel
Sorry I don't see what you mean?

Steve

 

[nanscombe]

To my simplistic, non-golfing, mind "A round of Golf" is playing 18 (or 9) holes.

So after each set of 18 holes, or when you reach 19, you add 1 to the number of rounds and set the number of holes back to 1.

Holes played	Rounds	Holes
17		0	17
18		0	18
19		1	1
20		1	2
...
36		1	18
37		2	1

Either that, or increment the round when you reach 18 and reset holes to 0.

Holes played	Rounds	Holes
17		0	17
18		1	0
19		1	1
20		1	2
...
36		2	0
37		2	1

Actually, looking back through the previous posts that is what gemma-the-husky is suggesting with his use of "cycle".

 

[Mihail]

The first Nigel's table can be generated with this formulas:

Rounds = INT((HolesPlayed-1) / NumberOfHolesPerRound)
Holes = HolesPlayed - Rounds*NumberOfHolesPerRound

assuming that number of hole played on each round is
NumberOfHolesPerRound = 18,

 

[Bhoc]

I have tried Gemma code but get a syntax error on cycle
Am a bit lost regarding the other suggestions - can easily include a round field in the table but am lost as to where I put the code that has been suggested by both Nigel and Mihail.
I am certainly no expert when it comes to this

Steve

 

[Mihail]

Unfortunately I have ZERO knowledge in the golf area. And either Nigel, either Dave are more skilled than me in databases area.
I'm pretty sure that I can find a formula to generate the second Nigel's table if you need.
But I think that the best way to help you is to post your database.
I use A2007 version but if you can upload a 2003 version is the best.

 

[gemma-the-husky]

I have tried Gemma code but get a syntax error on cycle
Am a bit lost regarding the other suggestions - can easily include a round field in the table but am lost as to where I put the code that has been suggested by both Nigel and Mihail.
I am certainly no expert when it comes to this

Steve

I think all the suggestions were similar.

on reflection I prefer the idea of numbering on after 18, so eg 40 is start tee 4, on the 3rd round/cycle.

 

[Bhoc]

I have finally had a chance to fully run all options on this and I must thank all of you again.
I used Nigel and Mihail's suggestion and in particular the code
Rounds = INT((HolesPlayed-1) / NumberOfHolesPerRound) Holes = HolesPlayed - Rounds*NumberOfHolesPerRoundWorks like a charm. the end user only sees what hole the players will tee off of

Steve