programmher
asked on
Access/VB Error: the operation is not supported for this type of object
Below is my code. I keep getting this error: the operation is not supported for this type of object
How can I resolve this error?
Public Function tone_Delete() As Boolean
'Deletes records in tbl_tone with a date less than the current date minus the number of days set as the pararmter in the tbl_mjobs table.
On Error GoTo ErrorHandler
Dim db As Database
Dim Pkg As Recordset
Dim Parms As Recordset
Dim d As Date
Dim pn As Long
pn = 0
d = 0
Set db = CurrentDb
Set Pkg = db.OpenRecordset("tbl_tone
Set Parms = db.OpenRecordset("tbl_mjob
With Parms
.Index = "PrimaryKey"
.Seek "=", "tone_Limit"
If .NoMatch = False Then
pn = !Parm_Value
End If
'MsgBox (pn)
End With
d = Date - pn
With Pkg
.Index = "LOAN_DATE"
.MoveFirst
Do Until !LOAN_DATE > d
.Delete
.MoveNext
Loop
End With
'
tone_Delete = True
Exit Function
ErrorHandler:
MsgBox Err.Number & vbCr & Err.Description, vbCritical, "Error in tone_Delete"
tone_Delete = False
End Function
How can I resolve this error?
Public Function tone_Delete() As Boolean
'Deletes records in tbl_tone with a date less than the current date minus the number of days set as the pararmter in the tbl_mjobs table.
On Error GoTo ErrorHandler
Dim db As Database
Dim Pkg As Recordset
Dim Parms As Recordset
Dim d As Date
Dim pn As Long
pn = 0
d = 0
Set db = CurrentDb
Set Pkg = db.OpenRecordset("tbl_tone
Set Parms = db.OpenRecordset("tbl_mjob
With Parms
.Index = "PrimaryKey"
.Seek "=", "tone_Limit"
If .NoMatch = False Then
pn = !Parm_Value
End If
'MsgBox (pn)
End With
d = Date - pn
With Pkg
.Index = "LOAN_DATE"
.MoveFirst
Do Until !LOAN_DATE > d
.Delete
.MoveNext
Loop
End With
'
tone_Delete = True
Exit Function
ErrorHandler:
MsgBox Err.Number & vbCr & Err.Description, vbCritical, "Error in tone_Delete"
tone_Delete = False
End Function
pdebaets
What line of code is highlighted when you get the error?
ASKER
.Index = "loan_date" is the line that is hilighted
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
instead of
Set Pkg = db.OpenRecordset("tbl_tone
use
Set Pkg = db.OpenRecordset(" Select * from tbl_tone Order by [Loan_date]")
You can then delete the .index line.
And to avoid any possible ambiguity you should declare your recordsets as DAO.Recordset
Set Pkg = db.OpenRecordset("tbl_tone
use
Set Pkg = db.OpenRecordset(" Select * from tbl_tone Order by [Loan_date]")
You can then delete the .index line.
And to avoid any possible ambiguity you should declare your recordsets as DAO.Recordset
ASKER
Thanks. I will try these suggestions. Any idea how this ever worked? My users tell me these errors didn't use to happen.
<<Thanks. I will try these suggestions. Any idea how this ever worked? My users tell me these errors didn't use to happen. >>
A seek can only be used on a locally opened table. If you recently split the DB into a FE/BE, then you need to make an adjustment to use a seek (by opening the remote DB - see code below) or you need to use FindFirst instead gustav said.
But I think your problem is what peter mentioned; DAO vs ADO. Did you recently switch Access versions? If so, you probably have a reference set for ADO, which has a recordset object, but no index method. You need to check the DAO reference and un-check the ADO reference if your not using ADO in code. If you are, then your dims need to have a ADO or DAO prefix as Peter said.
Jim.
'
' Got a key. Does it need to be tested for unique?
'
If (rst1![UniqueKey]) Then
If dbRemote Is Nothing Then
Set wrk = DBEngine.Workspaces(0)
Set dbCurrent = wrk.Databases(0)
Set tdfAttached = dbCurrent.TableDefs(strTab
strPath = tdfAttached.Connect
strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
Set dbRemote = wrk.OpenDatabase(strPath, False, True)
End If
Set rst2 = dbRemote.OpenRecordset(str
rst2.index = "PrimaryKey"
rst2.Seek "=", intlKeyValue
If Not (rst2.NoMatch) Then
rst2.Close
GoTo GetAKey
Else
rst2.Close
End If
End If
A seek can only be used on a locally opened table. If you recently split the DB into a FE/BE, then you need to make an adjustment to use a seek (by opening the remote DB - see code below) or you need to use FindFirst instead gustav said.
But I think your problem is what peter mentioned; DAO vs ADO. Did you recently switch Access versions? If so, you probably have a reference set for ADO, which has a recordset object, but no index method. You need to check the DAO reference and un-check the ADO reference if your not using ADO in code. If you are, then your dims need to have a ADO or DAO prefix as Peter said.
Jim.
'
' Got a key. Does it need to be tested for unique?
'
If (rst1![UniqueKey]) Then
If dbRemote Is Nothing Then
Set wrk = DBEngine.Workspaces(0)
Set dbCurrent = wrk.Databases(0)
Set tdfAttached = dbCurrent.TableDefs(strTab
strPath = tdfAttached.Connect
strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
Set dbRemote = wrk.OpenDatabase(strPath, False, True)
End If
Set rst2 = dbRemote.OpenRecordset(str
rst2.index = "PrimaryKey"
rst2.Seek "=", intlKeyValue
If Not (rst2.NoMatch) Then
rst2.Close
GoTo GetAKey
Else
rst2.Close
End If
End If