How to add sequential numbers to two recordsets

[neelsfer]

Ok i am back with a question about recordsets.
I can add a record to two different recordsets at the sametime.(RacetimingT and RaceEntry5)

My problem: how do i add a sequential number to both recordsets and then copy data to different fields in these recordsets based on the sequential number.

It works 100% if i first add the data to a subform and then use beforeupdate to create sequential number and afterupdate to open recordset and copy it to different fields based on the sequential no. I would prefer to use recordsets for other technical issues - see code.
I use this code to copy data into the 2 recordsets and it works

 Dim varRet As Variant
 Dim MyDB As DAO.Database
 
'Dim strSQL As String
 
  Set MyDB = CurrentDb
 Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
 If IsNull(Me![strInput1]) Then Exit Sub
 
'strSQL = "SELECT * FROM RaceEntry5 WHERE [racetimingId] = " & Me![racetimingId]
 'See if the Data has already been Captured, if not, Add, not Edit the Record
 
'If DCount("*", "Racetiming", "[racetimingId] = " & Me![racetimingId]) = 0 Then
 
 Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
' If DCount("*", "RaceEntry5", "[racetimingId] = " & Me![racetimingId]) = 0 Then
 
 Set rst2 = MyDB.OpenRecordset("RaceEntry5", dbOpenDynaset, dbAppendOnly)
 
 Dim strInputString As String        'Move with other Declarations
 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, ",")
 
 Select Case UBound(varRet)      'How many Race Numbers?
   Case 0        '1 Race#
     With rst
            .AddNew
         'Must ADD the Child Linking Field
         ![RaceNumber] = varRet(0)
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![Racedate] = [Forms]![frmrtmainchip]![RacingDate]
         '![RaceName] = [Forms]![frmrtmainchip]![RaceName]
         '![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
       .update
        End With
    With rst2
            .AddNew
         'Must ADD the Child Linking Field
         ![RaceNo] = varRet(0)
         ![FinishTime] = Format(Now(), "General Date")
         ![Racedate] = [Forms]![frmrtmainchip]![RacingDate]
      '![RaceName] = [Forms]![racesetupxcf]![RaceName]
       .update
        End With
 Case Else     'Who knows
        'Me.strInput.SetFocus
     'Do Nothing
 End Select
 rst.close
 rst2.close
 Set rst = Nothing
 Set rst2 = Nothing

The current code in my subform to add the sequential numbers to the RacetimingT that must be incorporated into the above.

Dim lngLastLapNo As Long
 
    lngLastLapNo = Nz(DMax("[LapNo]", "RaceTimingT", "[RaceNumber] = " & Me![RaceNumber] & _
" AND [RaceName] = '" & Me.Parent![RaceName] & "'"), 0)
   If lngLastLapNo = 0 Then    'Must be a new Race Number, so Reset Lap Number to 1
  Me![LapNo] = 1
  Else
   Me![LapNo] = lngLastLapNo + 1
     End If
 

The current code in my subform is used to copy data to the RaceEntry5 recordset

  Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
 Set MyDB = CurrentDb
strSQL = "SELECT * FROM RaceEntry5 WHERE [racetimingId] = " & Me![racetimingId]
 'See if the Data has already been Captured, if not, Add, not Edit the Record
If DCount("*", "RaceEntry5", "[racetimingId] = " & Me![racetimingId]) = 0 Then     'NOT Captured/ADD
  Set rst = MyDB.OpenRecordset("RaceEntry5", dbOpenDynaset, dbAppendOnly)
  With rst
 .AddNew
 ![Racedate] = Me.Parent![RacingDate]
 ![RaceNo] = Me![RaceNumber]
 ![LapNo] = Me![LapNo]
 ![Entries] = Me![FinishSeq]
 ![lap10] = Me![RaceFinishTime]
 ![racetimingId] = Me![racetimingId]
 .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
 ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
  .update
  End With
  Else        'Data Captured, so Edit the Recordset
  Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
    With rst
    .Edit
  ![Racedate] = Me.Parent![RacingDate]
 ![RaceNo] = Me![RaceNumber]
 ![LapNo] = Me![LapNo]
 ![Entries] = Me![FinishSeq]
 ![lap10] = Me![RaceFinishTime]
 ![racetimingId] = Me![racetimingId]
 .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
 ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
   .update
    End With
   End If
   rst.close
   Set rst = Nothing

