Number not incrementing using a Recordset method

[neelsfer]

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 if possible.
I have attached part of the application with the tables/forms i use, if anybody can assist please?
I have an idea that the problem has to do with

strWhere = "[RaceDate] = #" & [Forms]![FrmRTmainChip]![RacingDate] & "# AND [RaceNo] = " & _
            varRet(0)

Dim varRet As Variant
Dim MyDB As DAO.Database
'Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strInputString As String
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)
 
strInputString = Me![strinput1]
 
 'See if Trailing Comma (,) is present, if so Extract it!
If Right$(strInputString, 1) = "," Then
   strInputString = Left$(strInputString, Len(strInputString) - 1)
End If
 
varRet = Split(strInputString, ",")
 
'Let's find the Lap Number based on the Race Date, Race Name, and Race Number   -  THE RACENAME HAS A PROBLEM  _ TEMPORARILY REMOVED
'strWhere = "[RaceDate] = #" & [Forms]![FrmRTmainChip]![RacingDate] & "# AND " & _
           "[RaceName] = '" & Forms![FrmRTmainChip]![RaceName] & "' AND [RaceNumber] = " & _
            varRet(0)
strWhere = "[RaceDate] = #" & [Forms]![FrmRTmainChip]![RacingDate] & "# AND [RaceNo] = " & _
            varRet(0)
intLapNum = Nz(DMax("[LapNo]", "RaceEntry2", strWhere), 0)
 
'Select Case UBound(varRet)      'How many Race Numbers?
  'Case 0        '1 Race#
 
With rst2
      .AddNew
   ![RaceName] = Forms![FrmRTmainChip]![RaceName]  
        ![RaceNo] = varRet(0)
        ![FinishTime] = Format(Now(), "General Date")
        ![RaceDate] = [Forms]![FrmRTmainChip]![RacingDate]
        ![Lapno] = IIf(intLapNum = 0, 1, intLapNum + 1)
        .Fields("Lap" & CStr(intLapNum + 1)) = Format(Now(), "General Date")
 
      .Update
    End With
  rst2.Close
 Set rst2 = Nothing
 
Me.strinput1.Value = ""
 
'Me.Requery
 
[Forms]![FrmRTmainChip]![RaceEntry2sf].Requery
 
With Me
  ![text1].SetFocus        'Can use any Control
  ![strinput1].SetFocus
End With

[neelsfer]

I have just cleaned up the code a bit and rather use the new attachment below please
RaceName = textfieldtype ; Racenumber =number ; RaceDate =long date; LapNo=number type

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]![RacingDate] & "# AND " & _
           "[RaceName] = '" & Forms![frmXCTiming]![RaceName] & "' AND [RaceNumber] = " & _
            varRet(0) -     THE RACENAME HAS A PROBLEM  _ TEMPORARILY REMOVED
strWhere = "[RaceDate] = #" & [Forms]![frmXCTiming]![Racedate] & "# AND [RaceNo] = " & _
            Me.strInput1
intLapNum = Nz(DMax("[LapNo]", "RaceEntry2", strWhere), 0)
 
 
With rst2
      .AddNew
 ![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")
 
      .Update
    End With
  rst2.Close
 Set rst2 = Nothing
 
Me.strInput1.Value = ""
 
'Me.Requery
 
[Forms]![frmXCTiming]![RaceEntry2SF].Requery
 
With Me
  ![Text1].SetFocus        'Can use any Control
  ![strInput1].SetFocus
End With

[zmbd]

Ok,
I haven't opened your attachment... but I have a question... is the "RaceNo" always a unique value or is it only unique within a given date and racename?

Basic trouble shooting:
As given in your second post:
As you don't give any details as to what is actually happening, errors, etc..., I can only guess that you are getting the new record entry that lines 23-33 are calling for and that the value in [Lap] is only returning a value of 1 and the remaining field values are correct.

Working on that assumption:
Let check how your conditional is resolving and if you
actually have any returned information in your dmax on line 20
as listed in your Second posted code:
At Line 21 or 22; enter STOP compile and save
Run your application entering the racenumber or whatever it is that you need to get the application to this point.
Upon entering break mode and the VBA editor showing
<ctrl+g>
in immediate window
?strwhere
?intLapNum
Check these results against what you're expecting.
Please post back the result for the strwhere.

[neelsfer]

It does not increment the lapno field when a RaceNo is added.
Every RaceNo is unique to one person for that race. If a race consists of ie 5 laps that a RaceNo and finishtime will be added to the DB untill the 5 laps are completed.
I think my problem is with line 8+9

[zmbd]

8 and 9 maybe; however, you only increase my suspicion that you have a malformed string.

Do as you want; however, it would be helpful to have the information I asked for....

[neelsfer]

Zmbd, a friend came to my rescue yesterday and in between a kids party at home we fixed it last night (South Africa time).
The VBA producing my required results for anybody interested,looks like this. I removed the strwhere

Dim varRet As Variant
Dim MyDB As DAO.Database
Dim rst2 As DAO.Recordset
Dim intLapNum As Integer
If IsNull(Me![strInput1]) Then Exit Sub
Set MyDB = CurrentDb
Set rst2 = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
intLapNum = Nz(DMax("[LapNo]", "RaceEntry2", "[RaceNo] = " & Me![strInput1] & _
" AND [RaceName] = '" & [Forms]![frmxctiming]![RaceName] & "'"), 0)
With rst2
      .AddNew
        ![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")
 
      .update
      End With
      rst2.close
      Set rst2 = Nothing
 
      Me.strInput1.Value = ""
 
'Me.Requery
 
[Forms]![frmxctiming]![RaceEntry2SF].Requery
 
With Me
  ![Text1].SetFocus        'Can use any Control
  ![strInput1].SetFocus
End With

[zmbd]

Which for me adds a lot of weight to the malformed string hypothesis, which you might have confirmed using the method I offered and the information it would have returned.

[NeoPa]

Neels, Please see my PM.