Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    9

    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?

    Code:
    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] = " & _
                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
    Attached Files Attached Files
    Last edited by Neelsfer; 11-10-12 at 04:07.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try this:

    dim strSQL as string

    'Let's find the Lap Number based on the Race Date, Race Name, and Race Number


    strSQL = "SELECT Max(LapNo) as MaxLapNo FROM RaceEntry2 " & _
    "WHERE RaceDate = #" & [Forms]![frmXCTiming]![Racedate] & "# AND RaceNo = " & _
    Me.strInput1


    Set rst2 = MyDB.OpenRecordset(strSQL)

    if rs2.recordcount>0 then
    rs2.movefirst
    intLapNum=rs2.fields("MaxLapNo")
    else
    intlapnum=0
    end if


    And this:

    ![Lapno] = intLapNum

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •