Object Required error using Access with VBA backend

[mrsgwen]

Can someone please look at this code and tell me why I am getting the "Object Required" error:

Private Sub cmdAddRec_Click()
On Error GoTo Err_cmdAddRec_Click
Dim rstServices As New ADODB.Recordset
Dim fld As ADODB.Field
Dim strField As String


rstServices.Open "dbo_tbl_Service", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If IsNull(Me.ServiceID.Value) Then
'this is new record
'rstServices.AddNew

Else
'to stay on the record that was just inserted for editing
rstServices.Find ("ServiceID=" + Str$(Me.ServiceID.Value))
End If

rstServices!AddressID = Me.AddressID
rstServices!ServiceStatus = Me.cboServiceStatus
rstServices!NbrCarts = Me.NbrCarts
rstServices!ServiceFee = Me.ServiceFee
rstServices!ExtraFee = Me.ExtraFee
rstServices!BaseFee = Me.BaseFee
rstServices!HU_Route = Me.HU_Route
rstServices!HU_Account = Me.HU_Account
rstServices!HU_Class = Me.cboHU_Class
rstServices!BusinessName = Me.BusinessName
rstServices!Remarks = Me.Remarks
rstServices!UserID = UserName()
rstServices!Timestamp = Now()

rstServices.Update



Dim MyServices As DAO.Recordset
Dim MyFld As ADODB.Field
Dim MyStrField As String
Dim sqlstmt As String

sqlstmt = "SELECT AddressID, ServiceStatus, NbrCarts, ServiceFee, ExtraFee, BaseFee, HU_Route, " & _
" HU_Account, HU_Class, BusinessName, Remarks FROM dbo_tbl_Service WHERE AddressID = rstServices!AddressID"

rstMyServices.Open "dbo_tbl_Service", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

THIS IS WHERE THE CODE BREAKS AND GIVES ME THE ERROR!!!!

Dim intRec As Integer
Dim x As Integer

intRec = rstMyServices.recordCount

For x = 0 To intRec
If x > intRec Then
Exit For
End If


Forms![Services]![Service subform]![ServiceStatus].Value = Me.cboServiceStatus
Forms![Services]![Service subform]![NbrCarts].Value = Me.NbrCarts
Forms![Services]![Service subform]![ServiceFee].Value = Me.ServiceFee
Forms![Services]![Service subform]![ExtraFee].Value = Me.ExtraFee
Forms![Services]![Service subform]![BaseFee].Value = Me.BaseFee
Forms![Services]![Service subform]![HU_Route].Value = Me.HU_Route
Forms![Services]![Service subform]![HU_Account].Value = Me.HU_Account
Forms![Services]![Service subform]![HU_Class].Value = Me.HU_Class
Forms![Services]![Service subform]![BusinessName].Value = Me.BusinessName
Forms![Services]![Service subform]![Remarks].Value = Me.Remarks

Next x


rstServices.Close
Set rstServices = Nothing


cboServiceStatus = ""
NbrCarts = ""
ServiceFee = ""
ExtraFee = ""
BaseFee = ""
HU_Route = ""
HU_Account = ""
HU_Class = ""
BusinessName = ""
Remarks = ""

Exit_cmdAddRec_Click:
MsgBox "Record Successfully Saved!"
Exit Sub
Err_cmdAddRec_Click:
MsgBox Err.Description
Resume Exit_cmdAddRec_Click
End Sub

 

[boblarson]

I'm guessing that this:

Dim MyServices As DAO.Recordset


needs to be changed to this:

Dim MyServices As New ADODB.Recordset

 

[Galaxiom]

You code would be a lot tidier using With blocks.

With rstServices
    !AddressID = Me.AddressID
    !ServiceStatus = Me.cboServiceStatus
    !NbrCarts = Me.NbrCarts
   etc
    .Update
End With

Also note Value is the default property of a control so does not need to be explicitly referenced.