Johnsonsimon
Registered User.
- Local time
- Today, 00:24
- Joined
- May 5, 2012
- Messages
- 45
Hello all... again,
I am trying to get something to work using VBA, but I am really struggling. I presume it can be done, but I thought I should defer to the experts to find out.
The DB is a booking system for a chauffeur company. The are numerous tables, but the important 4 regarding my question are as follows;
Jobs & Booking
LocationMaster
LocationCollection
LocationDropoff
(The location tables all use LocationID primary key. The Jobs & Booking table has fields Called Collection Location and Drop-off Location which link via Foreign keys to the respective tables LocationCollection or LocationDropoff)
To add new locations or update current ones the user opens a form which links to the LocationMaster Table and then upon closing the form it runs some action queries...
Basically I have Append and Update queries that run to append or update the details from the LocationMaster table to the LocationCollection and LocationDropoff Tables.
The issue arises when I try to delete certain records from the LocationMaster table, if they are referenced in Records on the Jobs table. I have tried using a Delete Query which semi works, but often leaves records in one table intact, but deletes them from other tables (i.e. if the location has been selected in a Job as a collection and never used as a drop off location the delete query will delete it from the dropoff table and master table but leave it in the collection table.).
I have tried to use some VBA and an IF statement to get it to check if the LocationID is referenced in either the Collection Location or Dropoff Location fields of the Jobs & Booking table in any of the records, and if it is then to stop END the VBA but if it isnt referenced anywhere then the record will be deleted from all 3 tables (LocationMaster;Collection;Dropoff).
Here is the code, which obviously doesn't work, but I am not adept enough at VBA to even begin to figure out what I need to do. So thats where you come in..... Please help
Cheers Simon
I am trying to get something to work using VBA, but I am really struggling. I presume it can be done, but I thought I should defer to the experts to find out.
The DB is a booking system for a chauffeur company. The are numerous tables, but the important 4 regarding my question are as follows;
Jobs & Booking
LocationMaster
LocationCollection
LocationDropoff
(The location tables all use LocationID primary key. The Jobs & Booking table has fields Called Collection Location and Drop-off Location which link via Foreign keys to the respective tables LocationCollection or LocationDropoff)
To add new locations or update current ones the user opens a form which links to the LocationMaster Table and then upon closing the form it runs some action queries...
Basically I have Append and Update queries that run to append or update the details from the LocationMaster table to the LocationCollection and LocationDropoff Tables.
The issue arises when I try to delete certain records from the LocationMaster table, if they are referenced in Records on the Jobs table. I have tried using a Delete Query which semi works, but often leaves records in one table intact, but deletes them from other tables (i.e. if the location has been selected in a Job as a collection and never used as a drop off location the delete query will delete it from the dropoff table and master table but leave it in the collection table.).
I have tried to use some VBA and an IF statement to get it to check if the LocationID is referenced in either the Collection Location or Dropoff Location fields of the Jobs & Booking table in any of the records, and if it is then to stop END the VBA but if it isnt referenced anywhere then the record will be deleted from all 3 tables (LocationMaster;Collection;Dropoff).
Here is the code, which obviously doesn't work, but I am not adept enough at VBA to even begin to figure out what I need to do. So thats where you come in..... Please help
Cheers Simon
Code:
If (Eval("6<>MsgBox(""Are you sure you wish to delete this Location?"" & Chr$(13) & ""Please Note: If this location is used in a current, completed or cancelled job it will NOT be deleted."",52,""Delete Location"")")) Then
End
Else
If Me.[LocationID] = [Tables]![tbl_Job]![Collection Location] Or [Tables]![tbl_Job]![Drop-off Location] Then
End
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete Query - Collection", acViewNormal, acEdit
DoCmd.OpenQuery "Delete Query - Dropoff", acViewNormal, acEdit
DoCmd.OpenQuery "Delete Query - Master", acViewNormal, acEdit
MsgBox "The selected location has been deleted." & Chr(13) & "It will still be displayed here until the next time you open this form." & Chr(13) & Chr(13) & "Please Note: If this location is used in a current, completed or cancelled job it will NOT have been deleted.", vbInformation, "Location Deleted"
End If
End If