Anybody out there that can assist pls. I need to make these changes in order for one of the modules in my application to work with RFID. thx

[ADezii]

I am very confused as to the exact nature of your Request, but I'll give it a stab. The following Code will add Sequential Numbers to 2 Recordsets ([Seq]) as well as adding a Unique Value to a Field in each each Recordset ([Result]) based on those Sequential Numbers.

Dim MyDB As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim lngSeqNum As Long
 
Set MyDB = CurrentDb
Set rst1 = MyDB.OpenRecordset("Table1", dbOpenDynaset)
Set rst2 = MyDB.OpenRecordset("Table2", dbOpenDynaset)
 
For lngSeqNum = 1 To 10
  'ADD 10 Sequential Numbers to both Recordsets to the [Seq] Field. In
  'the 1st Recordset, add 100 to the Sequential Number and store it in a
  '[Result] Field. In the 2nd Recordset, square the Sequential Number and
  'store it in a [Result] Field.
  rst1.AddNew
    rst1![Seq] = lngSeqNum
    rst1![Result] = (lngSeqNum + 100)
  rst1.Update
 
  rst2.AddNew
    rst2![Seq] = lngSeqNum
    rst2![Result] = (lngSeqNum ^ 2)
  rst2.Update
Next
 
rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst2 = Nothing

[neelsfer]

Thx adezi. Sorry for complicating it like this.

To simplify it: In ie the racetimingT and RaceEntry5 tables (recordsets), the code must look individually at multiple fields in each of these tables and then add a sequential number to the LapNo field (number field) based on the current "racedate", and the "racenumber" fields (number field) in both.
Thus in this RacetimingT + RaceEntry5 tables, you will have many different racedates and racenumbers and lapno's from previous races.

What it must do - in the "RacetimingT" + RaceEntry5 recordsets it must look at the current "racedate" and current "racenumber" and the current "lapno" fields, and if the "lapno" = 0 or blank then it must add a "1" for this new racedate and racenumber when it gets added. If the lapno = "1" for these racedate and "racenumber" fields, then it must make it "2" etc etc
.
This same data will be added to both the RacetimingT and RaceEntry5 (tables) recordsets based on the criteria from previous records added.

to conclude:
RaceTimingT + RaceEntry5 tables = "RaceNumber" + "RaceDate"+ "LapNo" fields and the sequential number goes to the "Lapno" field.

Dim varRet As Variant
 Dim MyDB As DAO.Database
 Set MyDB = CurrentDb
 Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
  If IsNull(Me![strInput1]) Then Exit Sub
 
  Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  Set rst2 = MyDB.OpenRecordset("RaceEntry5", dbOpenDynaset, dbAppendOnly)
Select Case UBound(varRet)      'How many Race Numbers?
   Case 0        '1 Race#
     With rst
          .AddNew
         'Must ADD the Child Linking Field
         ![RaceNumber] = varRet(0)
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![Racedate] = [Forms]![frmrtmainchip]![RacingDate]
         ???? if racenumber and racedate exist for Lapno = 0, then add 1 to it etc etc (can go up to infinity)
       .update
        End With
    With rst2
            .AddNew
         'Must ADD the Child Linking Field
         ![RaceNo] = varRet(0)
         ![FinishTime] = Format(Now(), "General Date")
         ![Racedate] = [Forms]![frmrtmainchip]![RacingDate]
???? if racenumber and racedate exist for Lapno = 0, then add 1 to it etc etc (can go up to infinity)
       .update
        End With
 Case Else     
 
 End Select
 rst.close
 rst2.close
 Set rst = Nothing
 Set rst2 = Nothing

Hope it makes sense.

[ADezii]

1. Is there a Linking Field between these 2 Tables? If so, what is it's name and Data Type?
2. What exactly is the Relationship between these Tables?

[neelsfer]

no its 2 separate tables with same fields but the data gets added to both.
RacetimingId is a unique ID field in RacetimingT table and "RaceEntry5Id in the RaceEntry5 table when data gets added to it

[neelsfer]

Adezi
One can't perhaps add this also to the code for each of the tables

.Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]

