Unanswered: Need number to increment in recordset based on criteria
I have been struggling for 2 weeks now with an incrementing problem.
If you add a raceNo once then the lapno =1. If same number is added again, then the lapno should be 2 etc etc
I use A2007 but attachment is saved as A2003.
I would prefer to filter the strwhere in code on "RaceName" and "RaceNo" and exclude dates.
RaceName = textfieldtype ; Racenumber =number ; RaceDate =long date; LapNo=number type
I have attached part of the application with the tables/forms i use, if anybody can assist please?
Dim varRet As Variant
Dim MyDB As DAO.Database
Dim rst2 As DAO.Recordset
Dim strWhere As String
Dim intLapNum As Integer
If IsNull(Me![strInput1]) Then Exit Sub
Set MyDB = CurrentDb
Set rst2 = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
'Let's find the Lap Number based on the Race Date, Race Name, and Race Number
'strWhere = "[RaceDate] = #" & [Forms]![frmXCTiming]![RaceDate] & "# AND " & _
"[RaceName] = '" & Forms![frmXCTiming]![RaceName] & "' AND [RaceNumber] = " & _
varRet(0) - THE RACENAME HAS A PROBLEM _ TEMPORARILY REMOVED
strWhere = "[RaceDate] = #" & [Forms]![frmXCTiming]![Racedate] & "# AND [RaceNo] = " & _
intLapNum = Nz(DMax("[LapNo]", "RaceEntry2", strWhere), 0)
![Racename] = Forms![frmXCTiming]![Racename]
![RaceNo] = Me.strInput1
![FinishTime] = Format(Now(), "General Date")
![Racedate] = [Forms]![frmXCTiming]![Racedate]
![Lapno] = IIf(intLapNum = 0, 1, intLapNum + 1)
.Fields("Lap" & CStr(intLapNum + 1)) = Format(Now(), "General Date")
Set rst2 = Nothing
Me.strInput1.Value = ""
![Text1].SetFocus 'Can use any Control