• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 521
  • Last Modified:

Personnalize the MsAccess DELETE button

Hi Experts,

I have a command button that DELETE the record in the database and ARCHIVES it in the table TblArchive. The button runs a query. It works but it pops-ups three confirmation messages before the record is deleted and inserted in the Archive table. Is there a way to have just one warning message instead of 3 ?

Actually the code for the button is this:
Private Sub Command26_Click()
'move Record to Archive and Delete it from TblCalendar
   
'1 move the Record to tblArchive
    DoCmd.OpenQuery "QueryMove"
    'MsgBox("Do you wish to archive this record?" vbYesNo) = vbNo Then Exit
'2 now Delete this Record from tblCalendar,
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

End Sub

I tried to change it to:
Private Sub Command79_Click()
Dim confirm As Integer
confirm = MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Confirm Delete")
If confirm = 6 Then 'User clicked Yes
  'DoCmd.SetWarnings False 'prevent a second confirm message
  'DoCmd.RunCommand acCmdDeleteRecord
  DoCmd.OpenQuery "QueryMove"
  DoCmd.SetWarnings True
End If

'2 now Delete this Record from tblCalendar,
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End Sub

but with this, the record is deleted....but the query is not called.

thanks in advance for your help
stronghold888
0
stronghold888
Asked:
stronghold888
  • 5
  • 3
2 Solutions
 
LucasCommented:
You should call your query to move the data first, then delete it.

Private Sub Command79_Click()
Dim confirm As Integer
confirm = MsgBox("Are you sure you want to delete this record?", vbQuestion + vbYesNo, "Confirm Delete")
If confirm = 6 Then 'User clicked Yes
  'DoCmd.SetWarnings False 'prevent a second confirm message
    DoCmd.OpenQuery "QueryMove"
    'Delete the data from the calendar: currentdb.execute "Delete from tblcalendar where id = " & id & ""
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
else
   'Delete data from calendar
   'Delete the data from the calendar: currentdb.execute "Delete from tblcalendar where id = " & id & ""
End If
End Sub
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
DoCmd.SetWarnings False
'All your code goes here
DoCmd.SetWarnings True
0
 
stronghold888Author Commented:
This one ?

Private Sub Command26_Click()
'move Record to Archive and Delete it from TblCalendar
   
'1 move the Record to tblArchive
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QueryMove"
   
'2 now Delete this Record from tblCalendar,
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    DoCmd.SetWarnings True
End Sub

but here ther record is just deleted..not moved to the archive table even if the query is called
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LucasCommented:
Then something is up with your "querymove."  Is it an append query?
0
 
stronghold888Author Commented:
yes, it's an append query. it deletes the record from the TblCalendar, then insert it in the TblArchive
0
 
stronghold888Author Commented:
If there are no modifications to the delete button, the query works perfectly and the record is deleted and then moved to the TblArchive...but I have to click on the OK button 3 times to confirm the deletion, then the move to the TblArchive.
0
 
LucasCommented:
What if you created a delete query to delete the record.

docmd.setwarnings 0
DoCmd.OpenQuery "QueryMove"
DoCmd.OpenQuery "YourDeleteQuery"
docmd.setwarnings -1
0
 
stronghold888Author Commented:
The second query will delete the record....but in this case there should be something on the record, a case or checkbox that states that the record can be deleted? because before I had this kind of query and since, I had to move to a single query.

The move query is as it follows:
INSERT INTO TblArchive ( StatID, Activity, Calendar_E, Calendar_F, EventDay36, DirectorateID, Rel_Link, EMSQCode, StatReq, Stat_Applic, EMS_Item, EventDay45, EventDay51, EventDay55 )
SELECT TblCalendar.StatID, TblCalendar.Activity, TblCalendar.Calendar_E, TblCalendar.Calendar_F, TblCalendar.EventDay36, TblCalendar.DirectorateID, TblCalendar.Rel_Link, TblCalendar.EMSQCode, TblCalendar.StatReq, TblCalendar.Stat_Applic, TblCalendar.EMS_Item, TblCalendar.EventDay45, TblCalendar.EventDay51 AS Expr1, TblCalendar.EventDay55
FROM TblCalendar
WHERE (((TblCalendar.StatID)=[Forms]![FrmCalendarModify]![StatID]));

I quess I have to use the same, but change it in a delete query. Is this good?
0
 
stronghold888Author Commented:
Hello,

Did you found anything that could help?

thanks

stronghold888
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now