Sequential number depending on 2 fields are not adding

[neelsfer]

I need to add sequential lap numbers here.
If person's first racenumber is added, it shows lapno =1;

if same person in same Race and racedate do 2nd lap then lapno must show =2

Mainform is called Racesetupf

Subform where table is, is called RaceTimingSF
The Form and subform are linked via the RaceName field. The Racename and Racedate are both unique

Fields:
Racedate = date field
racename = txtfield
Racenumber = txtfield
Racefinishtime = longdate

code i tried in before insert of form:
Me.LapNo = Nz(DMax("LapNo" , "Racetiming T", " [RaceNumber] = '" & Me.[RaceNumber] & "'"), 0) + 1 --- do it with first lapno only

If i have another race on a different date, then process must start all over again for the Racenumbers + lapno
see attached screen capture pics
For Racenumber = 1 on the first lap the lapno = 1
if you enter Racenumber again, then lapno must change to = 2 etc for different racenumbers. This must work for at least 8 laps
pls help

[ADezii]

The way I see things are a little more complicated then what you indicate. I feel as though the Race Number is the Key to the solution. If the User enters a Race Number in the Sub-Form, it is checked against the previous Race Number. Prior entries for the Race Name, Lap Number, and Name (the actual Runner) must also be retrieved. If the Race Numbers are the same, the previous Race Name matches the current Race Name, and the prior Name (Runner) matches the current Name, then the Lap Number can be incremented by 1 (+1). If the Race Number is different, or the Race Name is different, or the Name is different, the Lap Number must be Reset to 1. Implementing this logic, of course, is a little more complicated than describing it.

[neelsfer]

sounds like i may have a problem; to complicated?

[ADezii]

Not yet. I may still have the DB you sent me, if I do then I'll have a look and see what, if anything, can be done. If I do not have it, I'll request another Demo in Access 2003 Format. I'll post soemthing for sure tomorrow, since I am at work for the entire Day/Evening.

[neelsfer]

thx ; will send you another copy

[ADezii]

Yep, then I'll see what I can do.

[ADezii]

neelsfer, I am not exactly sure that my proposed solution is what you are looking for, but if it isn't it has to be close, and should point you at least in the right direction. Before I post the code, a few minor points:

1. Remove all Code the BeforeInsert() Event of the Sub-Form RaceTimingSF.
2. As previously stated, I feel as though the Key to this problem is in the Race Number, once a new one has been entered. In the 'AfterUpdate()' Event of the [RaceNumber] Field, Copy-N-Paste the following Code. It is well documented, and I assume the Logic is correct, but wouldn't bet my life on it! (LOL).
   

   Private Sub RaceNumber_AfterUpdate()
   Dim strLastRaceNumber As String
   Dim lngLastLapNo As Long
   Dim strCurrentRaceNumber As String
   
   'Retrieve the last Race Number that was entered into Table RaceTimingT, for the specified
   'Race Name, this will be the Last Race Number
   strLastRaceNumber = Nz(DLast("[RaceNumber]", "RaceTimingT", "[RaceName] = '" & _
                       Me.Parent![RaceName] & "'"), "0")
                       
   'Retrieve the Maximum Lap Number that was entered into Table RaceTimingT, for the specified
   'Race Number and Race Name
   lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = '" & Me![RaceNumber] & _
                       "' AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)
   
   'Retrieve the newly entered Race Number
   strCurrentRaceNumber = Me![RaceNumber]
   
   If lngLastLapNo = 0 Then    'Must be a new Race Number, so Reset Lap Number to 1
     Me![LapNo] = 1
   ElseIf (strCurrentRaceNumber = strLastRaceNumber) Then
     'If the Current Race Number equals the prior Race Number for a given Race,
     'increment the Lap Number by 1 (add 1 to the Last Lap Number, given the Race
     'Number and Name
     Me![LapNo] = lngLastLapNo + 1
   Else        'New Race Number, so Reset Lap Number to 0
     Me![LapNo] = 1
   End If
   End Sub

