asked on

Error in Rs.MoveNext & Duplication of Record

I have a Form which will allow me to copy over records from one table to another using a button. I have successfully created a query which will help me to copy over the records.

I decided to modify the ID into my own unique ID. However, my record keeps duplicating and I keep getting Run Time Error 3071 on my rs.MoveNext.

Where am I going wrong? I have also attached a file of my database.

Private Sub Save_Click()

Dim rs As Recordset
Dim sql As String
Dim lastdate As String
Dim lastyear As Double
Dim lastid As String
Dim currentid As String
Dim a As Integer

sql = "select top 1 * from 2_WIP order by Date_Recorded DESC"
Set rs = CurrentDb.OpenRecordset(sql)

If rs.EOF Then
    lastdate = "01/1/1990"
    lastyear = 1990
    lastid = 0
Else
    lastdate = DateValue(rs!Date_Recorded)
    lastyear = Year(rs!Date_Recorded)
    lastid = Val(Mid(rs!RecordID, 6))
End If

sql = "select * from 1_Daily_Entry where datevalue(Date_Recorded)>= '" & lastdate & "' and year(Date_Recorded) >= '" & lastyear & "';"
Set rs = CurrentDb.OpenRecordset(sql)

Do While Not rs.EOF
    If DateValue(rs!Date_Recorded) = lastdate Then
            a = Year(rs!Date_Recorded)
                If (a = lastyear) Then
                    lastid = lastid + 1
                Else
                    lastid = 1
                    lastyear = a
                End If
                    currentid = a & "_" & lastid
                    If rs!S1_Good_Count = 0 Then
                        DoCmd.Close
                    ElseIf rs!S2_Good_Count = 0 Then
                        DoCmd.Close
                    ElseIf rs!S3_Good_Count = 0 Then
                        DoCmd.Close
                    Else
                        sql = "insert into 2_WIP (RecordID, Date_Recorded, Product, S1_Good_Count, S2_Good_Count, S3_Good_Count) values ('" & currentid & "', '" & rs!Date_Recorded & "', '" & rs!Product & "', '" & rs!S1_Good_Count & "', '" & rs!S2_Good_Count & "', '" & rs!S3_Good_Count & "')"
                        CurrentDb.Execute (sql)
                        DoCmd.Close
                    End If
        
    Else
        a = Year(rs!Date_Recorded)
        If (a = lastyear) Then
            lastid = lastid + 1
        Else
            lastid = 1
            lastyear = a
        End If
            currentid = a & "_" & lastid
            If rs!S1_Good_Count = 0 Then
                DoCmd.Close
            ElseIf rs!S2_Good_Count = 0 Then
                DoCmd.Close
    
             ElseIf rs!S3_Good_Count = 0 Then
                DoCmd.Close
             Else
                sql = "insert into 2_WIP (RecordID, Date_Recorded, Product, S1_Good_Count, S2_Good_Count, S3_Good_Count) values ('" & currentid & "', '" & rs!Date_Recorded & "', '" & rs!Product & "', '" & rs!S1_Good_Count & "', '" & rs!S2_Good_Count & "', '" & rs!S3_Good_Count & "')"
                CurrentDb.Execute (sql)
                DoCmd.Close
            End If
        End If
        
rs.MoveNext
Loop

End Sub

Open in new window

Database1_dplt.accdb

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Natasha

ASKER

It did worked. However, it keeps duplicating the last record over and over again.

Ryan Chong

what do you mean by "last record"? can you give an example here?

Natasha

ASKER

Okay umm when I cleared my table 2_WIP for testing, everything seems fine and it copied over the records until the new RecordID 2016_16.
Since it is the new 'max', the code keep duplicating the records for 2016_16 and created a new RecordID which is 2016_17, 2016_18 so on and so forth..... This happens when you keep clicking the Save button

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Natasha

ASKER

Same issue happened.

The latest RecordID was orginally 2016_16. As you can see Record 2016_17, 2016_18 and 2016_19 duplicates the Records for 2016_15 and 2016_16

Ryan Chong

Ok, saw the issues now...

so... what should we do to fix this issue?

Do you mean in table: 2_WIP, the last record should always be 2016_16 ,etc ? what's the logic for checking?

Natasha

ASKER

Not should always be 2016_16. There should not be any duplicate of records. I think that I am missing out in something to avoid duplication.

