Dlookup that wont update beteen the rows

[AndersL]

Hello everybody! this is my first thread here so lets hope im doing this right

I have been a passive "member" now for a couple of months but now i have run in to a problem who I have not solved yet (stuck on the same issue now for a couple of days).

I think it is easy to solve but now i have been staring att this for so long time i have a hard time seeing the forest for all the trees if you know what i mean

I have a table that im modifying by using vba code.
I will use the table to plot a chart over the "gap" time between drifferent race cars.
So far i have the "delta time", the time that differs between the actual driver and the winner for every lap and i call this Gap. I want to add the gap times so that for example on lap 2 the total gap is gap for lap 1 + gap for lap 2 and so on to be able to plot how the gap is developing troughout the race.

Im trying to use Dlookup function to get the previous laps gap and add this to the actual gap post.

The Part of the code that I need help whith looks like this:

Set db = CurrentDb()
Set Rst = db.OpenRecordset("GapAnalysis")


For x = 0 To Rst.RecordCount - 1
    For m = 2 To Rst!MaxLaps
        If Rst!Lap2 = 1 Then
            Rst.Edit
            Rst!Gap3 = Rst!Gap
            Rst.Update
            ElseIf Rst!Lap2 = [m] Then
            Rst.Edit
            Rst![Gap3] = DLookup("[Gap2]", "GapAnalysis", "Lap2 =" & m - 1 & "And [Driver] = [Förare] ")
            Rst.Update
        End If
    Next m
    Rst.MoveNext
Next x

The issue now is that when i run this it writes the first drivers previous laps gap on every row in the actual lap. I realize that this is a bit fuzzy to understand so i will try to make a table to show...

Lap     Gap     Driver    Gap3
1        0        Bob       Gap
1        4,54    Dave       Gap
1        3,21    Pete       Gap
2        0        Bob       Gap(Lap = 2 ) + Gap(Lap = 1 And Driver = Bob)
2        7,89    Dave       Gap(Lap = 2 ) + Gap(Lap = 1 And Driver = Dave)
2        2,67    Pete       Gap(Lap = 2 ) + Gap(Lap = 1 And Driver = Pete)
3        0        Bob       Gap(Lap = 3 ) + Gap(Lap = 2 And Driver = Bob)
3        8,93    Dave       Gap(Lap = 3 ) + Gap(Lap = 2 And Driver = Bob)
3        1,76    Pete       Gap(Lap = 3 ) + Gap(Lap = 2 And Driver = Bob)

And so on...

As you can see Bob is the winning driver and therefore he has 0 in gap trougout the race. Dave and Pete are on place 2nd and 3rd and they have a time gap.

What happens when I run it is:

Lap     Gap     Driver    Gap3
1        0        Bob       Gap
1        4,54    Dave       Gap
1        3,21    Pete       Gap
2        0        Bob       Gap(Lap = 2 ) + Gap(Lap = 1 And Driver = Bob)
2        7,89    Dave       Gap(Lap = 2 ) + Gap(Lap = 1 And Driver = Bob)
2        2,67    Pete       Gap(Lap = 2 ) + Gap(Lap = 1 And Driver = Bob)
3        0        Bob       Gap(Lap = 3 ) + Gap(Lap = 2 And Driver = Bob)
3        8,93    Dave       Gap(Lap = 3 ) + Gap(Lap = 2 And Driver = Bob)
3        1,76    Pete       Gap(Lap = 3 ) + Gap(Lap = 2 And Driver = Bob)

I havent added the + current gap part in this draft of the code but that should be easy peasy

Hope that some of you have time to answer a lost newbie
Best Regards
Anders

 

[AndersL]

Oh, those explaining tables didnt turn out to good

 

[spikepl]

Use indentation for code, and code tags for code and data. Go advanced -> select code/data-> click #

 

[spikepl]

Stuff like gap1, gap2, gapX is Excel not Access, and causes no end of problems. In Access you store similar items in the same container, and just mark the data with what it is. So,

tblGaps
--------
GapID
Gap
LapNo

or something like that.

I could imagine that the gap is itself derived from laptime ... so just store the laptime for each driver and proceed from there.

tblLapTimes
------------
LapTimeID
LapNo
DriverID
LapTime

You should also know that rst.RecordCount is not reliable - it only reflects the true value when the entire recordset is loaded, otherwise it will be >0 but an almost arbitary value. Prior to examining that value do

.MoveLast ' this forces loading of the entire recordset
.MoveFirst

or forget about the count and do a loop like this:

DO while NOT rst.EOF

Loop

 

[AndersL]

Stuff like gap1, gap2, gapX is Excel not Access, and causes no end of problems. In Access you store similar items in the same container, and just mark the data with what it is. So,

tblGaps
--------
GapID
Gap
LapNo

or something like that.

I could imagine that the gap is itself derived from laptime ... so just store the laptime for each driver and proceed from there.

tblLapTimes
------------
LapTimeID
LapNo
DriverID
LapTime

Yes that is correct, the reason for using access is that the data from the races are saved for numerous years in an access database to be able to look back in time to evaluate what happend last time we drove on this track. The whole database system is bases quite similar as you wrote but i didnt think that part of the program was interesting so i didnt include that in my question.

You should also know that rst.RecordCount is not reliable - it only reflects the true value when the entire recordset is loaded, otherwise it will be >0 but an almost arbitary value. Prior to examining that value do

I didnt tink about it like that but it makes sense, i have changed that in my code.

This still does not influence the result when i run the program. It still only updates the Dlookup function one time for every lap, not for every driver for every lap as i want it to.

It seems like the Dlookup function is not using the Driver = Förare criteria, since the result is the same if i remove the Driver = Förare critera
Do you have any idea of what im doing wrong or what i can do different?

Best regards
Anders

 

[AndersL]

Anyone who has any ideas to solve this or any suggestions on another aproach to solve the problem?

Best regards
Anders

 

[WayneRyan]

Anders,

The whole database system is bases quite similar as you wrote but i didnt think that part of the program was interesting so i didnt include that in my question.

We need to know your table structure to answer the question. The structure is very interesting to someone who wants to help solve your problem.

Wayne

 

[AndersL]

What I basically want to do is to do a running sum over the laps. I want this for every driver.

How do I show to you how my table looks in a good way? should I take a print screen and upload it here?

Best Regards
Anders

 

[AndersL]

Now I have solved the problem, I did not use a Dlookup. Instead I did some looping that did the trick.
Thanks for the help!

This is the code i used:

Set db = CurrentDb()
Set Rst = db.OpenRecordset("GapAnalysis")

Rst.MoveFirst

hold_ID = Rst!ID
hold_Count = 0

Do While Not Rst.EOF

    If hold_ID < Rst!ID Then
        hold_Count = 0
        hold_Count = hold_Count + Rst!Gap

    Else
        hold_Count = hold_Count + Rst!Gap

    End If
    hold_ID = Rst!ID
    Rst.Edit
    Rst!Gap2 = hold_Count
    Rst.Update
    Rst.MoveNext
Loop

Best Regards
Anders Larsson