Trouble with Inserting into tables

[James]

Hi everyone,

I posted my question earlier with help in inserting
values form text box into the tables and an internet user
provide me a code for inserting. The problem is that
ACCESS is giving me an error with this code.

The code is:
Private Sub cmdSetCompany_Click()

' cmdSetCompany is a command
' button on your form

Dim ws As Workspace // Error I'm receiving
Dim rstComp1 As Recordset
Dim rstComp2 As Recordset

On Error GoTo errHandler

' Use Workspace object for transactions
Set ws = DBEngine.Workspaces(0)
Set rstComp1 = CurrentDb.OpenRecordset("Comp1Table",
dbOpenDynaset)
Set rstComp2 = CurrentDb.OpenRecordset("Comp2Table",
dbOpenDynaset)

On Error GoTo errTrans

ws.BeginTrans

With rstComp1
.AddNew
!CompanyName = Forms!myform!CompanyName
!CompanyAddress = Forms!myform!CompanyAddress
.Update
End With

With rstComp2
.AddNew
!CompanyName = Forms!myform!CompanyName
.Update
End With

ws.CommitTrans

MsgBox "Company added to 2 tables"

EndIt:
Set ws = Nothing
Set rstComp1 = Nothing
Set rstComp2 = Nothing

Exit Sub

errTrans:
MsgBox "Error cmdSetCompany_Click (" & Err.Number & "): "
& Err.Description,
vbCritical
ws.Rollback
Resume EndIt

errHandler:
MsgBox "Error cmdSetCompany_Click (" & Err.Number & "): "
& Err.Description,
vbCritical
Resume EndIt

End Sub


Any help is appreciate

James

 

[Dan Artuso]

Hi,
Check to make sure you have a reference to the DAO library.
With any code module open go to Tools->References and check off
DAO. You probably also have a reference to ADO already in there.
You can remove that OR fully qualify your variables:
Dim ws As DAO.Workspace
Dim rstComp1 As DAO.Recordset
Dim rstComp2 As DAO.Recordset

If you still get an error, please post the exact text of the error message.