473,698 Members | 2,611 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, 281 views)
Nov 23 '10 #1
15 1486
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
12589
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
1304
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
5222
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
1412
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
1042
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
8757
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
1834
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
1878
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
36604
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
8683
marktang
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8610
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
9170
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...
1
8902
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
8873
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5862
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4372
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4623
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2007
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.