How to copy sequential number and time to another table in real time

[neelsfer]

What is easiest way to update a 2nd table used for reports in realtime as fields are filled with data


The current table is called RacetimingT and the fields are:
Racedate = date field
racename = txtfield
Racenumber = number field
Racefinishtime = GeneralDate field
LapNo - number field
mainform = "RaceSeupF"
Subform that contains the RacetimingT is called "RaceTiming SF"

In another Table called "RaceEntry" , i have a
RaceName -- numberfield
RaceDate -- date field
RaceNo ---- numberfield
lap1;lap2;lap3, lap4;lap5;lap6; lap7;lap8 = GeneralDate field

Both tables contains similar Racedates and Racenumbers/no, as the RaceEntry table is where the athlete gets entered for a race on a specific date, and and in the Racetiming tbl the athletes Racefinishtime is added.
These are then linked in a query etc.
I need to copy the "Racefinishtime s" for a specific RaceDate and Racenumbers(of athletes taking part on this day) for Lapno=1-8, into the Lap1-8 fields of the RaceEntry tbl based on the same Racedate and the same racenumber in previous table.
pls help
see screen pic

[ADezii]

The following Code will Copy 4 Fields from the RaceTimingSF Sub-Form to the RaceEntry Table, the rest I'll leave for you.

Dim MyDB As DAO.Database
Dim rstTiming As DAO.Recordset
Dim rstEntry As DAO.Recordset

Set MyDB = CurrentDb
Set rstEntry = MyDB.OpenRecordset("RaceEntry", dbOpenDynaset)

'Capture the Current State of the RaceTimingSF Sub-Form
Set rstTiming = Forms!RaceSetupF!RaceTimingSF.Form.RecordsetClone
    rstTiming.MoveFirst

With rstTiming
  Do While Not .EOF
    rstEntry.AddNew
      rstEntry![Racedate] = ![Racedate]
      rstEntry![RaceNo] = ![RaceNumber]
      rstEntry![FinishTime] = ![RaceFinishTime]
      'The [RaceName] Field in RaceEntry is a LONG, so you must look up the corresponding Value
      rstEntry![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & ![RaceName] & "'")
    rstEntry.Update
      .MoveNext
  Loop
End With

rstEntry.close
rstTiming.close
Set rstEntry = Nothing
Set rstTiming = Nothing

[neelsfer]

thx Adezii
where do i put the code? in a field's Afterupdate event ?
thx

[neelsfer]

may i pls add a sting in the tail?
if i want to copy ie. the Racefininishtim e of Racenumber = 1 and Lapno =1 from the RacetimingT on a specific date, into the Lap1 field of the RaceEntry tbl. What then? This must happen from lapno 1 to 8 for both tables. ( the Racename in RacetimingT is txt and in the RaceEntry the same field is a number field, that why i suggested a date field lookup/link)

[ADezii]

The following Code, placed in the AfterUpdate() Event of the Sub-Form RaceTimingSF, will populate the RaceEntry Table with the required Data and necessary Conversions, after a Record is Saved/Updated in the Sub-Form:

Private Sub Form_AfterUpdate()
Dim MyDB As DAO.Database
Dim rstEntry As DAO.Recordset
  
Set MyDB = CurrentDb
Set rstEntry = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
  
With rstEntry
  .AddNew
    ![Racedate] = Me.Parent![RacingDate]
    ![RaceNo] = Me![RaceNumber]
    
    'Lap Numbers must be in sync with Field Names in RaceEntry
    .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
    
    'The [RaceName] Field in RaceEntry is a LONG, so you must look up the corresponding Value
    ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
  .update
End With

rstEntry.close
Set rstEntry = Nothing
End Sub

[neelsfer]

Adezii, what can i say, when one is a genius, you are a genius.
I am making a small adjustment and will create a RaceEntry2 table to store this data, like you had in the code. I will then do my timing calculations to determine the actual laptimes from there. This is exactly what i had in mind.
Have a great evening!!

[ADezii]

You too, neelsfer. Just one small thing to keep in mind is that if you make any kind of change to an existing Record in that Sub-Form, those Values will also be written to the RaceEntry Table.

[neelsfer]

Using the above code, I setup the RaceEntry2 table to only accept 8 laps of racetimedata. When we get to 9 by error and it currently crashes, i would like it to say
“ you are only allowed to capture 8 laps per athlete on this system”.
It currently crashes and go to the VB screen, when you get to 9 because it cant append the data.
It should then take you back to the racenumber field where you started in this form, before you entered more than 8 laps
any suggestions?

[ADezii]

Allow a Maximum of 8 Laps for a given combination of Race Name and Number?

[neelsfer]

yes you are correct;
ie a racenumber should not have more than 8 laps linked to it per racename/racedate but can be less.
I have added a field to the Racedetail table ("racedetail subform") for the max laps per specific distance called TotalLaps and its a numberfield. One can now add the max laps per distance there in the subform tab = "Racesetup and Start" if it may help to limit the laps per racedistance.
will have change the applications name soon;; hehehe == "Adezii Racetime"
thx for the trouble.

[neelsfer]

here are screenpics

[ADezii]

Try a little modified Code on for size (Code Lines 4 thru 13):

Private Sub Form_AfterUpdate()
Dim MyDB As DAO.Database
Dim rstEntry As DAO.Recordset
Dim bytNumOfRecsForRace As Byte

'Allow a MAXIMUM of 8 Records in RaceEntry for any given combination of Race Date/Number
bytNumOfRecsForRace = DCount("*", "RaceEntry", "[RaceDate] = #" & Me.Parent![RacingDate] & _
                             "# AND [RaceNumber] = " & Me![RaceNumber])

If bytNumOfRecsForRace >= 8 Then        'Already at MAX for Race Date/Number
  MsgBox "Nice try Dude, you're already at MAX - Record cannot be written", vbExclamation, "Maximum Laps"
    Exit Sub        'Don't fall through to Write Record
End If
  
Set MyDB = CurrentDb
Set rstEntry = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
  
With rstEntry
  .AddNew
    ![Racedate] = Me.Parent![RacingDate]
    ![RaceNo] = Me![RaceNumber]
  
    'Lap Numbers must be in sync with Field Names in RaceEntry
    .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
  
    'The [RaceName] Field in RaceEntry is a LONG, so you must look up the corresponding Value
    ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
  .Update
End With
  
rstEntry.Close
Set rstEntry = Nothing
End Sub

[neelsfer]

thx mr Adezii, will try and let you know

[neelsfer]

any ideas on "error trapping" with the above code please!

[ADezii]

For Basic Error Trapping, see Code Lines# 2, 39 to 44. You can Test the Error Trap in Code Line# 17.

Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate
Dim MyDB As DAO.Database
Dim rstEntry As DAO.Recordset
Dim bytNumOfRecsForRace As Byte
  
'Allow a MAXIMUM of 8 Records in RaceEntry for any given combination of Race Date/Number
bytNumOfRecsForRace = DCount("*", "RaceEntry", "[RaceDate] = #" & Me.Parent![RacingDate] & _
                             "# AND [RaceNumber] = " & Me![RaceNumber])
  
If bytNumOfRecsForRace >= 8 Then        'Already at MAX for Race Date/Number
  MsgBox "Nice try Dude, you're already at MAX - Record cannot be written", vbExclamation, "Maximum Laps"
    Exit Sub        'Don't fall through to Write Record
End If

'****************************************************************
'Err.Raise 16        'Remove Initial Comment to Test Error Trap
'****************************************************************
  
Set MyDB = CurrentDb
Set rstEntry = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
  
With rstEntry
  .AddNew
    ![Racedate] = Me.Parent![RacingDate]
    ![RaceNo] = Me![RaceNumber]
  
    'Lap Numbers must be in sync with Field Names in RaceEntry
    .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
  
    'The [RaceName] Field in RaceEntry is a LONG, so you must look up the corresponding Value
    ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
  .Update
End With
  
rstEntry.Close
Set rstEntry = Nothing

Exit_Form_AfterUpdate:
  Exit Sub

Err_Form_AfterUpdate:
  MsgBox Err.Description, vbExclamation, "Error in Form_AfterUpdate()"
    Resume Exit_Form_AfterUpdate
End Sub