cannot delete record using db.execute

[Nick Mleczko]

Hi,

I'm trying to delete a record programatically, this is the code;

Dim db As DAO.Database
'Dim QD As QueryDef
'Dim where As Variant
Dim strSQL As String

Set db = CurrentDb()

Set db = CurrentDb

strSQL = "DELETE * FROM tblQuoteDescription where QuoteID =" &
intQuote & " and CustomerID =" & Forms!frmMain.CustomerID & ";"
db.Execute strSQL

db.Execute strSQL, dbFailOnError

Debug.Print db.RecordsAffected
Debug.Print strSQL

The records exists but the rowsaffected is returned as 0
the SQL statement is as follows;
DELETE * FROM tblQuoteDescription where QuoteID = 40100 and CustomerID
= 9;

ANy help would be greatly appreciated

N

 

[Ken Snell \(MVP\)]

What error are you getting when you run the code?

 

[Nick Mleczko]

Ken,

As far as I know there was no error

I got around it by using the standard delete record macro that you can
generate from the button wizard, however I've noticed that you cannot
reuse the primary key again. Once used and deleted it will not be
reused... pity...

N

 

[Ken Snell \(MVP\)]

I assume that you're using an Autonumber field as the primary key? Once a
value is "assigned" to a record (even if the record is deleted before it's
saved), that value is gone and cannot be used again.