Deleting a Record From Table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BobV - 17 Dec 2005 18:51 GMT

Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the following code
but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Any help will be greatly appreciated.

Thanks,
BobV

Reply to this Message

Tom Lake - 17 Dec 2005 19:04 GMT

> Group:
>
[quoted text clipped - 3 lines]
>
> db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Try this:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is Null"

Strings that haven't had a value assigned yet are Null rather than ""

Tom Lake

Reply to this Message

BobV - 17 Dec 2005 19:17 GMT

Tom:

I tried your suggestion, but it still doesn't work -- the record is not
deleted. Any other suggestions?

Thanks,
BobV

>> Group:
>>
[quoted text clipped - 11 lines]
>
> Tom Lake

Reply to this Message

Duane Hookom - 17 Dec 2005 19:37 GMT

Do you have referential integrity set on? Is there a record in a related
child table?
What happens if you paste the SQL into a blank query and attempt to run it?

Signature

Duane Hookom
MS Access MVP

> Tom:
>
[quoted text clipped - 19 lines]
>>
>> Tom Lake

Reply to this Message

Alex Dybenko - 17 Dec 2005 19:41 GMT

Also try to run:
db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] =
""",dbFailOnError

in this case you will get a runtime error if data can not be deleted for
some reason

Signature

Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

> Tom:
>
[quoted text clipped - 19 lines]
>>
>> Tom Lake

Reply to this Message

BobV - 17 Dec 2005 19:57 GMT

Alex:

Thanks, but that didn't work either. If I change the DELETE statement to the
following, the code works:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = 'ABC
Company'"

But where the Name field is null, the code that has been suggested does not
work.

Here is my subroutine:

'CANCEL BUTTON
Private Sub CancelButton_Click()
Dim db As DAO.Database
On Error Resume Next
If OriginalCompanyName = "" Then
Set db = CurrentDb
db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is
Null"
End If
End Sub

Any further suggestions?

Thanks,
BobV

> Also try to run:
> db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] =
[quoted text clipped - 26 lines]
>>>
>>> Tom Lake

Reply to this Message

Duane Hookom - 17 Dec 2005 21:39 GMT

What happens when you paste the sql into a new query and attempt to run it?
Also, do you expect the value of OrginalCompanyName to be a zero length
string or could it be Null (not the same thing). Have you tried to step
through the code? How about adding a Msgbox after the If OriginalCompanyName
= ""...?

Signature

Duane Hookom
MS Access MVP

> Alex:
>
[quoted text clipped - 55 lines]
>>>>
>>>> Tom Lake

Reply to this Message

John Vinson - 18 Dec 2005 03:37 GMT

> Alex:
>
[quoted text clipped - 3 lines]
> db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = 'ABC
> Company'"

The DELETE statement requires you delete *SOMETHING* - a fieldname or (more
commonly) the * pseudofield. Try

db.Execute "DELETE * FROM [MasterData] WHERE [MasterData].[Name] =
'ABC Company'"

or (more specifically for your example)

db.Execute "DELETE * FROM [MasterData] WHERE [MasterData].[Name] IS NULL"

John W. Vinson/MVP

Reply to this Message