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!
5 4286
Bump....anyone have any ideas?
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: -
Dim Counter as Integer
-
Counter = 0
-
-
Do Until rst.EOF
-
rst.MoveNext
-
Counter = Counter + 1
-
If Counter >50 then
-
' Code to copy the files would go here
-
' Will probably be an AddNew and Edit depending on what you are working with
-
' If you need me to lay this syntax out for you let me know
-
End If
-
Loop
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: -
Dim Counter as Integer
-
Counter = 0
-
-
Do Until rst.EOF
-
rst.MoveNext
-
Counter = Counter + 1
-
If Counter >50 then
-
' Code to copy the files would go here
-
' Will probably be an AddNew and Edit depending on what you are working with
-
' If you need me to lay this syntax out for you let me know
-
End If
-
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.
ADezii 8,834
Recognized Expert Expert
Bump....anyone have any ideas?
I made a Generic Template that should work quite well for you: - Dim MyDB As DAO.Database
-
Dim MyRS As DAO.Recordset
-
Dim MyRS_2 As DAO.Recordset
-
-
Set MyDB = CurrentDb()
-
-
Set MyRS = MyDB.OpenRecordset("<Table/Query/SQL Statement>", dbOpenDynaset)
-
Set MyRS_2 = MyDB.OpenRecordset("<Table/Query/SQL Statement>", dbOpenDynaset)
-
-
'Necessary for accurate Record Count
-
MyRS.MoveLast: MyRS.MoveFirst
-
-
If MyRS.RecordCount > 50 Then
-
MyRS.Move 50 'Move to 51st Record
-
Do While Not MyRS.EOF
-
MyRS_2.AddNew 'Must Add prior to Deletion
-
MyRS_2![Field1] = MyRS![Field1]
-
MyRS_2![Field2] = MyRS![Field2]
-
MyRS_2![Field3] = MyRS![Field3]
-
'...
-
MyRS_2.Update
-
MyRS.Delete 'Now you can Delete the Record
-
MyRS.MoveNext 'Advance to next Record
-
Loop
-
Else
-
'do nothing, <= 50 Records
-
End If
-
-
MyRS.Close
-
MyRS_2.Close
-
Set MyRS = Nothing
-
Set MyRS_2 = Nothing
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 ...)?
Sign in to post your reply or Sign up for a free account.
Similar topics |
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
|
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).
|
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...
|
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...
|
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...
| |
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...
|
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.
|
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)
|
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...
|
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,...
|
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...
| |
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...
|
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...
|
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();...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |