Link to home
Start Free TrialLog in
Avatar of Marilyn Messineo
Marilyn MessineoFlag for United States of America

asked on

Delete Records on a Form in Microsoft Access

I have a button on a form that will allow the user to delete the record that is being displayed.  I want to give them the ability to delete multiple records (multiple tables linked) at one time.  I thought about building a form that lists quote numbers and names and adding a "Delete" checkbox so that the user can choose the Quotes they want to delete.  I'm not sure if I use a combo box or list box.  Also, would I need to loop through the chosen records in VBA in order to delete multiple quotes at one time?  Below is my current Delete routine.

Thanks!

Private Sub cmdDeleteQuote_Click()
Dim msg, Style, Title, Help, Ctxt, Response, MyString
Dim db As DAO.Database
Dim rstQuote As DAO.Recordset
Dim strSQL As String
Dim strQuoteNo As String

On Error GoTo Delete_Quote_Err


Set db = CurrentDb()

strQuoteNo = Me.QuoteNo
strQuoteName = Me.Quote_Name

' Define Message, Buttons and Title
msg = "You are about to delete Quote: " & Me.QuoteNo & ": " & Me.Quote_Name & _
        ", are you sure you want to continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Delete Quote"

'Prompt use if they want to delete quote
Response = MsgBox(msg, Style, Title, Help, Ctxt)

If Response = vbYes Then
    DoCmd.OpenQuery ("qdel_Quotes")
    DoCmd.OpenQuery ("qdel_Quote_Equipment")
    DoCmd.OpenQuery ("qdel_Quote_Disposables")
    DoCmd.OpenQuery ("qdel_Quote_Team")
    DoCmd.OpenQuery ("qdel_Quote_Bookings")
   
    strSQL = "Select QuoteNo, Total_Versions from tbl_Quotes where QuoteNo = '" & strQuoteNo & "';"
    Set rstQuote = db.OpenRecordset(strSQL, dbOpenDynaset)
    'Update Quote Version Numbers
    If rstQuote.EOF Then
        intVersions = 0
    Else
        intVersions = rstQuote!Total_Versions - 1
    End If
    If intVersions <> 0 Then
        strSQL = "Update tbl_Quotes " & _
                "Set Total_Versions = " & intVersions & _
                " Where QuoteNo = '" & Me.txtQuoteNo & "';"
    DoCmd.RunSQL strSQL
    End If
    MsgBox ("Quote " & strQuoteNo & ": " & strQuoteName & "has been deleted.")
   
    'Refresh Forms
    Me.txtQuoteNo = ""
    Me.cboQuoteName.Requery
    Me.fsub_Equipment_Selected.Form.Requery
    Me.Form.Requery
    Me.txtListPrice = 0
Else
    MsgBox ("Quote " & Me.QuoteNo & ": " & Me.Quote_Name & " was not deleted.")
End If
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

The easy method is to set up Referential Integrity between the tables with Cascade Delete turned on.
Then, when user deletes a quote, all related data are deleted at once and automatically.

/gustav
Apart from Gustav's comment, it will be a lot work.

To give a meaningful comment, give a representative example of the issue.
Upload demo tables with few records.
Explain how to manage related records.
Avatar of Marilyn Messineo

ASKER

Let's say I have 1 table - tbl_quotes with 2 fields QuoteNo (unique id) and Quote Name.  I want to display all the records in tbl_quotes in a list box or combo box and give the user the ability to select multiple quotes to delete at 1 time.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I agree with Gustav.  Setting a Deleted field to False is much safer.  No matter what the client says, often they will want (or need) to look at deleted data.  An alternative is to archive the data -- see my Access Archon articles on this topic, using several different methods.
accarch209.zipaccarch207.zip