:lol: (my delte record code adds a record)

[cid]

On Error Resume Next 'Err handling
ADOCn.BeginTrans 
'setup your rs here
ADOCn.Execute "DELETE FROM Permits WHERE [Tract_ID] = " & Combo1.SelText
If Err.Number <> 0 Then
    ADOCn.Rollback
Else
    ADOCn.Execute "DELETE FROM Permitsw WHERE [Tract_ID] = " & Combo1.SelText
    If Err.Number <> 0 Then
        ADOCn.Rollback
    Else
        ADOCn.CommitTrans
    End If
End If
rsvalues.Update
rsvalues.moveprevious
On Error GoTo 0

So basically I want it to delete the record that is tied to the field "Tract_ID" And a user selects which record out of the field from a combobox....

BUT ITS ADDING A BLANK NEW FIELD

[si_the_geek]

There is no need for "rsvalues.Update", as you have not modifed rsvalues at all. The fact that this is adding a new record probably means that somewhere earlier you ran "rsvalues.Addnew", but didn't run an update with it (you should find this and fix it!).

For this code however, you should run an "rsvalues.ReQuery" to get the recordset up to date with your changes.

I don't understand why you have "rsvalues.moveprevious" there either.

[randem]

cid,

Also if a delete does not work there is nothing to rollback... Transactions are worthless in this case. At least for the first delete.

[randem]

cid,

A better way to do this would probably be this:

On Error Resume Next 'Err handling
ADOCn.BeginTrans 
'setup your rs here
ADOCn.Execute "DELETE FROM Permits WHERE [Tract_ID] = " & Combo1.SelText, RecordsAffected
if RecordsAffected > 0 then ADOCn.Execute "DELETE FROM Permitsw WHERE [Tract_ID] = " & Combo1.SelText, RecordsAffected
 
If RecordsAffected = 0 then
   ADOCn.RollbackTrans
else
   ADOCn.CommitTrans
End If

[si_the_geek]

I think it should be kept - the way it is at the moment, if the second delete fails then the first will be rolled back.

Rolling back the first seem to be worthless, but it cancels the transaction.


edit: nice alternative method you just posted!

[cid]

Thank you Randem, But before I worship you.... What should i dim RecordsAffected as?

It says variable not defined

[si_the_geek]

It should be ADOCn.RecordsAffected

[SeanK]

Thank you Randem, But before I worship you.... What should i dim RecordsAffected as?

It says variable not defined

What are you using it for? The answer to that will tell you what is should be created as.

[cid]

Actually, what does recordsaffected do?

Dim ADOCn as ADODB.Connection

There is no "ADOCn.RecordsAffected"?

[si_the_geek]

It is a property of the connection object, which tells you how many records were affected by the last Execute (in this case: how many records were deleted). It does not need to be defined.

Just change "RecordsAffected" in randems code to "ADOCn.RecordsAffected"

[cid]

Thank you for the help... although my ADOCn. doesnt recognize a command in there called RecordsAffected...

here is the commands before and after where it is supposed to be:

ADOCn.Provider

ADOCn.RollbackTrans

[Static]

FYI: RecordsAffected is NOT ADO it is DAO

[cid]

FYI: RecordsAffected is NOT ADO it is DAO

This is no good, I am using ADO not DAO

[randem]

cid,

RecordsAffected is defined as a long.

More info ADO .Execute

[cid]

I cant add new records, and i cant delete them... what is going on?

When I goto delete a record all it does is just sit there... but when i hit rsvalues.moveprevious it says EOF ( record has been deleted)... but when i open the database back up, the record is still there....

Its like it isnt refreshing properly but i have my requery outside of my loop, and i defined records affected as long...

any ideas?

thanks randem for the link

[randem]

Could you post your project? It seems your issues stem from somewhere else.

[cid]

acutally no, the project is about 3 megs... with the dlls, forms, database, and ocx's

pm me your email and i will be more than happy to send it to you

[si_the_geek]

part of it (with my comments on the issues) is here:
http://www.vbforums.com/showthread.p...38#post2230538

FYI: RecordsAffected is NOT ADO it is DAO