Delete a record from two different tables

[DKpitbull]

Hi all!

I have a form which contains many text boxes. What I'm doing now is that any time I create a new record I save this record to the table the form was created from and I also save to a specific field of an irrelevant table(other table) the index of one of the text boxes of this form.

e.g

My form contains the text boxes: "Index", "Tabletype", "Component".

I save all of the above to the table "tblTransistor".

Then I take the "Component" only and I save it in the field "Test" of another table which I call "tblManComps".

My form is called: "Transistor".

So far so good...

Now I want to be able to delete at the same time the record from each table.
Is it possible to have some VBA which will do it?

Thanks in advance!

 

[Drunkenneo]

Does your these two tables are related one to one relation ship, if it is so, access will take care of deleting all the rows related to it.

If not could i know exactly which datas you want to delete and taken care of by VBA?

 

[DKpitbull]

The tables are totally different and not connected.

Every time I enter a new record in my form, the index of the text box "Component" is saved in the "Transistor" table and in the "ManComps" table.

What I need to is, every time I select to delete on record from the form, my VBA code to delete the record from both tables.

I tried the code below, but it appears me the message 'Too few parameters. Expected 1'.

Code:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_Delete_Click
If MsgBox("This will permanently delete your record. Are you sure you want to Delete?", vbYesNo) = vbYes Then
'Turn OFF access messages
DoCmd.SetWarnings False
CurrentDb.Execute "DELETE * FROM ManComps WHERE Catalogue Ref = " & Me.Component
DoCmd.GoToRecord , , acNewRec
cboEdit.Requery
cboEditLastname.Requery
cboEditTMR.Requery
End If
Exit_Delete_Click:
'Turn ON access messages
DoCmd.SetWarnings True
Exit Sub
Err_Delete_Click:
MsgBox Err.Description
Resume Exit_Delete_Click
End Sub

I attache my project to make it easier!

Thanks Drunkenneo!

 

[Drunkenneo]

The attached file is not opening, could you please send it again.

 

[DKpitbull]

Thanks Drankenneo, but I found the solution!

Private Sub cmdRunQuery_Click()
If MsgBox("Would you like to delete?", vbYesNo, "Confirmation") = vbYes Then
Set rstDelete = CurrentDb.OpenRecordset("SELECT * FROM ManComps WHERE [Catalogue Ref] = '" & Component & "'")
rstDelete.Delete
Set rstDelete = CurrentDb.OpenRecordset("SELECT * FROM [Transistor Catalogue] WHERE Component = '" & Component & "'")
rstDelete.Delete
rstDelete.Close
Forms!Transistor_Catalogue_query.Requery
End If
End Sub