473,618 Members | 3,005 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Advanced DAO recordset operations: Move records to another table

3 New Member
Hello all,

Here is what I need to do and need help with:
I have a table that is feeding a chart in a report in Access. If this table has more than 50 records, the graph gets messy, and this is where I need help with my solution to this. If the table has more than 50 records, I want to take the next 50 records after the 50th and move these to another table (which I need to create), and this will continue until there are no more records.

I have successfully setup a DAO recordset and can get the count of records, but need to know how I can interact with the recordset to accomplish what I'm trying to do (move records to another table based on their position in the recordset).

Any help will be greatly appreciated.
Thanks!
Jul 29 '08 #1
5 4286
trixb
3 New Member
Bump....anyone have any ideas?
Aug 1 '08 #2
jghouse
9 New Member
Bump....anyone have any ideas?
Let's see if I can help. Assuming you have already done your record count to determine whether or not you will need to do this (probably be an If statement before this portion based on your RecordCount) you want to try the following:
Expand|Select|Wrap|Line Numbers
  1. Dim Counter as Integer
  2. Counter = 0
  3.  
  4. Do Until rst.EOF
  5.   rst.MoveNext  
  6.   Counter = Counter + 1
  7.   If Counter >50 then
  8.      ' Code to copy the files would go here 
  9.      ' Will probably be an AddNew and Edit depending on what you are working with
  10.      ' If you need me to lay this syntax out for you let me know
  11.   End If  
  12. Loop
Aug 1 '08 #3
trixb
3 New Member
Let's see if I can help. Assuming you have already done your record count to determine whether or not you will need to do this (probably be an If statement before this portion based on your RecordCount) you want to try the following:
Expand|Select|Wrap|Line Numbers
  1. Dim Counter as Integer
  2. Counter = 0
  3.  
  4. Do Until rst.EOF
  5.   rst.MoveNext  
  6.   Counter = Counter + 1
  7.   If Counter >50 then
  8.      ' Code to copy the files would go here 
  9.      ' Will probably be an AddNew and Edit depending on what you are working with
  10.      ' If you need me to lay this syntax out for you let me know
  11.   End If  
  12. Loop
Thank you so much for your reply. I had played around with my logic and actually came up with the exact structure as you proposed (rst.MoveNext until the counter reaches 50).
What I need help with, is the actual syntax after the counter has reached 50. How do I start moving these 50 records at a time to another table?

Again, your response is appreciated and I look forward to hearing what else you think.

Thanks,
TB.
Aug 1 '08 #4
ADezii
8,834 Recognized Expert Expert
Bump....anyone have any ideas?
I made a Generic Template that should work quite well for you:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim MyRS As DAO.Recordset
  3. Dim MyRS_2 As DAO.Recordset
  4.  
  5. Set MyDB = CurrentDb()
  6.  
  7. Set MyRS = MyDB.OpenRecordset("<Table/Query/SQL Statement>", dbOpenDynaset)
  8. Set MyRS_2 = MyDB.OpenRecordset("<Table/Query/SQL Statement>", dbOpenDynaset)
  9.  
  10. 'Necessary for accurate Record Count
  11. MyRS.MoveLast: MyRS.MoveFirst
  12.  
  13. If MyRS.RecordCount > 50 Then
  14.   MyRS.Move 50      'Move to 51st Record
  15.   Do While Not MyRS.EOF
  16.     MyRS_2.AddNew       'Must Add prior to Deletion
  17.       MyRS_2![Field1] = MyRS![Field1]
  18.       MyRS_2![Field2] = MyRS![Field2]
  19.       MyRS_2![Field3] = MyRS![Field3]
  20.       '...
  21.     MyRS_2.Update
  22.     MyRS.Delete         'Now you can Delete the Record
  23.     MyRS.MoveNext       'Advance to next Record
  24.   Loop
  25. Else
  26.   'do nothing, <= 50 Records
  27. End If
  28.  
  29. MyRS.Close
  30. MyRS_2.Close
  31. Set MyRS = Nothing
  32. Set MyRS_2 = Nothing
Aug 2 '08 #5
NeoPa
32,566 Recognized Expert Moderator MVP
Instead of moving records around between tables, have you considered running the charts off a query that limits the number of records returned to fifty (SELECT TOP 50 ...)?
Aug 5 '08 #6

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

Similar topics

8
4732
by: Dima Protchenko | last post by:
Hi, guys. Please help if you know something about this. Error: ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed. line: if not rs.EOF then (from the code below) I have an SP on SQL2k that returns a recordset after a few data manipulations. The code for the SP is too long to attach here, but to
3
8353
by: Tlm | last post by:
Hello All, I have a form (FrmA) with a subform (SubFrmB) embedded in it. SubFrmB also has a subform embedded in it (SubFrmC) The form's recordsource is based on a table (TblA). SubFrmB's recordsource is also based on a table (TblB). SubFrmC's recordsource is also based on a table (TblC). There is a one-to-many relationship between TblA (one) and TblB (many).
9
4344
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user for any changes of ratings on that day. The resulting data table grows by approx 600 records per...
4
3159
by: Dalan | last post by:
I presume that using an open recordset method is the preferred method of accomplishing what I'm trying to do. Of course, if there are other options that would work, feel free to share them. I need to provide a means of scanning one field for all records and to trigger a message upon opening the form for alerting a user that maintenance activities need to be promptly performed. The recordset does not necessarily have to be used for any...
25
10212
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
22
2861
by: Gerry Abbott | last post by:
Hi all, I having some confusing effects with recordsets in a recent project. I created several recordsets, each set with the same number of records, and related with an index value. I create a table and add the index value and a value/s from each recordset in turn, into a temporary table, which I used to create a report. I created this with DAO objects, and it worked fine. I use iteration, and the ..movenext command to get the next...
13
3459
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the query: QryICTMassDistribution3) , I then use a form and the code below to create a new record in the corrispondence table to show what corrispondence has been sent to various companies.
7
2275
by: tdr | last post by:
I need to compare table 1 to table 2 and if the row/recordset in table 1 is different from table 2, write the entire row/recordset from table 1 to table 3. I can read an entire row/recordset from table 1 into a recordset using the following strSQL_in = "select * from table1" ( and i can refer to the columns as needed)
2
15116
kcdoell
by: kcdoell | last post by:
Hello I have a code where I want to delete the records that are found in my DAO recordset. I took a stab at this for the first time and got it to work but it is only delete one record at a time. If I execute the code again my record count will be minus one and then it will delete another single record etc etc until there are no records to delete. How could I create a loop statement so that I don't have to keep on executing the code??? Below...
0
8153
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
8653
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
7126
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
6101
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
5552
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
4065
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
4150
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2587
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
1
1760
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.