Advanced DAO recordset operations: Move records to another table

[trixb]

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!

[trixb]

Bump....anyone have any ideas?

[jghouse]

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

[trixb]

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]

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]

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 ...)?