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", "RacetimingT", " [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
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: - Remove all Code the BeforeInsert() Event of the Sub-Form RaceTimingSF.
- 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
- 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!
- Test the Code, and let me know what you think.
16 1956
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.
sounds like i may have a problem; to complicated?
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.
thx ; will send you another copy
Yep, then I'll see what I can do.
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: - Remove all Code the BeforeInsert() Event of the Sub-Form RaceTimingSF.
- 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
- 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!
- Test the Code, and let me know what you think.
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?.
Are you saying that the previously entered Race Number does not enter into the equation?
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
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?
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
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
ADezi you are a STAR!!!! working 100% now. I owe you big time.
Thx
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: - Change the [RaceNumber] Field from TEXT to NUMBER, unless you have a very good reason not to.
- 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].
- The real problem lies if the User should enter an erroneous Value such as 57, which would now kill the correct sequence.
I changed the Racenumber to a "number field" as suggested. What changes do i make to the code now to make it work again pls?
got it right - lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = " & Me![RaceNumber] &
- " AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Michael Pedersen |
last post by:
Hi group
I want to be able to expand a huge form with a number of text fields.
The number of fields must be entered in a form field and submitted, or
alternatively be a drop down menu. I have...
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what...
|
by: GAVO. |
last post by:
Hello every one
I have a database with a form called "frmOrders" on that for I need to
create a sequential number for each city apart from the original autonumber.
So the table "tblorders" would...
|
by: Megan |
last post by:
Hi Everybody-
I know that this is a really, really long post, but I wanted to try to
give you as much background as possible.
So here's a quick overview of the issues I'm asking for help with:...
|
by: Proteus |
last post by:
Hi, I'm using access 97 on windows 98.
I have table with 162 fields in it. I need to withdraw this information
in a (totals) query with 164 fields (162 "avg" fields and 2 "count"
fields). When I...
|
by: Nooby |
last post by:
New to Access here. I inherited a db that has the first column as an
automatically generated sequential number. I want to bump it up, but
for the life of me I can't figure out how to do it. Is...
|
by: masker |
last post by:
I was on the web trying to find a javascript that would allow me to input a number on my website and have it increase by a given percentage every second.
During my search I found the Earth...
|
by: linsha |
last post by:
hi...
I would like to know about maximum number number of fields that can be created in a table. my requirement is to meet data entry in morethan 300 fields. I am using SQL 2000. if impossible is...
|
by: MSakthi1985 |
last post by:
Query for how to retrieve number of fields in the table for postgreSQL
|
by: Pauline Bennison |
last post by:
Sorry but I am a newbie and this question is very basic.
What is the total number of fields I can have in a MS Access database.
Pauline
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |