Select and Delete a specific child record

[AlanM]

I have three tables:

tblCompany (one to many) tblLocations (one to many) tblPersonnel. The relationships are all ERI with cascade updates (but not cascade deletes)

I have a form showing company details (name etc from tblCompany) in the header with multiple subforms (each on a tab) for each of the company's locations (a given LocationID, phone, address, etc from tblLocations). (There is no personnel data on this form)

In the header area I want to include a button, to give the user facility to select and delete a Location, (using the LocationID) when it is no longer required. (The defined database relationships will prevent deletion of a location, if there are still personnel child records attached.)

I am having trouble with the code behind the button that needs to do two things:

1, include the parameter asking "which locationID do you wish to delete?"

and

2, restricting the selection and subsequent delete action to only those Locations attached to the current Company (parent) on the form.

Each record in tblCompanies has a unique key field, linking to its child Locations.
Each record in tblLocations has a unique key field, linking to its child personnel records. However the given LocationIDs are not unique, ie CompanyA can have a Location1 and a Location2 and a Location3, while CompanyB may have only a Location1 and a Location2.


Does anyone have a piece of sample code to do this please?

I've looked for examples of this but cannot locate any.

Thanks

AlanM

 

[Lister]

Seem’s simple enough, if you have say, a list box with all the locations.
The user would select the location and then click the button (or right click for a menu or whatever) “Delete”. The button is called btnDeleteLocation
Let say that you list box was called lstLocations

Sub btnDeleteLocatioin_Click()
'Error Handler here
Dim strSQL As String
Dim inHolder As Integer
‘Check to see if list is highlighted here
inHolder = Me.lstLocations
DoCmd.Setwarnings = False
strSQL = “Delete * From tblLocations Where WHATEVERYOURFIELDIS = “ & inHolder
DoCmd.RunSQL (strSQL)
DoCmd.Setwarnings = True
'Error Hander here
End Sub

Something like that.
Hope it help mate, let us know how you get on