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 "RaceTimingSF"
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 "Racefinishtimes" 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
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
15 1470
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
thx Adezii
where do i put the code? in a field's Afterupdate event ?
thx
may i pls add a sting in the tail?
if i want to copy ie. the Racefininishtime 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)
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
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!!
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.
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?
Allow a Maximum of 8 Laps for a given combination of Race Name and Number?
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.
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
thx mr Adezii, will try and let you know
any ideas on "error trapping" with the above code please!
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
thx its working as "usual" now!!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ken |
last post by:
Hi.
Can anyone refer me to any articles about the compatibility between
c++ polymorphism and real-time programming?
I'm currently on a real-time c++ project, and we're having a
discussion...
|
by: sans_spam |
last post by:
I'm writing a classic ASP application that records all logging of user
logins on our support site. The logging is a rolling window of how many
people have logged in for a given month, i.e. tracked...
|
by: GAVO. |
last post by:
Hello every one
I have a database with a form called "frmOrders" on that for I need to
create a sequential number for each city apart from the original autonumber.
So the table "tblorders" would...
|
by: tkal2k |
last post by:
hi all,
i have a quick question. i have a table where we use autonumbers to
catergorize it, and another table where we use those autonumbers not as
the primary id, but still have them in the...
|
by: socasteel21 via AccessMonster.com |
last post by:
I have a database that is used extensively to log warranty claims. There are
2 forms that this problem concerns, a warranty claim form and a warranty
registration form. Each of these forms is...
|
by: imtmub |
last post by:
Hi,
I have two tables in that one table contain name id, name, categary etc.,
and other table contains name id, name but not categary. now i want to copy the categary column another table.
One...
|
by: jmarr02s |
last post by:
I am trying to "Copy all columns from another table" here is the SQL
code I am using in MS Access:
CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id 1000);
I think I got it to...
|
by: datasec23 |
last post by:
Hi,
I currently have two tables PatientInfo and PatientMedicine. I have created a form from PatientInfo and a subform in datasheet view for PatientMedicine on the PatientInfo form. I have a...
|
by: TaeMike |
last post by:
Hello, I have some varchar(2000) fields in my tables, and they have a lot of "weird" characters in them including line break and carriage returns, etc. When I do a select, I see the entire string of...
|
by: Kurt Mueller |
last post by:
Am 08.10.2008 um 06:59 schrieb Hendrik van Rooyen:
OK, this is gives an impression of SPEED.
In your application the REAL-TIME requirements
are isolated and implemented in the custom...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |