Question Delete Button: Forms and Tables

[doddy88]

Hi,

I have a form, with tab control which includes fields from two tables. When I delete a record on a form it deletes fine.

When I go into the tables the record is still there in the one side of the relationship but has been deleted in the table of the many side.

I've read that a simple delete button on a form will only delete records in the many side of a relationship from a table.

How do I get a delete button that deletes the record from both the one and many tables.

Regards

 

[boblarson]

If you have a one-to-many relationship you shouldn't be trying to use a single query/form to display and use it. You should be using a main form for the one side and a subform for the many side. And in your relationships you set Cascade Deletes to YES. And then if you delete the one side record it will automatically delete the many side.

 

[doddy88]

Hi,

I am aware a subform would be better in most cases, but with there been so much information and on 95% of the time the relationship would be one to one, I have gone against using subforms.

Im not sure if not using subforms would affect this?

I have set Cascade Deletes to YES on the relationships but to no avail!

 

[boblarson]

You can try going into the query/select statement for the form and in the query properties (right click on the gray area where the tables show up in the query by example grid and select PROPERTIES) and then change Recordset Type from Dynaset to Dynaset(inconsistent updates).

If that doesn't work, a subform is likely what you'll need.

 

[doddy88]

Unsuccessful! If anyone has any other ideas, let me know cheers!

 

[boblarson]

You could use code to delete the many record and then the parent record.

Dim strSQL As String
 
strSQL = "Delete * From ChildTableNameHere Where PKFieldName = " & Me.PKField
 
CurrentDb.Execute strSQL, dbFailOnError
 
strSQL = "Delete * From ParentTableNameHere Where PKFieldName = " & Me.PKField
 
CurrentDb.Execute strSQL, dbFailOnError

 

[doddy88]

I've adapted the code to:

Dim strSQL As String

strSQL = "Delete * From tblPlacements Where Placement_ID = " & Me.PKfield

CurrentDb.Execute strSQL, dbFailOnError

strSQL = "Delete * From tblStudents Where Student_ID* = " & Me.PKfield

CurrentDb.Execute strSQL, dbFailOnError

It is highlighting the PKfield.

I noticed for some reason Placement_ID was not a primary key when it should be, so I made it a primary key again and then altered the record source in the form to show this. Think this could be a big problem?

 

[boblarson]

The Me.PKField is wrong unless you have a field named PKField in the table or as a control on the form bound to the field. You need to use the actual names of your field or control

 

[doddy88]

Sorry a touch confused.

The primary keys for the tables are Placement_ID and Student_ID*, what are PKFields? Are these the primary key field names too?

 

[boblarson]

PKFields are just what I used to refer to apparently Placement_ID and Student_ID.

 

[doddy88]

I've tried the following:

Dim strSQL As String

strSQL = "Delete * From tblPlacements Where Placement_ID = " & Me.Placement_ID

CurrentDb.Execute strSQL, dbFailOnError

strSQL = "Delete * From tblStudents Where Student_ID* = " & Me.Student_ID*

CurrentDb.Execute strSQL, dbFailOnError

Produces error 3705. Deletes from the form but the record is still viewable inside the table tblstudents.

The Student_ID field is used on every tab but obviously the control name is different on each tab, could this be why?