Best way to Delete in other database for referential integrity 1

[autex]

Hello.
I have a form that shows company info. If user deletes a company I want the entries in a table in another acess DB to be deleted as well for RI.

I'm having trouble finding the best place to put my code. If I put in in afterdelconfirm the me.company_numeric used is not the company that was deleted it is the company that pops up on screen to to replace the deleted one.

If it is best to put it in the beforedelconfirm I'm not sure of the best way to have it there, with cancelling deletions etc.

This is my code I'm trying to find a place for:
'deletes entry from plan-comp table to maintain integrity

Dim deltcomplan As Database
Dim deltcomplanrec As DAO.Recordset
Dim delcompnum As Long

Set deltcomplan = CurrentDb
Set deltcomplanrec = deltcomplan.OpenRecordset("plan-company t",dbopendynaset)

delcompnum = Me.Company_Numeric

deltcomplanrec.MoveFirst

Do While Not deltcomplanrec.EOF
If deltcomplanrec![Company Numeric] = delcompnum Then
deltcomplanrec.Delete
End If
deltcomplanrec.MoveNext
Loop

Set deltcomplanrec = Nothing
Set deltcomplan = Nothing

thanks in advance

 

[autex]

Any other info I can provide to increase my odds?

 

[HitechUser]

More detail on this please...

If I put in in afterdelconfirm the me.company_numeric used is not the company that was deleted it is the company that pops up on screen to to replace the deleted one.

..

 

[autex]

Private Sub Form_AfterDelConfirm(Status As Integer)

'deletes entry from plan-comp table to maintain integrity

Dim deltcomplan As Database
Dim deltcomplanrec As DAO.Recordset
Dim delcompnum As Long

Set deltcomplan = CurrentDb
Set deltcomplanrec = deltcomplan.OpenRecordset("plan-company t",dbopendynaset)

delcompnum = Me.Company_Numeric

deltcomplanrec.MoveFirst

Do While Not deltcomplanrec.EOF
If deltcomplanrec![Company Numeric] = delcompnum Then
deltcomplanrec.Delete
End If
deltcomplanrec.MoveNext
Loop

Set deltcomplanrec = Nothing
Set deltcomplan = Nothing
End Sub

What is above deletes the me.company_numeric I want to refer to in this recordset code before this code has a chance to run.

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)

ditto

end sub

This just runs the recordset code referred to above.

 

[RoyVidar]

opinion

I would disallow delete thru the interface, and use a delete button firing of delete queries.

1 - fetch the PK of the company to be deleted
2 - execute delete queries "from the bottom and up"

- short sample without any errorhandling, typed not tested...

[tt]private sub cmbmydelete_click()
dim strSql as string
dim lngPK as long
lngPK = me("Company_Numeric").value
strsql = "delete * from [plan-company t] where [Company Numeric] =" & lngPK
currentdb.execute strsql
strsql = "delete * from [<your main table>] where [Company Numeric] =" & lngPK
me.requery
end sub[/tt]

I'm of the opinion that what can be achieved thru queries, should be performed thru queries, but well, it's just an opinion...

Roy-Vidar

 

[autex]

ok, I'll do that. I've never used a delete query, not once. I tend to choose the more complicated path, but not because of my opinions, I've been coding for about 1 month. I like the delete query, takes out my loop and shortens the code. Thanks.