Recordsets / move next

[Jimmyshoo]

Good evening
I am hoping someone can offer me some help. What I am trying to do sounds very simple and I did not envisage having the problems I have encountered
To simplify what I am trying to do is move records from a temp table to a permanent table in code
So let's say table one has two fields ( first name and last name) and three records . I want to move these three records to the permanent table using Dao.i open both recordsets and add new / update / move next but what happens is the input table receives three records but they are all duplicates so for example Jimmy shoo appears three times not Jimmy shoo, Simon Stanley and Margaret Smith.
I have been trying to resolve for 6 hours now without success.. has anyone any sample code that could help with this problem please?

 

[Dreamweaver]

Can you give us more details on the duplicates I.E. Do they have different ID's and have you tried using apend queries?

 

[theDBguy]

Hi. Sounds like you're not looping through the records correctly. If you could post the code you're using, we could check it out. However, for moving records between tables, you should be able to simply use an APPEND query. You don't have to use Recordsets all the time.

 

[arnelgp]

dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset

Set rsSource=Currentdb.OpenRecordset("table1", dbOpensnapshot)
Set rsTarget=Currentdb.OpenRecordset("table2", dbOpenDynaset)
With rsSource
    If Not (.BOF And .EOF) Then
      .MoveFirst
      While Not .EOF
         rsTarget.AddNew
         rsTarget![First Name] = ![First Name]
         rsTarget![Last name] = ![Last Name]
         rsTarget.Update
         
         .MoveNext
      Wend
   End If
End With
rsTarget.Close
rsSource.Close
Set rsTarget=Nothing
Set rsSource=Nothing

or you could just use Query and much faster:

Currentdb.Execute "Update targetTable Right Join sourceTable ON " & _
      "targetTable.[First Name]=sourceTable.[First Name] And " & _
      "targetTable.[Last Name]=sourceTable.[First Name] " & _
      "Set targetTable.[First Name]=sourceTable.[First Name], " & _
      "targetTable.[Last Name]=sourceTable.[Last Name];"

 

[Jimmyshoo]

Hello

Wow , that works. I just adjusted or copied and pasted your code over mine. Now i just need to understand where mine was going wrong as it was very similar.
Thanks everyone else who replied too. And I will send official thanks in a moment. I thought it would be days b4 I got a response . That's really good of you all!

 

[arnelgp]

you're welcome!

 

[theDBguy]

Hello

Wow , that works. I just adjusted or copied and pasted your code over mine. Now i just need to understand where mine was going wrong as it was very similar.
Thanks everyone else who replied too. And I will send official thanks in a moment. I thought it would be days b4 I got a response . That's really good of you all!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

MOVE is different from COPY. What the code does is to copy the rows from one table and append them to another. It does not remove them from the original table.

Also, queries are much more efficient for this type of action. You would run an append query to copy from tblA and append to tblB and then run a delete query with the same criteria to delete the selected records from tblA.