I Can't Delete!

R

roy_ware

In the form, I display a purchase order. I click the delete button, and it
won't work. Here's the code:

Dim strPONumber As String
Dim strSQL As String
Dim db As Database
Dim rec As Recordset
Dim qdfAction As QueryDef

strPONumber = PONumber.Value 'PONumber is the primary key of the
table

strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = " _
& strPONumber & ";"

Set db = CurrentDb()
Set rec = db.OpenRecordset("Purchase Orders")
Set qdfAction = db.OpenRecordset("Purchase Orders", strSQL)

qdfAction.Execute dbFailOnError

rec.Close
Set rec = Nothing

Me.Requery


What am I doing wrong??????
 
D

Douglas J Steele

How you're trying to instantiate the QueryDef is incorrect. In actual fact,
there's no reason to even use a QueryDef object, since you'll never be able
to use that query again! As well, the Recordset is unnecessary in this
context (although perhaps you have some other reason for it)

Dim strPONumber As String
Dim strSQL As String

strPONumber = PONumber.Value

strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = " _
& strPONumber

' The semi-colon at the end isn't actually required.
' See Roger's note if PONumber is text, as opposed to numeric

CurrentDb.Execute strSQL, dbFailOnError

Me.Requery
 
D

Duane Hookom

Why would you want to even open a recordset?
I would actually use Roger's SQL with:

Dim strPONumber As String
Dim strSQL As String
Dim db As Database
Set db = CurrentDb()
strPONumber = PONumber.Value 'PONumber is the primary key of the
table
strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = '" _
& strPONumber & "';"
db.Execute strSQL, dbFailOnError
Set db = Nothing

--
Duane Hookom
MS Access MVP
--

Roger Carlson said:
Since PONumber is a text value, the SQL should look like this:
strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = '" _
& strPONumber & "';"

Notice the two apostrophies I added and where.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


roy_ware said:
In the form, I display a purchase order. I click the delete button, and it
won't work. Here's the code:

Dim strPONumber As String
Dim strSQL As String
Dim db As Database
Dim rec As Recordset
Dim qdfAction As QueryDef

strPONumber = PONumber.Value 'PONumber is the primary key of the
table

strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = " _
& strPONumber & ";"

Set db = CurrentDb()
Set rec = db.OpenRecordset("Purchase Orders")
Set qdfAction = db.OpenRecordset("Purchase Orders", strSQL)

qdfAction.Execute dbFailOnError

rec.Close
Set rec = Nothing

Me.Requery


What am I doing wrong??????
Click to expand...
Click to expand...
 
R

roy_ware

I put the apostrophes in and upon execution, I get this error:

"Data Type Conversion Error."

I put the strSQL in Quick Watch and the actual value (apostrophes included)
is:

"DELETE * FROM [Purchase Orders] WHERE [Purchase Orders].PONumber = 'Dummy3';"

This looks correct to me......

Using breakpoint and stepping, the line causing the error is:

Set qdfAction = db.OpenRecordset("Purchase Orders", strSQL)

Fascinating. On a whim, I removed the table name from this line and got the
same error.


Roger Carlson said:
Since PONumber is a text value, the SQL should look like this:
strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = '" _
& strPONumber & "';"

Notice the two apostrophies I added and where.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


roy_ware said:
In the form, I display a purchase order. I click the delete button, and it
won't work. Here's the code:

Dim strPONumber As String
Dim strSQL As String
Dim db As Database
Dim rec As Recordset
Dim qdfAction As QueryDef

strPONumber = PONumber.Value 'PONumber is the primary key of the
table

strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = " _
& strPONumber & ";"

Set db = CurrentDb()
Set rec = db.OpenRecordset("Purchase Orders")
Set qdfAction = db.OpenRecordset("Purchase Orders", strSQL)

qdfAction.Execute dbFailOnError

rec.Close
Set rec = Nothing

Me.Requery


What am I doing wrong??????
Click to expand...
Click to expand...
 
R

roy_ware

That was the ticket...I guess I was trying to over-tink the plumbing. Thank
you!


Douglas J Steele said:
How you're trying to instantiate the QueryDef is incorrect. In actual fact,
there's no reason to even use a QueryDef object, since you'll never be able
to use that query again! As well, the Recordset is unnecessary in this
context (although perhaps you have some other reason for it)

Dim strPONumber As String
Dim strSQL As String

strPONumber = PONumber.Value

strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = " _
& strPONumber

' The semi-colon at the end isn't actually required.
' See Roger's note if PONumber is text, as opposed to numeric

CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


roy_ware said:
In the form, I display a purchase order. I click the delete button, and it
won't work. Here's the code:

Dim strPONumber As String
Dim strSQL As String
Dim db As Database
Dim rec As Recordset
Dim qdfAction As QueryDef

strPONumber = PONumber.Value 'PONumber is the primary key of the
table

strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = " _
& strPONumber & ";"

Set db = CurrentDb()
Set rec = db.OpenRecordset("Purchase Orders")
Set qdfAction = db.OpenRecordset("Purchase Orders", strSQL)

qdfAction.Execute dbFailOnError

rec.Close
Set rec = Nothing

Me.Requery


What am I doing wrong??????
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Action queries (DELETE, UPDATE, INSERT INTO) do not create recordsets. Only
SELECT queries do.

Fortunately, you've already solved this problem...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



roy_ware said:
I put the apostrophes in and upon execution, I get this error:

"Data Type Conversion Error."

I put the strSQL in Quick Watch and the actual value (apostrophes
included)
is:

"DELETE * FROM [Purchase Orders] WHERE [Purchase Orders].PONumber =
'Dummy3';"

This looks correct to me......

Using breakpoint and stepping, the line causing the error is:

Set qdfAction = db.OpenRecordset("Purchase Orders", strSQL)

Fascinating. On a whim, I removed the table name from this line and got
the
same error.


Roger Carlson said:
Since PONumber is a text value, the SQL should look like this:
strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = '" _
& strPONumber & "';"

Notice the two apostrophies I added and where.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


roy_ware said:
In the form, I display a purchase order. I click the delete button,
and it
won't work. Here's the code:

Dim strPONumber As String
Dim strSQL As String
Dim db As Database
Dim rec As Recordset
Dim qdfAction As QueryDef

strPONumber = PONumber.Value 'PONumber is the primary key of
the
table

strSQL = "DELETE * FROM [Purchase Orders] " _
& "WHERE [Purchase Orders].PONumber = " _
& strPONumber & ";"

Set db = CurrentDb()
Set rec = db.OpenRecordset("Purchase Orders")
Set qdfAction = db.OpenRecordset("Purchase Orders", strSQL)

qdfAction.Execute dbFailOnError

rec.Close
Set rec = Nothing

Me.Requery


What am I doing wrong??????
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inconsistant behavior in forms vba module 2
Mismatch error when opening SQL string 4
How to 1
Cannot find a record in table after SQL insert error 0
Syntax Error in a SQL string 3
Error Exporting Memo Fileds to Excle 2
Remove Identical words 0
delete sub and stay on same parent record 4

Top