This is to populate the racefinishtime of that specific lapno, to fields called either lapno1 or lapmo2 or lapno3 etc depending on the specific lapno involved in the racetimingT and RaceEntry5 tables

[ADezii]

Sorry neelsfer, but I am still at somewhat of a loss. At this point, I would need a condensed Version of the Database (2003) with a precise explanation of what needs to happen at a specific point.

[neelsfer]

Thx adezi i will send one tonight South Africa time

[neelsfer]

Hi Adezi
It must have been very confusing. Here is the DB. I have added instructions on the screens. thx for your time

[ADezii]

Will.get.back.to.you.

[neelsfer]

thx adezi i appreciate your time

[ADezii]

I do believe that I have arrived at a viable solution. Rather than post the partial Code Modifications, I'll simply Attach a Revised Database, and we'll take it from there.

[neelsfer]

Thx Adezi you are spot on so far.
Is there anyway one can populate the following data to the lap1-10 fields in the RaceEntry5 table?

Rst2
If Me.Lapno = "1" Then Me![Lap1] = Format(Now(), "General Date")
etc etc

[ADezii]

Is there anyway one can populate the following data to the lap1-10 fields in the RaceEntry5 table?

I'll see what I can come up with, either later today, or this weekend.

[ADezii]

I think that Code Line #22 is what you are looking for:

'************************* Code Intentionally Omitted *************************
'Select Case UBound(varRet)      'How many Race Numbers?
  'Case 0        '1 Race#
    With rst
      .AddNew
        ![RaceName] = Forms![frmrtmainchip]![RaceName]
        ![RaceNumber] = varRet(0)
        ![RaceFinishTime] = Format(Now(), "General Date")
        ![RaceDate] = [Forms]![frmrtmainchip]![RacingDate]
        ![Lapno] = IIf(intLapNum = 0, 1, intLapNum + 1)
      .Update
    End With
 
    With rst2
      Select Case (intLapNum + 1)
        Case 1 To 10        'Valid Lap Numbers
          .AddNew
            ![RaceNumber] = varRet(0)
            ![RaceFinishTime] = 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
        Case Else
          'Don't know what you want to do here. Validation on the Lap Number
          'should have previously been applied, before any Updates
      End Select
    End With
'Case Else     'Who knows
'End Select
'************************* Code Intentionally Omitted *************************
 

[neelsfer]

You are a legend Adezi! thx a million!!

[ADezii]

You are quite welcome, neelsfer.

[neelsfer]

Hi Adezi - i have picked up a bug. If i have 2 dates in DB of different races, then it seems to move to the next date when i add a new racenumber. I attach DB

[ADezii]

If i have 2 dates in DB of different races, then it seems to move to the next date when i add a new racenumber

Be more specific, I do not see this happening.

[neelsfer]

As you press enter after adding a racenumber, it moves to the next date/race name. Go to 14/10/2011 and add a racenumber to see the problem.
I hope it is not just a A2007 problem
I attach data that i cleaned up in this DB

[ADezii]

'Uncomment or Remove Code Line #33

'************************* Code Intentionally Removed *************************
'Select Case UBound(varRet)      'How many Race Numbers?
  'Case 0        '1 Race#
    With rst
      .AddNew
        ![RaceName] = Forms![FrmRTmainChip]![RaceName]
        ![RaceNumber] = varRet(0)
        ![RaceFinishTime] = Format(Now(), "General Date")
        ![RaceDate] = [Forms]![FrmRTmainChip]![RacingDate]
        ![Lapno] = IIf(intLapNum = 0, 1, intLapNum + 1)
      .Update
    End With
'Case Else     'Who knows
'End Select
With rst2
      .AddNew
       ' ![RaceName] = Forms![FrmRTmainChip]![RaceName]
        ![RaceNumber] = varRet(0)
        ![RaceFinishTime] = 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
 rst.Close
 rst2.Close
 Set rst = Nothing
 Set rst2 = Nothing
 
Me.strinput1.Value = ""
 
'Me.Requery
[Forms]![FrmRTmainChip]![RaceTimingSF3chip].Requery
[Forms]![FrmRTmainChip]![RaceEntry5sf].Requery
'************************* Code Intentionally Removed *************************

[neelsfer]

thx adezi for quick response. its sorted!