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

What error are you getting when you run the code?

--

Ken Snell
<MS ACCESS MVP>


"Nick Mleczko" <(E-Mail Removed)> wrote in message
news:49b75bac-a806-4348-854d-(E-Mail Removed)...
> 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,

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

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.

--

Ken Snell
<MS ACCESS MVP>


"Nick Mleczko" <(E-Mail Removed)> wrote in message
news:89da6a3d-fe2c-4a6c-bb53-(E-Mail Removed)...
> 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