prevent deletion of inventory items containing matching sales trans

[BofRossClan]

I am working on an sales/inventory program. I need to be able to stop the user from deleting inventory items in the Master Inventory that have matching sales transactions. I have placed a delete button next to the item in my inventory lookup form. This is the code I tried, and it didn't work. I created a table "MasterInventory Without Matching Transactions" to search from. I get an error message "You canceled the previous operation" when I run my code.

varCode = Me.ItemCode
Dim varResult As Variant

varResult = DLookup("[InvID]", "[MasterInventory Without Matching Transactions]", "invid = " & varCode)

If varResult Is Null Then

MsgBox ("Sales transanctions exist for this item. You cannot delete it")
Exit Sub

Else

DoCmd.RunSQL "DELETE MasterInventory.InvID FROM MasterInventory" & _
" WHERE invid = " & varCode

End If

http://www.accessbrenda.com

 

[Remou]

Is there a reason why you are not using a relationship that enforces referential integrity?

http://lessthandot.com

 

[PHV]

If InvID is not defined as numeric in the table "MasterInventory Without Matching Transactions":

varResult = DLookup("InvID", "[MasterInventory Without Matching Transactions]", "InvID='" & varCode & "'")

Anyway, replace this:
If varResult Is Null Then
with this:
If IsNull(varResult) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BofRossClan]

I am enforcing referential integrity. That why I need to stop inventory items from being deleted - all sales transactions are deleted when that happens.

http://www.accessbrenda.com

 

[PHV]

all sales transactions are deleted
seems you've enabled the cascading delete stuff ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BofRossClan]

Anyway, replace this:
If varResult Is Null Then
with this:
If IsNull(varResult) Then

Still getting the same result. I made sure my INVID in my query was set to numeric.

http://www.accessbrenda.com

 

[PHV]

Have you disabled the cascading delete option in the RelationShip ?
Are you sure that the ItemCode control in your form is populated and numeric ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BofRossClan]

I don't really want to disable the cascading delete. The data is important in both tables, and I really don't want either of them deleted.

The itemCode has to be populated for the delete button to show up on the form, and all my ItemCode fields are numeric.

http://www.accessbrenda.com

 

[BofRossClan]

I found the problem. I was referencing the wrong field. The ItemCode field is NOT numeric. I forgot I had a separate numeric field for indexing.

Thanks.

http://www.accessbrenda.com

 

[PHV]

The data is important in both tables, and I really don't want either of them deleted.
So, you definitively want to disable the cascading delete !

 

[BofRossClan]

So, you definitively want to disable the cascading delete !

Well, not really. It would create a huge mess to have sales transactions hanging out there without a master inventory item to reference, and I have some code that relies on the cascading delete. As long as I force the user into NOT being able to delete when there are transactions, there won't be a problem. Thus this code - because my client did delete records he shouldn't have!!!!

http://www.accessbrenda.com

 

[PHV]

With a properly configured RelationShip you can't delete a referenced item.