473,321 Members | 1,669 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Append a recordset to multiple fields in another table on request

547 512MB
I do race timing.
I capture race numbers in a subform, a sequential lapnumber is created, and the racenumber and finishtime are appended in realtime to a specific lap number fields in another table. Ie if you finish lap 1, then the finishtime gets appended to 2nd table and to the lap 1 field, if you finish lap 2, then it goes to the lap 2 field etc
It works great, until you capture a wrong number for an athlete. The net result is that both the source and 2nd table have to be corrected then.
See example.


Expand|Select|Wrap|Line Numbers
  1.  Dim MyDB As DAO.Database
  2.    Dim rstEntry As DAO.Recordset
  3.   Set MyDB = CurrentDb
  4.  Set rstEntry = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
  5.  
  6.  With rstEntry
  7.      .AddNew
  8.     ![Racedate] = Me.Parent![RacingDate]
  9.        ![RaceNo] = Me![RaceNumber]
  10.   ![LapNo] = Me![LapNo]
  11.   ![Entries] = Me![FinishSeq]
  12.        ![lap10] = Me![RaceFinishTime]
  13.        ![racetimingId] = Me![racetimingId]
  14.       'Lap Numbers must be in sync with Field Names in RaceEntry
  15.       .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
  16.       ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
  17.     .update
  18.  
  19.  End With
  20.  
  21.    rstEntry.close
  22.  Set rstEntry = Nothing
My request - i would prefer to have only the main table (RacetimingT) where the racenumbers and finishtimes are added initially, to store the data. All this data filtered in a query, will then be appended (Batch updating) to the "RaceEntry2" table into the different lap fields, when i want to create a live report.

My plan B - can i not perhaps use the "Update method" in conjunction with the "AddNew method" to replace the original data if there was an error in the capturing. This will avoid the "double fixing" of errors in the original and 2nd table. This will save me a lot of trouble if possible
How do i do this?


Attached Images
File Type: jpg recordset1.jpg (117.6 KB, 506 views)
File Type: jpg recordset2.jpg (53.1 KB, 439 views)
Jun 27 '11 #1
6 2776
NeoPa
32,556 Expert Mod 16PB
I feel sure we've covered this before, but it looks like you need to read about Database Normalisation and Table structures.

The approach you are describing is complicated because it is not normalised. In a normalised db the data is stored only in one place. The most appropriate place. Whenever this data needs to be approached from a different angle it can be presented using SQL based queries in whatever form the database is designed to support. Your question asks for help to handle a problem that is created simply by a non-normalised approach, so my suggestion is that you redisign your db in the proscribed way and make the problem disappear that way.

Data certainly can be updated using queries, but experience teaches us that this approach leads to increasingly onerous problems that eventually become too much, and then the solution (redesigning the db from scratch) is so much more of a problem than it is if you catch it early on.
Jun 28 '11 #2
neelsfer
547 512MB
My problem is that Racetiming is so complex and there are so many factors involved here. The Racenumber capturing during the race for example takes place independantly from the "adding of personal details", as both processes happens independantly and are only matched at the reporting stage for numerous reasons..
Everything works 100% except when we make capturing errors.

Maybe option B is a better bet for me as i then just need to update changes to capturing errors in the second table, using the recordset method.

My problem - i have captured "racenumber" 232 and then realized that it should have been 332 instead:
The problem now is that 232 has been appended to the "RaceEntry2" table already when pressing enter.
I would now like to change this 232 later in the row it was originally captured, and then change it to 332, press enter again, and the same record is updated in the same row in the "RaceEntry2" table. I have a field called "RacetimeId" in both tables.
Please assist with above code to enable me to edit a record as described above.
Jun 28 '11 #3
NeoPa
32,556 Expert Mod 16PB
If it is clearly understood by all involved that I absolutely do not recommend this approach then I would be happy to help with coding where I can.

Unfortunately, as yet, I don't understand how the code you want help with matches what you say you are trying to achieve. Nor even very clearly what you are trying to achieve. I don't feel I can be of much help in the current circumstances, but if you were to explain the situation more clearly using only references to items that you'd already defined, I may get an understanding sufficient to be able to assist. As I say, I'm happy to help you with some logic and syntax, I simply must understand first what it is you are trying to achieve, and more importantly, I must make sure that when this is done no-one gets the idea that this approach is recommended by either me or any expert at Bytes, as we feel we have a responsibility to our users (not just members but the many more people who browse through our threads every day).

As you've been with us a fair time, and what you are describing is admittedly quite complicated, I will consider looking at your database if you choose to attach a copy. Remember to follow the guidelines set out in Attach Database (or other work) though.
Jun 28 '11 #4
neelsfer
547 512MB
Thx for helping me.I have added the main "problematic" part of the application only. i have explained the problem on the screen, where i am struggling.

