473,748 Members | 2,223 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

547 Contributor
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
Attached Images
File Type: jpg racetimingT.jpg (61.9 KB, 211 views)
File Type: jpg raceentry table1.jpg (42.5 KB, 282 views)
Nov 23 '10 #1
15 1495
ADezii
8,834 Recognized Expert Expert
The following Code will Copy 4 Fields from the RaceTimingSF Sub-Form to the RaceEntry Table, the rest I'll leave for you.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstTiming As DAO.Recordset
  3. Dim rstEntry As DAO.Recordset
  4.  
  5. Set MyDB = CurrentDb
  6. Set rstEntry = MyDB.OpenRecordset("RaceEntry", dbOpenDynaset)
  7.  
  8. 'Capture the Current State of the RaceTimingSF Sub-Form
  9. Set rstTiming = Forms!RaceSetupF!RaceTimingSF.Form.RecordsetClone
  10.     rstTiming.MoveFirst
  11.  
  12. With rstTiming
  13.   Do While Not .EOF
  14.     rstEntry.AddNew
  15.       rstEntry![Racedate] = ![Racedate]
  16.       rstEntry![RaceNo] = ![RaceNumber]
  17.       rstEntry![FinishTime] = ![RaceFinishTime]
  18.       'The [RaceName] Field in RaceEntry is a LONG, so you must look up the corresponding Value
  19.       rstEntry![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & ![RaceName] & "'")
  20.     rstEntry.Update
  21.       .MoveNext
  22.   Loop
  23. End With
  24.  
  25. rstEntry.close
  26. rstTiming.close
  27. Set rstEntry = Nothing
  28. Set rstTiming = Nothing
Nov 23 '10 #2
neelsfer
547 Contributor
thx Adezii
where do i put the code? in a field's Afterupdate event ?
thx
Nov 24 '10 #3
neelsfer
547 Contributor
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)
Nov 24 '10 #4
ADezii
8,834 Recognized Expert Expert
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2. Dim MyDB As DAO.Database
  3. Dim rstEntry As DAO.Recordset
  4.  
  5. Set MyDB = CurrentDb
  6. Set rstEntry = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
  7.  
  8. With rstEntry
  9.   .AddNew
  10.     ![Racedate] = Me.Parent![RacingDate]
  11.     ![RaceNo] = Me![RaceNumber]
  12.  
  13.     'Lap Numbers must be in sync with Field Names in RaceEntry
  14.     .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
  15.  
  16.     'The [RaceName] Field in RaceEntry is a LONG, so you must look up the corresponding Value
  17.     ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
  18.   .update
  19. End With
  20.  
  21. rstEntry.close
  22. Set rstEntry = Nothing
  23. End Sub
Nov 24 '10 #5
neelsfer
547 Contributor
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!!
Nov 24 '10 #6
ADezii
8,834 Recognized Expert Expert
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.
Nov 24 '10 #7
neelsfer
547 Contributor
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?
Nov 27 '10 #8
ADezii
8,834 Recognized Expert Expert
Allow a Maximum of 8 Laps for a given combination of Race Name and Number?
Nov 27 '10 #9
neelsfer
547 Contributor
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.
Nov 27 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

18
12591
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 about whether we should allow polymorphism. Our system is not embedded and does not need to be as real-time as, say, a pacemaker. But it does involve updating displays based on radar input. So a need for something close to real-time is desired...
1
1306
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 a 'lastlogin' field so the tracking is done in a date range. So, for the month of January I would record lastlogin dates between January 1 and January 31. My question is this...on the last day of the month (example being Janauary)at 23:59:59PM...
7
5231
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 look something like this: OrderID (Autonumber) SeqNo City 1 1 London 2 1 Madrid 3 ...
1
1417
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 table. is it possible to develop a query between the number and the autonumber? my current sql code:
0
1045
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 based off of a different table. I would like to be able to enter a machine serial number on the claim form and have a checkbox that would be checked if the machine serial number exists in the Warranty Registration table, and unchecked if it does...
3
8759
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 table is contains unic data. But other table contains repeated data. 1st table is Address list of the customer 2nd table is Distribution list for every month. So some time the distribution list contains the name id many times according...
1
1838
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 work once using INTO instead of FROM.
2
1880
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 listbox on the main form that contains a list of medicines and it is set as multiselect "none". I am trying to set up the listbox so that when you click a medicine it will populate the PatientMedicine table. The user should be able to select multiple...
2
36618
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 characters. When I pull them from a custom program, dts wizard, or just copy them to the clipboard, it truncates some of them. I'm guessing because it sees a line break or carriage return so it stops there. The result is that the extracted value...
0
266
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 controller. And the HMI which has to be fast enough (SPEED, not HARD-REAL-TIME)
0
8822
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9528
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9359
jinu1996
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9310
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8235
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3298
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2774
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.