Delete multiple records (1 Viewer)

[Shaimaa T]

Hi all,

If I want to delete multiple records (select more than a record and right-click delete) how can I achieve this

given the following with one record:
Private Sub Form_Delete(Cancel As Integer)

DoCmd.RunSQL "Delete * from Trial where TrialID =(Text40)"

Me.Refresh
End Sub

PS:- I am trying to solve the cannot update /delete because of "modifications due to multiple base tables "issue and I am not sure if attempting to add in the On delete event would fix it (instead of an instead of delete trigger since it would be useless to setup triggers with one table to update ). I am thinking of doing the same with update in the after update event.

 

[jdraw]

Please post a jpg of your tables and relationships.

Can you try again to describe What you want to delete?

You should always do a SELECT query on the records intended for delete --to ensure your selection is correct. Once you are satisfied with the selection then convert the Select query to a Delete query. DELETEs can be quite unforgiving if you delete more than intended.

 

[Shaimaa T]

The view on which i am trying to update/delete records is :

SELECT Trial.TrialID, Trial.TrialCode, Trial.ActualNrOfTotalEntries, Trial.ActualNrOfEntriesVarNr1, Trial.ActualNrOfEntriesVarNr2, Trial.ActualNrOfEntriesVarNr3, Trial.ActualNrOfEntriesVarNr4, Trial.AtualStartweek, Trial.PlannedStartWeek, Trial.ActualStartYear, Trial.CultureID, Trial.weekOfTrialrequest, Trial.yearOfTrialrequest, Trial.remarks1, Trial.remarks2, Trial.RequestedNrOfTotalEntries, Trial.RequestedStartweek, Trial.RequestedNrOfEntriesVarNr1, Trial.RequestedNrOfEntriesVarNr2, Trial.RequestedNrOfEntriesVarNr3, Trial.RequestedNrOfEntriesVarNr4, Trial.plannedRequest, Trial.trialCodeGroup, Trial.OrigRequestedNrOfTotalEntries, Trial.OrigRequestedStartweek, Trial.OrigRequestedNrOfEntriesVarNr1, Trial.OrigRequestedNrOfEntriesVarNr2, Trial.OrigRequestedNrOfEntriesVarNr3, Trial.OrigRequestedNrOfEntriesVarNr4, Trial.PlanningAproved, Trial.EntryFactorTrial, Trial.FirstStepWithEntryFactor, Trial.ReasonEntryFactor, Trial.RequestedEntryFacorTrial, Trial.RequestedFirstStepWithEntryFactor, Trial.weekCorrection, Trial.StepWCorr, Trial.M2Correction, Trial.StepM2Corr, Trial.fixedDeliveryWeek, Trial.createdBy, Trial.dateCreated, Trial.trialevaluation, Trial.TrialCode AS trcdasc, Culture.StartWeek, Culture.CultureName, Culture.Requestor, Culture.Cropspecialist, Culture.Process, Culture.CuttingsOrSeed, Culture.EndLocation, Culture.Crop, Trial.trialCodeGroup AS Expr1, Culture.[EntryVariation?], Trial.OrigRequestedNrOfTotalEntries AS Expr2, Trial.OrigRequestedStartweek AS Expr3, Trial.OrigRequestedNrOfEntriesVarNr1 AS Expr4, Trial.OrigRequestedNrOfEntriesVarNr2 AS Expr5, Trial.OrigRequestedNrOfEntriesVarNr3 AS Expr6, Trial.OrigRequestedNrOfEntriesVarNr4 AS Expr7, Trial.PlanningAproved AS Expr8, Trial.createdBy AS Expr9, Trial.dateCreated AS Expr10, normalstartweek.weeknr AS normalstartweek, Culture.timeregCode
FROM (Culture RIGHT JOIN Trial ON Culture.cultureID = Trial.CultureID) LEFT JOIN (Select * from CultureStep where CultureStepID in  (SELECT  min(CultureStepID) as minWeek   FROM            CultureStep   group by cultureID  ) )  AS normalstartweek ON Culture.cultureid = normalstartweek.cultureid;

Trial has a foreign key CultureID on the primary key CultureId of Culture ; same with Culture Step (1 to many relation between Culture and Trial; and between Culture and Culture Step).

 

[Shaimaa T]

If Trial ID is represented by Text40, how would multiple selections go over each of the values in Text40 until it is deleted ?

 

[wind20mph]

Hi Shaimaa, I found the same problem with my current development. The details are:

DELETE * FROM eqpt WHERE eid = '" & me![eid] & "'"
DELETE * FROM hist WHERE eid ='" & me![eid] & "'"

however I couldn't do it in the current form fEqipment.

This is the full code in the command button, hope someone can share an enlightment:

Private Sub cmdDelete()
   Dim myDB as ADODB.Database
   Dim rsHBK as ADODB.Recordset
   Dim strHKB as String
   
   If MsgBox ("Are you sure you wanted to delete?", vbQuestion+vbYesNo , "Confirmation") = vbYes Then
     strHBK = "DELETE * FROM hist WHERE eid = '" & Me![eid] &"'"
     Set myDB = New ADODB.Database
     Set rsHBK = New ADODB.Recordset
     rsHBK.Open strHBK, CurrentProject.Connection,,adOpenStatic
    
     rsHBK.Close
     Set rsHBK = Nothing
     
     strHBK = "DELETE * FROM eqpt WHERE eid = '" & Me![eid] & "'"
     rsHBK.Open strHBK, CurrentProject.Connection, , adOpenStatic
    
     rsHBK.Close
     Set rsHBK = Nothing
     Set MyDB = Nothing
   End if
End Sub

However this code gives a lot of confirmation alerts... and finally cannot delete the record.

Hi all,

If I want to delete multiple records (select more than a record and right-click delete) how can I achieve this

given the following with one record:
Private Sub Form_Delete(Cancel As Integer)

DoCmd.RunSQL "Delete * from Trial where TrialID =(Text40)"

Me.Refresh
End Sub

PS:- I am trying to solve the cannot update /delete because of "modifications due to multiple base tables "issue and I am not sure if attempting to add in the On delete event would fix it (instead of an instead of delete trigger since it would be useless to setup triggers with one table to update ). I am thinking of doing the same with update in the after update event.