Data access and Domain Functions

[Jason]

I am developing a lapscoring database. The riders number
is taken, thier previous lap is looked up to determine
how many laps they have done

Its really weird, I tried using DAO - findlast() method,
it looks up the last record in the table with a memberID
as the criteria, seems to work good until about lap 5 -
stops finding the last record - sometimes. I use it to
increment the next lap i.e. the next lap = the last lap
for that member + 1.

The problem seems inconsistent. I tried using a query for
the data source, tried using "DLAST" method on the table
direct - same problem. I even moved the table out of
Access into MYSQL - same problem. Had a bit more luck
using "DMAX" but I still don't trust it.


Code below:
Set rstLapScore = CurrentDb.OpenRecordset("select
* from tblLapScore where raceid = " _
& intRaceID & "and
RiderNumber = '" & strNumber & "'", dbOpenDynaset)

'Set rstLapScore = CurrentDb.OpenRecordset
("qryLapScoreRider", dbOpenDynaset)

With rstLapScore
'>>>>>>>>>>>>>>>>>>>>>>>>>>> problem is below
intLap = Nz(DMax
("Lap", "tblLapScore", "RiderNumber = '" & strNumber
& "'"), 0) + 1

'add rider's lap
.AddNew
!RaceID = intRaceID
!SignOnID = intSignOnID
!RiderNumber = strNumber
!lap = intLap
.Update

End With

 

[Marshall Barton]

I am developing a lapscoring database. The riders number
is taken, thier previous lap is looked up to determine
how many laps they have done

Its really weird, I tried using DAO - findlast() method,
it looks up the last record in the table with a memberID
as the criteria, seems to work good until about lap 5 -
stops finding the last record - sometimes. I use it to
increment the next lap i.e. the next lap = the last lap
for that member + 1.

The problem seems inconsistent. I tried using a query for
the data source, tried using "DLAST" method on the table
direct - same problem. I even moved the table out of
Access into MYSQL - same problem. Had a bit more luck
using "DMAX" but I still don't trust it.


Code below:
Set rstLapScore = CurrentDb.OpenRecordset("select
* from tblLapScore where raceid = " _
& intRaceID & "and
RiderNumber = '" & strNumber & "'", dbOpenDynaset)

'Set rstLapScore = CurrentDb.OpenRecordset
("qryLapScoreRider", dbOpenDynaset)

With rstLapScore
'>>>>>>>>>>>>>>>>>>>>>>>>>>> problem is below
intLap = Nz(DMax
("Lap", "tblLapScore", "RiderNumber = '" & strNumber
& "'"), 0) + 1

'add rider's lap
.AddNew
!RaceID = intRaceID
!SignOnID = intSignOnID
!RiderNumber = strNumber
!lap = intLap
.Update

End With

Not sure I follow all the attempts above, but you should be
aware that Last and DLast refer to the last record found in
an unordered bag of records (i.e a table) so they are nearly
usles for this purpose. The easiest way to get the latest
lap is to use the TOP predicate and an ORDER BY clause:

strSQL = "SELECT TOP 1 table,* " _
& "FROM tblLapScore " _
& "WHERE raceid = " & intRaceID _
& " And RiderNumber = '" & strNumber & "' " _
& "ORDER BY lap DESC"
Set rstLapScore = CurrentDb.OpenRecordset(strSQL, _
dbOpenDynaset)
With rstLapScore
If .RecordCount > 0 Then
intLap = !Lap + 1
Else
intLap = 1
End If
.AddNew
. . .

 

[Jason]

Marshall

Thank you very much for this fix. I also checked out
Predicates on google, this will be a valuable tool.

Regarding the rational behind my attempts to get around
the problem I had - tiredness and blind aggravation are
my only excuses OH and chuck in a bit of ignorance too.

Thanks again.

Regards Jason Willis

 

[Marshall Barton]

You're welcome.

Get some sleep and forge ahead. The ignorance will vanish
like a morning fog in the noonday sun soon enough ;-)

 

[Tim Ferguson]

The ignorance will vanish
like a morning fog in the noonday sun

Poetry in a programming group..?


<g>

Tim F