:lol: (my delte record code adds a record)
VB Code:
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 :(
Re: :lol: (my delte record code adds a record)
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.
Re: :lol: (my delte record code adds a record)
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.
Re: :lol: (my delte record code adds a record)
cid,
A better way to do this would probably be this:
VB Code:
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
Re: :lol: (my delte record code adds a record)
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!
Re: :lol: (my delte record code adds a record)
Thank you Randem, But before I worship you.... What should i dim RecordsAffected as?
It says variable not defined :(
Re: :lol: (my delte record code adds a record)
It should be ADOCn.RecordsAffected
Re: :lol: (my delte record code adds a record)
Quote:
Originally Posted by cid
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.
Re: :lol: (my delte record code adds a record)
Actually, what does recordsaffected do?
Dim ADOCn as ADODB.Connection
There is no "ADOCn.RecordsAffected"?
Re: :lol: (my delte record code adds a record)
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"
Re: :lol: (my delte record code adds a record)
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
Re: :lol: (my delte record code adds a record)
FYI: RecordsAffected is NOT ADO it is DAO ;)
Re: :lol: (my delte record code adds a record)
Quote:
Originally Posted by Static
FYI: RecordsAffected is NOT ADO it is DAO ;)
This is no good, I am using ADO not DAO :(
Re: :lol: (my delte record code adds a record)
cid,
RecordsAffected is defined as a long.
More info ADO .Execute
Re: :lol: (my delte record code adds a record)
:(
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
Re: :lol: (my delte record code adds a record)
Could you post your project? It seems your issues stem from somewhere else.
Re: :lol: (my delte record code adds a record)
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
Re: :lol: (my delte record code adds a record)
part of it (with my comments on the issues) is here:
http://www.vbforums.com/showthread.p...38#post2230538
Quote:
FYI: RecordsAffected is NOT ADO it is DAO
:blush: