Full Version: delete record from form/subform (without referential integrity )
UtterAccess Forums > Microsoft® Access > Access Forms
Zhang
hi, all
want to delete a parent record in form and child records in subform without referential integrity (Delete Cascade)
and the tables:
1- workerID Primary key
Fname text
Lname text
dateaffiliated date
2- workerPromotions
recordID primary key
workerID number foreign key
Fdate
Tdate
jobID
thank you for any help
theDBguy
Hi,
welcome2UA.gif
Why couldn't you just turn Referential Integrity on? Otherwise, I would probably delete all the child records first before deleting the parent record. To do that, you could use a DELETE query.
Hope that helps...
Zhang
thank you theDBguy
I am trying:
dim thesql as String
thesql = "DELETE FROM workers " & _
"WHERE workers.workerID = " & Me.workerID]
DoCmd.RunSQL thesql
End If
if I turn Referential Integrity on this would tackle the deletion in subform
but I need to add delete string to the statement to make it work without cascade delete
thank you in advance
theDBguy
Hi,
Have you tried?
Dim strSQL As String
strSQL = "DELETE FROM WorkerPromotions WHERE WorkerID = " & Me.WorkerID
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "DELETE FROM Workers WHERE WorkerID = " & Me.WorkerID
CurrentDb.Execute strSQL, dbFailOnError
Hope that helps...
Zhang
hi,
tried this
Dim strSQL As String
strSQL = "DELETE FROM WorkerPromotions WHERE WorkerID = '" & Me.WorkerID
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "DELETE FROM Workers WHERE WorkerID = ' " & Me.WorkerID
CurrentDb.Execute strSQL, dbFailOnError
Oadded single quotation to workerID because it is text
I get error 3075
thank you
theDBguy
Hi,
For a text field, it would be:
CODE
... WHERE WorkerID = '" & Me.WorkerID & "'"

Hope that helps...
Zhang
thank you theDBguy alt="thumbup.gif" />
theDBguy
Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.