deleting records off a subform

[jamin14]

i have constructed code to allow me to delete a single record but i would like to delete all records off the subform is this possible?

my code is below

rivate Sub Command28_Click()
Me.F_Order_line_subform.SetFocus


With Me.F_Order_line_subform
DoCmd.RunCommand acCmdDeleteRecord

End With
End Sub

thanks

[Datagopherdan]

Yes, it is. You would run a delete query. Are you trying to delete all the records in the sub-form that are related to the current record that is showing on the main-form?

If so, lets say that you have your parent child relationship set on ParentID and SubFormID. You would create a command button on your main form and place the below in the on click event.

Private Sub cmdDelete_Click()
Dim intID As Integer
If Not IsNull(Me.ParentID) Then
    intID = Me.ParentID
Else
    Exit Sub
End If
CurrentDb.Execute "DELETE Table2.* " & _
                "FROM Table2 " & _
                "WHERE SubFormID=" & intID
Me.MySubform.Requery
end Sub

[Evgeny]

DoCmd.RunSQL "Delete * From YourTableName"

[Datagopherdan]

You're right, Docmd.RunSQL will work but it's not the preferred way. Chances are that you would not want the user to see the system warnings, which would occur with the RunSQL method unless you turned them off, which is not a good practice.

The Currentdb.Execute method does not display the system warnings and you can easily add a message box prompt to give the user the option to carry through or cancel the action.

[Evgeny]

thank you for pointing out this fact, i am still learning too . I am only running a small DB so i use Docmd.RunSQL which works for me, but i am definitely will be look into changing my code to provided examples. Is there any reason why not use Docmd.RunSQL or is just a universal preference to use TheCurrentdb.Execute?

[Datagopherdan]

You don't ever want to turn system warnings off, which is what you would need to do with RunSQL, if you did not want the user getting a system message everytime you have action queries running. The reason for this is because if the code does not execute to the point of turning them back on, it can cause lots of problems like the user accidentally deleting a record where there would be no prompt to warn them.

When you only have 1 query running such as in this case, the differences are not as noticeable because you would most likely want to notify the user that they are about to delete some records. When you run into instances where you may have a function that is executing several queries in the background for example with an automated import operation where you might be deleting, appending, updating etc. You wouldn't want the user to be prompted each time and you would also not want to turn the system warnings off. With the CurrentDb.Execute method, there are no message displayed and no need to mess with the system warnings. It's a smooth process flow where the user just sees the end results and has no idea what is happening in the background.

Probably a long winded explanation but hope it made some sense.

[Evgeny]

Little bit confusing, but i see where you coming from

So how would your method depict the code below, which uses Docmd.RunSql "Delete" ? :


Private Sub cmdDeleteBuilding_Click()

Dim Answer As String

Answer = MsgBox("Delete Building?", vbQuestion + vbYesNo, "Confirm")

DoCmd.SetWarnings False

DoCmd.RunCommand acCmdSaveRecord

If Not IsNull(Me.txtBuildingID) Then

Select Case Answer

Case vbYes

DoCmd.RunSQL "Delete * From Building Where BuildingID = " & Me.txtBuildingID

DoCmd.Requery

End Select

Else

MsgBox ("Nothing to delete")

End If

End Sub

Regards, Evgeny

[Datagopherdan]

You would get rid of the SetWarnings to False and replace with the RunSQL syntax like below.

CurrentDb.Execute "Delete * From Building Where BuildingID = " & Me.txtBuildingID. So all and all something like this.

Private Sub cmdDeleteBuilding_Click()

Dim Answer As String

If IsNull(Me.txtBuildingID) Then
    MsgBox ("Nothing to delete")
       Exit Sub
End If

Answer = MsgBox("Delete Building?", vbQuestion + vbYesNo, "Confirm")

DoCmd.RunCommand acCmdSaveRecord

Select Case Answer
    Case vbYes
       CurrentDb.Execute "Delete * From Building Where BuildingID = " & Me.txtBuildingID.
DoCmd.Requery
    Case VbNo
      Msgbox "Whatever you would want the message to be here"
End Select


End Sub

[Evgeny]

Thank you for reply, it worked perfectly. Just one thing if you could explain to me: I am relatively new to VBA but taught myself few things so far and getting around it pretty well. I understand IF ... Else functions and such, but have not came across End IF before this thread. Could you give me a little guideline please?

Regards,

P.s Thanking you for sharing the knowledge

[Datagopherdan]

Technically with the example that I provided, you don't need the End If because theres only one condition, it could be just written as

If IsNull(Me.txtBuildingID) Then MsgBox ("Nothing to delete")
       Exit Sub

I always put End If probably more out of habit then anything else, plus in my personal opinion, I think it's a good habit to get used to in order to make sure that you always end the statements with the "End If" for instances where you have multiple condtions.

When you have multiple conditons where it keeps going until the value it finds is true, that's where the ElseIf and End If comes into play

If MyValue = 1 Then
      MsgBox "One"
         Exit Sub
Elseif MyValue = 2 Then
     MsgBox "Two"
        Exit Sub
End If

With the above, it would go through your conditions until it found what was true and exit the sub. So, if MyValue = 1, the routine would exit on the first statement and not even do an evaluation on the second one but if it did not find the condition in the first part, it would go to the second part condition and if it was true, it would exit there.

[Evgeny]

Good example, good explanation = priceless

Thank you