delete record in a main form and any link records in the subform

G

Guest

my problems is that if the subform dont have any record the system return
with a MICROSOFT OFFICE ACCESS MESSAGE - "NO CURRENT REORD"

CLICK "OK"

however the system does not delete the record in the mainform. Can anyone
help?

My coding is as follows:
Private Sub Delete_equipment_Click()
On Error GoTo Err_Delete_equipment_Click
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "you are about to IRREVERSIBLY DELETE *ALL*INFORMATION CONCERNING
THIS MAIN EQUIPMENT FROM THE DATABASE!?" ' Define message.
Style = vbOKCancel + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "ARE YOU 100% SURE!!!?" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose Ok.
Me.AllowDeletions = True
Me.FORM_conveyor_data.Form.Recordset.Delete
'Me.Form_Brake.Form.Recordset.Delete
'Me.Form_Backstop.Form.Recordset.Delete
'Me.Form_Coupling.Form.Recordset.Delete
'Me.Form_CV_GearBox.Form.Recordset.Delete
'Me.FORM_CV_pulley.Form.Recordset.Delete
'Me.Form_motor_equipment.Form.Recordset.Delete
'Me.Form_Instrumentation.Form.Recordset.Delete
Me.[sub equipment details].Form.Recordset.Delete
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
Exit_Delete_equipment_Click:
Exit Sub

Err_Delete_equipment_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Delete_equipment_Click

End Sub


Many thanks in advance
 
D

Dirk Goldgar

hngo said:
my problems is that if the subform dont have any record the system
return with a MICROSOFT OFFICE ACCESS MESSAGE - "NO CURRENT REORD"

CLICK "OK"

however the system does not delete the record in the mainform. Can
anyone help?

My coding is as follows:
Private Sub Delete_equipment_Click()
On Error GoTo Err_Delete_equipment_Click
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "you are about to IRREVERSIBLY DELETE *ALL*INFORMATION
CONCERNING THIS MAIN EQUIPMENT FROM THE DATABASE!?" ' Define
message. Style = vbOKCancel + vbCritical + vbDefaultButton2 '
Define buttons. Title = "ARE YOU 100% SURE!!!?" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose Ok.
Me.AllowDeletions = True
Me.FORM_conveyor_data.Form.Recordset.Delete
'Me.Form_Brake.Form.Recordset.Delete
'Me.Form_Backstop.Form.Recordset.Delete
'Me.Form_Coupling.Form.Recordset.Delete
'Me.Form_CV_GearBox.Form.Recordset.Delete
'Me.FORM_CV_pulley.Form.Recordset.Delete
'Me.Form_motor_equipment.Form.Recordset.Delete
'Me.Form_Instrumentation.Form.Recordset.Delete
Me.[sub equipment details].Form.Recordset.Delete
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
Exit_Delete_equipment_Click:
Exit Sub

Err_Delete_equipment_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Delete_equipment_Click

End Sub


Many thanks in advance
Click to expand...

The simplest, no-code way to support this process is to set "Cascade
Deletes" on the relationship between the main form's table and the
subform's table. If you do that, you don't have to do anything special
to delete the subform's records.

If for some reason you don't want to use Cascade Deletes, then you could
just run a delete query to delete all related records, then requery the
subform. For example:

CurrentDb.Execute _
"DELETE * FROM Conveyer_Data " & _
"WHERE EquipmentID = " & Me!EquipmentID,
dbFailOnError

Me!FORM_conveyor_data.Requery
 
G

Guest

SORRY

WHAT DO I HAVE TO DO? REPACE ALL MY CODE WITH YOURS?

Dirk Goldgar said:
hngo said:
my problems is that if the subform dont have any record the system
return with a MICROSOFT OFFICE ACCESS MESSAGE - "NO CURRENT REORD"

CLICK "OK"

however the system does not delete the record in the mainform. Can
anyone help?

My coding is as follows:
Private Sub Delete_equipment_Click()
On Error GoTo Err_Delete_equipment_Click
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "you are about to IRREVERSIBLY DELETE *ALL*INFORMATION
CONCERNING THIS MAIN EQUIPMENT FROM THE DATABASE!?" ' Define
message. Style = vbOKCancel + vbCritical + vbDefaultButton2 '
Define buttons. Title = "ARE YOU 100% SURE!!!?" ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbOK Then ' User chose Ok.
Me.AllowDeletions = True
Me.FORM_conveyor_data.Form.Recordset.Delete
'Me.Form_Brake.Form.Recordset.Delete
'Me.Form_Backstop.Form.Recordset.Delete
'Me.Form_Coupling.Form.Recordset.Delete
'Me.Form_CV_GearBox.Form.Recordset.Delete
'Me.FORM_CV_pulley.Form.Recordset.Delete
'Me.Form_motor_equipment.Form.Recordset.Delete
'Me.Form_Instrumentation.Form.Recordset.Delete
Me.[sub equipment details].Form.Recordset.Delete
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If
Exit_Delete_equipment_Click:
Exit Sub

Err_Delete_equipment_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Delete_equipment_Click

End Sub


Many thanks in advance
Click to expand...

The simplest, no-code way to support this process is to set "Cascade
Deletes" on the relationship between the main form's table and the
subform's table. If you do that, you don't have to do anything special
to delete the subform's records.

If for some reason you don't want to use Cascade Deletes, then you could
just run a delete query to delete all related records, then requery the
subform. For example:

CurrentDb.Execute _
"DELETE * FROM Conveyer_Data " & _
"WHERE EquipmentID = " & Me!EquipmentID,
dbFailOnError

Me!FORM_conveyor_data.Requery

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

hngo said:
SORRY

WHAT DO I HAVE TO DO? REPACE ALL MY CODE WITH YOURS?
Click to expand...

Please don't shout.

Assuming you didn't mean to be shouting, I need to find out what you
really want to do. Did you consider taking the first approach I
suggested -- using cascading deletes to automatically delete all related
records when you delete the main record? That would eliminate the need
for any code at all.
 
G

Guest

The caps lock key is on at the time , I did not take any notice. please
accept my appologies if this upset you.

iI can not use the first approach as not all the table link together. And
at this point in time I am not sure, so I prefer the second approach ie coding

many thanks
 
D

Dirk Goldgar

hngo said:
The caps lock key is on at the time , I did not take any notice.
please accept my appologies if this upset you.
Click to expand...

Apology accepted. It did make it hard for me to interpret the tone of
your message. Generally, typing in all caps is read as shouting.
iI can not use the first approach as not all the table link together.
Click to expand...

Maybe the tables *should* be linked together, if these are records that
are related to the main form's record. That's what relationships and
referential integrity are all about -- ensuring that there are never any
"orphan" records. However, I won't pursue that approach if you don't
want to .
And
at this point in time I am not sure, so I prefer the second approach
ie coding
Click to expand...

Okay. For this purpose, I need to know the names of the tables involved
and by what key fields they are related. In my earlier post, I was just
guessing.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

delete record from bubform again 1
Deleting a blank record in a form 1
Deleting a Form & SubForm Record 1
Delete record from form and tables 2
delete 1 record on the continous form 4
How to wait till the Report is printed from a form? 8
print a record in form view 5
Help !Docmd.domenuitem to delete record in a form??? 1

Top