3. Enter a New Record in the Sub-Form, specifically the [RaceNumber] Field. Depending on the Current Race Number entered, the Last Race Number entered, and the specific Race Name, the Lap Number will automatically be populated and will either be incremented by +1 from the prior Race Number for the same Race, or Reset to 1. Whew...enough already!
4. Test the Code, and let me know what you think.

[neelsfer]

thx a lot for your time. it works 100% if you add the same racenumber a few times after each other ie. racenumber 1 , 1 ,1 but as soon as you add another racenumber in between like 1,2,1 then it starts at lapno= 1 again for each of racenumbers. any suggestions pls?.

[ADezii]

Are you saying that the previously entered Race Number does not enter into the equation?

[neelsfer]

let me explain; if i use ie this sequence
racenumber ------ lapno
1 -------------- 1
2 -------------- 1
2 -------------- 2 - correct till here
1 -------------- 1 start at lapno=1 again instead of = 3

[ADezii]

Kindly post 10 correct combinations of Race Number and Lap Numbers, so I can be absolutely sure of the sequence.
P.S. You still did not answer Post# 9 and does the Race Name even matter?

[neelsfer]

Either the racedate or racename can be used to distinguish as both are unique per Race event.
I only have one Racename per one date per one racenumber (athlete)

what is meant is, if ie

This racename may be a 3 laps race in total (usualy not more than 8laps per race per athlete but can vary)
in this sequence now:
Peter who uses racenumber = 1 gets to finishline then lapno = 1.
John arrives next at finishline with racenumber=2 and it is lapno =1
Peter (racenumber = 1)finish another lap and is now Lapno = 2
John (Racenumber= 2)does another lap and lapNo =2
Peter (Racenumber = 1) does his final lap and lapno =3
then John (Racenumber =2)finish his final lap Lapno =3
race is over;
You may have 150 persons in the race each with own Racenumber and all doing perhaps 8 laps
This is how it should be
racenumber ------ correctlapno- ------------wrong lap#
1 ----------------------1---------------------------1
2 --------------------- 1---------------------------1
1 --------------------- 2---------------------------1
2 --------------------- 2---------------------------1
1-----------------------3---------------------------1
2-----------------------3---------------------------1
race over
As its now, the lapno start all over again at = 1 if you dont have the same racenumber(1,1, 1) sequence after another when racenumbers are mixed up (1,2,1,2,2,1)
Basicly it must look at what was last lapno for a specific racenumber, and add one to that.
thx for the trouble

[ADezii]

Replace the Code in the AfterUpdate() Event of the [RaceNumber] Field with this more simplified Version, and let's see what happens:

Private Sub RaceNumber_AfterUpdate()
Dim lngLastLapNo As Long
                    
'Retrieve the Maximum Lap Number that was entered into Table RaceTimingT, for the specified
'Race Number and Race Name
lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = '" & Me![RaceNumber] & _
                    "' AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)

If lngLastLapNo = 0 Then    'Must be a new Race Number, so Reset Lap Number to 1
  Me![LapNo] = 1
Else
  Me![LapNo] = lngLastLapNo + 1
End If
End Sub

[neelsfer]

ADezi you are a STAR!!!! working 100% now. I owe you big time.
Thx

[ADezii]

You are quite welcome. As it turns out, I was actually over-complicating the issue. Sometimes, especially for me, trying to decipher a problem at the other end of a Web Page is not nearly as simple as it seems. There are two items that you may wish to consider:

1. Change the [RaceNumber] Field from TEXT to NUMBER, unless you have a very good reason not to.
2. Lock the [LapNo] Field in the Sub-Form. Now that the code is working 100%, the proper Lap Number will be written to this Field after the Update of [RaceNumber].
3. The real problem lies if the User should enter an erroneous Value such as 57, which would now kill the correct sequence.