Error in Rs.MoveNext & Duplication of Record

Question

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?

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 *error keeps highlighting at this line*
Loop

End Sub

Answer 1

Found a solution for those who have a similar issue as me.

Link for Answer to Question