Why doesn't this SQL work?

[David Habercom]

I need to delete a record from a table, and I can't see why this SQL
statement doesn't see the record:

strSQL = "DELETE * FROM DonorTbl WHERE DonANDI = " & Qt(Me.NewANDI) & ""
DoCmd.RunSQL strSQL

Private Function Qt(s)
'Simplifies use of quotes in SQL statement.
Qt = Chr(34) & s & Chr(34)
End Function

The strSQL line translates to:
DELETE * FROM DonorTbl WHERE DonANDI = "Y33333"
and I know the record exists, but the Access warning tells me I am about to
delete 0 records!

What am I missing?

Thanks in advance.

 

[Joan Wild]

Before the DoCmd.RunSQL line put
Debug.Print strSQL
and check in the immediate window to see what the SQL string looks like.

By the way, you can use
CurrentDb.Execute strSQL, dbFailOnError
instead of DoCmd.RunSQL

 

[David Habercom]

Thanks, Joan, but the translation I showed:
DELETE * FROM DonorTbl WHERE DonANDI = "Y33333"
has been verified in the Immediate window. I also tried CurrentDb.Execute,
but had the same result. I'm still mystified. Could I be looking in the
wrong place?

I appreciate your advice. Thanks.

 

[David Habercom]

I figured it out! My strSQL was looking for DonANDI in the current record.
This happened because my Me.NewANDI was bound to the the table. By making
Me.NewANDI an Unbound control and then going to a different record, the
strSQL worked. Whew!

Thanks everybody.

 

[Douglas J Steele]

Just a comment.

Had you used

CurrentDb().Execute strSQL, dbFailOnError

rather than

DoCmd.RunSQL strSQL

you would have got a trappable error raised that might have given you a clue
as to why the delete was failing. (You would also have avoided receiving the
"You are about to delete 0 records" message)