Marilyn Messineo
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.Requ ery
Me.Form.Requery
Me.txtListPrice = 0
Else
MsgBox ("Quote " & Me.QuoteNo & ": " & Me.Quote_Name & " was not deleted.")
End If
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
Me.Form.Requery
Me.txtListPrice = 0
Else
MsgBox ("Quote " & Me.QuoteNo & ": " & Me.Quote_Name & " was not deleted.")
End If
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.
To give a meaningful comment, give a representative example of the issue.
Upload demo tables with few records.
Explain how to manage related records.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
accarch209.zipaccarch207.zip
Then, when user deletes a quote, all related data are deleted at once and automatically.
/gustav