By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Advanced DAO recordset operations: Move records to another table

P: 3
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
Share this Question
Share on Google+
5 Replies


P: 3
Bump....anyone have any ideas?
Aug 1 '08 #2

P: 9
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

P: 3
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
Expert 5K+
P: 8,597
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
Expert Mod 15k+
P: 31,186
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

Post your reply

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