I have a field called RacetimeId that is appended together with the other data, if one needs to lookup the same row/record in the RacetimeEntry2 table that must be updated..Both are in number format.
regards
Neels
Attached Files
File Type: zip Racetimingv6.4Amenddata2003.zip (2.27 MB, 61 views)
Jun 29 '11 #5
ADezii
8,834 Expert 8TB
I am probably more familiar with this Code than anyone, and I am still honestly confused as to your exact request. I see no easy Method of preventing an erroneous entry and subsequent Append, short of verifying the existence of a Race Number, if practical. What you can do is to provide a sort of Verification Dialog prior to initiating this process. I hope that I am not too far off base on this one.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEntry As DAO.Recordset
  3. Dim strMsg As String
  4. Dim intResponse As Integer
  5.  
  6. strMsg = "Are you sure that Race Number [" & Me![RaceNumber] & "] is correct?" & vbCrLf & vbCrLf & _
  7.          "Once you commit by clicking Yes, Data will be Appended to the RaceEntry2 " & _
  8.          "Table, and this process cannot easily be reversed." & vbCrLf & vbCrLf & _
  9.          "Click Yes to continue or No to Cancel the Append operation"
  10.  
  11. 'Confirm prior to executing     
  12. intResponse = MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton1, "Append Confirmation")
  13.  
  14. If intResponse = vbNo Then Exit Sub
  15.  
  16. Set MyDB = CurrentDb
  17. Set rstEntry = MyDB.OpenRecordset("RaceEntry2", dbOpenDynaset, dbAppendOnly)
  18.  
  19. With rstEntry
  20.   .AddNew
  21.     ![Racedate] = Me.Parent![RacingDate]
  22.     ![RaceNo] = Me![RaceNumber]
  23.     ![LapNo] = Me![LapNo]
  24.     ![Entries] = Me![FinishSeq]
  25.     ![lap10] = Me![RaceFinishTime]
  26.     ![racetimingId] = Me![racetimingId]
  27.  
  28.      'Lap Numbers must be in sync with Field Names in RaceEntry
  29.     .Fields("Lap" & CStr(Me![LapNo])) = Me![RaceFinishTime]
  30.     ![RaceName] = DLookup("[RaceDetailID]", "RaceDetail", "[RaceName] = '" & Me.Parent![RaceName] & "'")
  31.   .Update
  32. End With
  33.  
  34. rstEntry.Close
  35. Set rstEntry = Nothing
Jul 13 '11 #6
neelsfer
547 512MB
Thx Adezi.
Let me put it this way. Finger errors do happen.

Its very simple if you only add a record to 1 table and delete/change it, when its wrong.
In this instance it gets copied in realtime to a second table into different fields, and both have to be updated in real time when an error happens.(In a race everything happens very fast, so i want to avoid messages prompting me for yes or no.)

In short i have 2 options:
- either i fix the errors in realtime in both tables (RacetimingT and RaceEntry2) as it happens (the RacetimingID appears in both tables),
or i only use the original "RacetimingT" table where the data gets captured during the race and errors can easily be fixed, (i prefer this method), and when viewing reports, all the data from this current Racedate, are appended at once to the "RaceEntry2" table fields.
I hope this make sense.
Jul 13 '11 #7

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

Similar topics

2
by: Colm O'Hagan | last post by:
Hi there, I having a problem with a database I'm setting up, I would be delighted if someone out there could help. The database I'm setting up is a task register datebase, it will be used to...
4
by: Martin Lacoste | last post by:
(Access 2000) Two issues: Within a query, I need to return a field name as data (see eg. below). I need to search within 80 fields (same criteria) - is there a way to avoid 80 separate...
3
by: alex_peri | last post by:
Hello All, I am having problems with sorting a recordset by fields in Access. I have a table with three columns called ID, SNo and Time and would like to sort the records by Time. I would like to...
4
by: tarafinlay | last post by:
Hi all, I am new to access and am finding it a bit unintuitive having worked with SQL server in the past... And I am in a bit of a hurry because my employer wants me to crank something out which...
3
by: mkjets | last post by:
I have worked for hours on trying to find a solution and have not figured it out. I am working in Access 2003. I need to create a query that takes values from 1 table and displays them in...
3
by: colleen1980 | last post by:
Hi: Can any one please help me when user select 2 dates from DDLDate1 10/09/2006 and DDLDate2 10/12/06 and the name and it close the form. I need to create multiple records in the another table on...
0
by: Howie | last post by:
Hi all. Hope you can help. I occasionally need to enter info directly into a table. However, when I do, I often need to enter the same value into a large number of fields. It would be nice if...
3
by: wvmbark | last post by:
First time poster... I just found this forum and it appears there's plenty of people here that could make short work of problem that's been driving me absolutely bonkers for months. Every day we...
1
by: chaurous | last post by:
hi guys, i have a problem of building a function and from the result i should append the results to another table. function required: my first idea is query but it is not working. how should...
3
by: Bigdaddrock | last post by:
I have a form that allows for input of new Customer Billing address fields (B1, B2, B3) as well as fields for the Shipping Address (S1, S2, S3). Sometimes these are the exact same! I have text...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.