Still Deletes Records after I Cancel

[xcrx]

When I delete records in my database it brings up the are you sure dialog like it does for everyone how ever when I click cancel or no it still deletes my records. It fills all the fields in with #Deleted. Up until now this hasn't been a problem because I haven't been deleting things unless they needed to be but now I am adding a function that checks a form's subform for records and cancels the delete if records exist. I am not sure how to go about fixing this problem.

I am using Access 2007 as a frontend and Mysql 5.1 as a backend.

Any help would really be appreciated.

 

[missinglinq]

Are you deleting using delete from the menu/shortcut menu or do you have a custom delete button with popup message? If the latter, what code are you using?

Linq ;0)>

 

[xcrx]

I am simply using the delete key. I then run a BeforeDelComfirm Sub with this code.

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
If DLookup("[Job_Num]", "[Work_Details_tbl]", "[Job_num] =" & Chr(39) & JobNumber & Chr(39)) = "" Then
MsgBox "Order Deleted"
Else
Cancel = True
MsgBox "This work order stil contains parts"
End If
End Sub

The delete key behaves the same with or without the code. I am thinking that is has something to do with mysql.

 

[missinglinq]

First off, with "" you think you're checking to see if your DLookup() returns nothing, which is incorrect. When no match is found, using DLookup(), it returns a Null, not a Zero Length String.

Also, the syntax in your Where clause doesn't look right. Correct syntax for [Job_num] would be

if Numeric

"[ Job_num] = " & JobNumber

if Text

"[ Job_num] = '" & JobNumber & "'"

So, your If...Then statement should be

if Numeric

If IsNull(DLookup("[Job_Num]", "[Work_Details_tbl]", "[Job_num] = " & JobNumber)) Then

if Text

If IsNull(DLookup("[Job_Num]", "[Work_Details_tbl]", "[Job_num] = '" & JobNumber & "'")) Then

Lastly, you need to check your basic logic here, which I can't do, not understanding your business. Your code, in plain speak, as written, is canceling the Deletion if no match is found for the [Job_num] field. Is this what you need?

Linq ;0)>

 

[xcrx]

All that is correct about my code. My code works as is but I will update it as you suggested.

This isn't what I am needed help about though. Like I said the result of me canceling a delete is the same whether I do it with VBA or if I click the cancel button with it asks if you are sure you want to delete the record.