Ryan Chong

how to determine the duplication of records in your case above? can you visualize it if possible?

Natasha

ASKER

I was hoping if you can assist me with that.

I have some idea but I am not sure if this is going to work. Since for 1_Daily_Entry the RecordID is an auto number, I will also copy it over to 2_WIP as RecordID. Then, this unique ID I created will be under column ID. Not sure about the coding yet. But will it avoid duplication?

Picture below is just a draft plan for you to visualize.

Natasha

ASKER

okay nevermind I already make it work.

Ryan Chong

I think technically since your records are being copied from 1_Daily_Entry to 2_WIP, we need to make a Left join in your select statement before we insert the record, which should able to avoid the insertion of duplicate records.

see if attached is what you wanted?

Private Sub Save_Click()

Dim rs0 As Recordset
Dim rs As Recordset
Dim sql As String
Dim lastdate As String
Dim lastyear As Double
Dim lastid As String
Dim currentid As String
Dim a As Integer

sql = "select top 1 * from 2_WIP order by Date_Recorded DESC"
Set rs0 = CurrentDb.OpenRecordset(sql)

If rs0.EOF Then
    lastdate = CDate("01/1/1990")
    lastyear = 1990
    lastid = 0
Else
    lastdate = rs0!Date_Recorded
    lastyear = Year(rs0!Date_Recorded)
    lastid = Val(Mid(rs0!RecordID, 6))
End If

rs0.Close
Set rs0 = Nothing

sql = "select a.* from 1_Daily_Entry as a left join 2_WIP b on a.RecordID = b.RecordID where b.RecordID is null and a.Date_Recorded >= #" & Format(lastdate, "mm/dd/yyyy") & "# and year(a.Date_Recorded) >= " & lastyear & ";"
Set rs = CurrentDb.OpenRecordset(sql)

Do While Not rs.EOF
    If rs!Date_Recorded = lastdate Then
            a = Year(rs!Date_Recorded)
                If (a = lastyear) Then
                    lastid = lastid + 1
                Else
                    lastid = 1
                    lastyear = a
                End If
                    currentid = a & "_" & lastid
                    If rs!S1_Good_Count = 0 Then
                        DoCmd.Close
                    ElseIf rs!S2_Good_Count = 0 Then
                        DoCmd.Close
                    ElseIf rs!S3_Good_Count = 0 Then
                        DoCmd.Close
                    Else
                        sql = "insert into 2_WIP (ID, RecordID, Date_Recorded, Product, S1_Good_Count, S2_Good_Count, S3_Good_Count) values ('" & currentid & "', " & rs!RecordID & ", '" & rs!Date_Recorded & "', '" & rs!Product & "', '" & rs!S1_Good_Count & "', '" & rs!S2_Good_Count & "', '" & rs!S3_Good_Count & "')"
                        CurrentDb.Execute (sql)
                        DoCmd.Close
                    End If
        
    Else
        a = Year(rs!Date_Recorded)
        If (a = lastyear) Then
            lastid = lastid + 1
        Else
            lastid = 1
            lastyear = a
        End If
            currentid = a & "_" & lastid
            If rs!S1_Good_Count = 0 Then
                DoCmd.Close
            ElseIf rs!S2_Good_Count = 0 Then
                DoCmd.Close
    
             ElseIf rs!S3_Good_Count = 0 Then
                DoCmd.Close
             Else
                sql = "insert into 2_WIP (ID, RecordID, Date_Recorded, Product, S1_Good_Count, S2_Good_Count, S3_Good_Count) values ('" & currentid & "', " & rs!RecordID & ", '" & rs!Date_Recorded & "', '" & rs!Product & "', '" & rs!S1_Good_Count & "', '" & rs!S2_Good_Count & "', '" & rs!S3_Good_Count & "')"
                CurrentDb.Execute (sql)
                DoCmd.Close
            End If
        End If
        
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub

Open in new window

Database1_dplt_c.accdb

Ryan Chong

okay nevermind I already make it work

Ok, interested to see how you resolved it, probably you can also consider to use Left Join as suggested in my last comment.
see which one best suit your requirements cheers

Natasha

ASKER

Alright I will also try your suggestion on the Left Join. Thank you for your help so far! :-)

Error in Rs.MoveNext &amp; Duplication of Record

Error in Rs.MoveNext & Duplication of Record