Object Not Supported FOr This Type Of Object

[Guest]

Dim Td1 As TableDef
Dim QD1 As QueryDef
Dim NewRule As Long
Dim ListCriteria As Variant
Dim RS3 As Recordset
Set db = CurrentDb()
Set Td1 = db.TableDefs!tblListItems
Set RS3 = Td1.OpenRecordset

Please..can some one help,

This piece of code is meant to delete an item from a table called
tblListItems dependant on the value of ListOfItems (ListBox)

It used to work...Now I keep on getting the above error on RS3.FindFirst
ListCriteria

WHat am I doing wrong??


With Me.ListOfItems
For DeleteLoop = 0 To Me.ListOfItems.ListCount - 1
DeleteItem = Me.ListOfItems.Column(0, DeleteLoop)
ListCriteria = "RuleLink like " & Me.RuleIdentifier.Value
RS3.MoveFirst
RS3.FindFirst ListCriteria
DeletePoint:
If Me.ListOfItems.Selected(DeleteLoop) Then
If RS3!RuleValue = DeleteItem Then
RS3.Delete
GoTo ExitDelete
Else
RS3.MoveNext
GoTo DeletePoint
End If
End If
Next DeleteLoop
End With

 

[chris.nebinger]

The first thing is I wonder if RS3 is being declared as an ADO
recordset? Does making this change help?


Dim RS3 as DAO.Recordset


Also, there shouldn't be a need to worry about the table def. Just:

Dim dbs as DAO.Database
dim RS3 as DAO.Recordset

Set dbs = CurrentDB
Set RS3 = dbs.OpenRecordset("Select * from tblListItems")



Or, if I'm reading your code right:


Dim itm As Variant
For Each itm In Me.ListOfItems.ItemsSelected
CurrentDB.Execute "Delete from tblListItems where RuleLink = "
& Me.RuleIdentifier.Value & " AND RuleValue = '" &
ListOfItems.ItemData(itm) & "'"
Next i


If RuleLink is a text field, then:


CurrentDB.Execute "Delete from tblListItems where RuleLink = '" &
Me.RuleIdentifier.Value & "' AND RuleValue = '" &
ListOfItems.ItemData(itm) & "'"

Chris Nebinger

 

[Dirk Goldgar]

The first thing is I wonder if RS3 is being declared as an ADO
recordset? Does making this change help?


Dim RS3 as DAO.Recordset

Sean's code is opening a table-type recordset, which doesn't support the
FindFirst method. He could get past that problem by specifying
dbOpenDynaset for the optional Type argument when he opens the
recordset, but I think your code is much better..