Table Append gets error 3420

[JIM]

I picked up the following code from an answer Arvin Meyer gave:
Private Sub Check_Closed_Click()
On Error GoTo Error_Handler

If Me.Check_Closed = True Then
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblBillableWOs", , dbAppendOnly)
With rst
.AddNew
!CustomerName = Me.cboCustomerName
!WONo = Me.txtWoNo
.Update
.Close
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End If
End Sub
It compiles fine but when Check_Closed box is checked error msg 3420
displays and just hangs up my computer and I have to re-boot. The subroutine
is supposed to append to tbleBillableWOs, just two fields. What am I doing
wrong?
TIA

 

[Piet Linden]

"Application-defined or object-defined error"

Sounds like DAO is not registered on that machine. Open a code
window, go to Tools, References, and make sure DAO is checked. And
probably put it ABOVE the reference to ADO if you don't unregister it.

 

[JIM]

Thanks Piet for you suggestion. DAO was referenced but ADO isn't. I did
move DAO as high on reference list as possible. When I test it still gives
the 3420 error then error 0: (no description), then error 20: Resume without
error, then error 91: object variable or with block variable not set. The
record is appended to the file and is correct. Running in debug mode, it
seems to give first error on line rst.Close. It doesn't like it at all and
skips to error routine. Then it's in a continual loop and loops from Set rst
= Nothing to error routine and hangs computer up. Any otheer help would be
appreciated.

 

[Dirk Goldgar]

I picked up the following code from an answer Arvin Meyer gave:
Private Sub Check_Closed_Click()
On Error GoTo Error_Handler

If Me.Check_Closed = True Then
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblBillableWOs", , dbAppendOnly)
With rst
.AddNew
!CustomerName = Me.cboCustomerName
!WONo = Me.txtWoNo
.Update
.Close
End With

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End If
End Sub
It compiles fine but when Check_Closed box is checked error msg 3420
displays and just hangs up my computer and I have to re-boot. The
subroutine
is supposed to append to tbleBillableWOs, just two fields. What am I
doing
wrong?

I see three things wrong with the code. First, you're closing the recordset
twice; second, you have the exit and error-handling code inside the If
block; and third, you need to exit the sub before the Error_Handler. Try
this version and see if it's better:

'----- start of revised code -----
Private Sub Check_Closed_Click()

On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

If Me.Check_Closed = True Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tblBillableWOs", , dbAppendOnly)
With rst
.AddNew
!CustomerName = Me.cboCustomerName
!WONo = Me.txtWoNo
.Update
.Close
End With

End If

Exit_Here:
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

'----- end of revised code -----

 

[JIM]

Thanks Dirk, it's working perfect.

I see three things wrong with the code. First, you're closing the recordset
twice; second, you have the exit and error-handling code inside the If
block; and third, you need to exit the sub before the Error_Handler. Try
this version and see if it's better:

'----- start of revised code -----
Private Sub Check_Closed_Click()

On Error GoTo Error_Handler

Dim db As DAO.Database
Dim rst As DAO.Recordset

If Me.Check_Closed = True Then

Set db = CurrentDb
Set rst = db.OpenRecordset("tblBillableWOs", , dbAppendOnly)
With rst
.AddNew
!CustomerName = Me.cboCustomerName
!WONo = Me.txtWoNo
.Update
.Close
End With

End If

Exit_Here:
Set rst = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Sub

'----- end of revised code -----


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)