prevent record deletion if relasted records exist

[tonycl69]

Hi all and thanks for reading.
I have a database for vehicles and sometimes they go on a job and the location is recorded as to find it for servicing etc, the location is in a another table related to the vehicle table. But if I delete the location, regardless if there are any related records I can still delete the record from the location table. How can I prevent the user from deleting a location if related records exist, I am using a form to edit/delete location.

[ranman256]

set the relationship property, database tools tab menu:
set CASCADE DELETE
and
CASCADE UPDATE

[tonycl69]

Hi and thanks for that, two things, I have been to check the relationship and the cascade update and delete are greyed out, also it is an access front end with all the tables on a SQL server

[ranman256]

Then it needs to be done on the backend.

[Bulzie]

Can you list the fields in your tables and an example of the data?

Not sure I understand exactly your issue but you can always do a DLookUp on the VehicleID to see if there is a record before deleteing a Location record that is tied to that vehicle.

[tonycl69]

Hi Bulzie how would I write that, sorry not too familiar with vba.

[Bulzie]

Maybe in the OnDelete Event of the Location form or on the Delete button. Assuming VehicleID is a field even if invisible on your Location record that they want to delete (hence the me.VehicleID).

If Not isnull(DLookup("[VehicleID]", "tblVehicle", "[VehicleID] =" & me.VehicleID)) then

Msgbox "This location cannot be deleted"
End

End If

DoCmd.RunCommand acCmdDeleteRecord

[tonycl69]

Hi Bulzie, thanks for that much appreciated, I'll try it out now.

[ssanfu]

It sounds like you should not have "Cascading Delete".

Since your BE is SQL Server, read these:

Referential Integrity in Microsoft SQL Server
http://www.craigsmullins.com/sql_ref.htm Look for a heading "Delete Rules"


The following two links are for Visual Studio, but if you set Cascading actions for ON DELETE to "NO ACTION", you shouldn't be able to delete a PK record if there are child (FK) records

Cascading Referential Integrity Constraints
https://technet.microsoft.com/en-us/...ql.105%29.aspx

Foreign Key Relationships Dialog Box (Visual Database Tools)
https://technet.microsoft.com/en-us/...ql.105%29.aspx