Handling error

[Guest]

Is error handling code only used 1 time? I ran thru debug on the following
code it I get an error for dup record the second time. What I was trying to
do was handle all duplicate records be added.

Private Function BuildActualEstimate(MyNewYear)
Dim MyDB As DAO.Database
Dim MySet1 As DAO.Recordset
Dim MyProjectName As String
Dim MyUserName As String
On Error GoTo nextrec:
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet1 = MyDB.OpenRecordset("QryActiveActual", DB_OPEN_DYNASET)
Set MySet2 = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With MySet1
.MoveFirst
Do While Not .EOF
MyProjectName = !PROJECTNAME
MyUserName = !UserName
With MySet2
.AddNew
!PROJECTNAME = MyProjectName
!UserName = MyUserName
!ActYear = MyNewYear
.Update
End With
nextrec:
.MoveNext
Loop
End With
Set MyDB = Nothing
Set MySet1 = Nothing
End Function

 

[Guest]

I would try and write it that way

Private Function BuildActualEstimate(MyNewYear)
On Error GoTo BuildActualEstimate_Err
Dim MyDB As DAO.Database
Dim MySet1 As DAO.Recordset
Dim MyProjectName As String
Dim MyUserName As String

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet1 = MyDB.OpenRecordset("QryActiveActual", DB_OPEN_DYNASET)
Set MySet2 = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
If Not MySet1.EOF Then
MySet1.MoveFirst
While Not MySet1.EOF
MySet2.AddNew
MySet2!ProjectName = MySet1!ProjectName
MySet2!UserName = MySet1!UserName
MySet2!ActYear = MyNewYear
MySet2.Update
MySet1.MoveNext
Wend
End If
Set MyDB = Nothing
Set MySet1 = Nothing

BuildActualEstimate_Exit:
Exit Function
BuildActualEstimate_Err:
If Err = 111 Then ' check the error number for duplicate and replace the
111
Resume Next
Else
GoTo BuildActualEstimate_Exit
End If
End Function

 

[Guest]

Ofer

It worked great, thanks for the advice.

John