Issues with ADO Delete Record

[jamie]

To anyone who can help:

I have a form, in datasheet view, I wrote code (in ADO), listed below,
to delete a given record. The code is placed in a module, and called
by simmilar forms. For this paticular form, when I initate the code,
the record is deleted and the application crashses (shuts down without
warning). The table is linked.


When I execute the command in debug mode, the following error (Record
Is Deleted, 3167) is recieved, when I press end the application ends.
When I bypass the requerry cmd, and refresh the form mannually, I get
the same error.


Thus far, I have tried to compact/repair both tables; rebuild the form,

rebuild the table. I even deleted all table relationships.


For all my attempts, I have had no luck. - Thanks in advance, to anyone

who can assit


'***** Code Snipit *****
Dim db As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim msgResult, ItemCurrent As Long
Dim lngRelatedID As Variant


'-- Constants --
SQLCodeA = "SELECT CB_MaterialProject.ID " _
& "From CB_MaterialProject " _
& "WHERE CB_MaterialProject.ID = " & intID


'-- Objects --
Set db = CurrentProject.Connection
Set rst = New ADODB.Recordset


DoCmd.close acForm, strFormName, acSaveNo 'Close form to avoid errors


'-- Open Database --
rst.Open SQLCodeA, db, adOpenKeyset, adLockOptimistic
If rst.EOF Then Exit Sub
msgResult = MsgBox("Do you wish to delete Item ?", vbInformation +
vbYesNo)
If msgResult = vbYes Then
rst.Delete (adAffectCurrent)
rst.Update
rst.close
end If


me.requery '!!!!!!!!!!!!!! Code fails here

 

[RoyVidar]

To anyone who can help:

I have a form, in datasheet view, I wrote code (in ADO), listed
below, to delete a given record. The code is placed in a module, and
called by simmilar forms. For this paticular form, when I initate
the code, the record is deleted and the application crashses (shuts
down without warning). The table is linked.


When I execute the command in debug mode, the following error (Record
Is Deleted, 3167) is recieved, when I press end the application ends.
When I bypass the requerry cmd, and refresh the form mannually, I get
the same error.


Thus far, I have tried to compact/repair both tables; rebuild the
form,

rebuild the table. I even deleted all table relationships.


For all my attempts, I have had no luck. - Thanks in advance, to
anyone

who can assit


'***** Code Snipit *****
Dim db As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim msgResult, ItemCurrent As Long
Dim lngRelatedID As Variant


'-- Constants --
SQLCodeA = "SELECT CB_MaterialProject.ID " _
& "From CB_MaterialProject " _
& "WHERE CB_MaterialProject.ID = " & intID


'-- Objects --
Set db = CurrentProject.Connection
Set rst = New ADODB.Recordset


DoCmd.close acForm, strFormName, acSaveNo 'Close form to avoid
errors


'-- Open Database --
rst.Open SQLCodeA, db, adOpenKeyset, adLockOptimistic
If rst.EOF Then Exit Sub
msgResult = MsgBox("Do you wish to delete Item ?", vbInformation +
vbYesNo)
If msgResult = vbYes Then
rst.Delete (adAffectCurrent)
rst.Update
rst.close
end If


me.requery '!!!!!!!!!!!!!! Code fails here

Would it make any difference if you tried just

SQLCodeA = "SELECT CB_MaterialProject.ID " _
& "From CB_MaterialProject " _
& "WHERE CB_MaterialProject.ID = " & intID
CurrentDB.Execute SQLCodeA, dbFailOnError

But, if this code is in a module, then the Me reference wouldn't
work, as that's used in class modules - typically in a forms class
module to reference the current form. In a standard module, as I
understand this is, you'd need something like this

Forms(strFormName).Requery

where strFormName is a variable containing the name of the form.