Results 1 to 8 of 8
  1. #1
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    70

    bypass delete confirmations


    I know we can bypass confirmation messages through Options > Client settings > Confirm at the program level. Can this be done at a database level with VBA? I have a database that uses a delete query on startup to delete old records. I'd like to be able to surpress the confirmation messages across the board, without having all of my users change program settings.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,344
    Docmd.SetWarnings False

    Code that gives the messages

    DoCmd.SetWarnings True

  3. #3
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,122
    Using DoCmd.SetWarnings False will suppress all messages including error messages

    Better still use CurrentDB.Execute ...., dbFailOnError.
    This will suppress those confirmation messages but still show error messages
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  4. #4
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    70
    Thanks! Which module would that go into? Or do I need to create a new one? I'm having problems adding a screenshot of my modules:
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,836
    I have a database that uses a delete query on startup to delete old records
    .
    Where exactly is the code that deletes old records? Can you post a screenshot of that code?

  6. #6
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,122
    That code doesn't normally go into a module.
    You would use it wherever any kind of action sql statement is used e.g. In a form load event or button click
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  7. #7
    Poohbear0471 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    70
    Gotcha. This is the onload that triggers the delete query.

    Private Sub Form_Load()
    '---Set access levels by role for menu items.---
    Dim sPermit As String
    Dim iAccess As Integer
    Dim sInitials As String
    Dim ctl As Access.control
    Me.txtUser = Environ("username")
    If IsNothing(Me.txtUser) Then
    sPermit = "ReadOnly"
    DoCmd.OpenForm "Staff", acNormal, , , acFormAdd, acDialog

    Else
    sPermit = GetPermission(Me.txtUser)
    sInitials = GetInitials(Me.txtUser)
    End If

    Select Case sPermit
    Case "Admin"
    iAccess = 5
    Case "Lead"
    iAccess = 4
    Case "QA"
    iAccess = 3
    Case "User"
    iAccess = 2
    Case Else
    iAccess = 1
    End Select
    Me.txtLevel = iAccess
    Me.txtInitials = sInitials

    '---Refresh Pending Sort Report subform---
    DoCmd.Requery "qryPendingSrtRprtbyUser subform"

    '---Delete records more than 60 days old---
    DoCmd.OpenQuery "qry60_day_delete"
    DoCmd.OpenQuery "qry60_day_SrtRprt_delete"

    '---determine splash screen to open---
    'If iAccess = 1 Then
    'DoCmd.OpenForm "frmUnauthSplash"
    'Else
    'DoCmd.OpenForm "frmSplash"
    'End If

    End Sub

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,066
    You could simply Google ms access execute (or similar) but I'm feeling generous today
    Will also show you how code tags (forum toolbar #) and indentation code easier to read, and provide example of error handler, which almost all procedures should have. You can also take some shortcuts such as
    Me.txtInitials = GetInitials(Me.txtUser) since you don't use the variable more than once (that I noticed) and that was just to set the form control value anyway.

    Code:
    Private Sub Form_Load()
    '---Set access levels by role for menu items.---
    Dim sPermit As String, sInitials As String
    Dim iAccess As Integer
    Dim ctl As Access.control
    Dim db As DAO.Database
    
    On Error GoTo errHandler
    
    Set db = CurrentDb
    Me.txtUser = Environ("username")
    'If IsNothing(Me.txtUser) Then IsNothing is for Objects. This would likely never true as long as the control exists on the form
    If Me.txtUser & vbNullString = "" Then 'one way of determining no value is there
     sPermit = "ReadOnly"
    DoCmd.OpenForm "Staff", acNormal, , , acFormAdd, acDialog
    
    Else
     sPermit = GetPermission(Me.txtUser)
     sInitials = GetInitials(Me.txtUser)
    End If
    
    Select Case sPermit
     Case "Admin"
      iAccess = 5
     Case "Lead"
      iAccess = 4
     Case "QA"
      iAccess = 3
     Case "User"
      iAccess = 2
     Case Else
      iAccess = 1
    End Select
    Me.txtLevel = iAccess
    Me.txtInitials = sInitials
    
    '---Refresh Pending Sort Report subform---
    DoCmd.Requery "qryPendingSrtRprtbyUser subform"
    
    '---Delete records more than 60 days old---
    'DoCmd.OpenQuery "qry60_day_delete"
    db.Execute "qry60_day_delete", dbFailOnError
    'DoCmd.OpenQuery "qry60_day_SrtRprt_delete"
    db.Execute "qry60_day_SrtRprt_delete", dbFailOnError
    
    '---determine splash screen to open---
    'If iAccess = 1 Then
    'DoCmd.OpenForm "frmUnauthSplash"
    'Else
    'DoCmd.OpenForm "frmSplash"
    'End If
    
    exitHere:
    Set db = Nothing
    Exit Sub
    
    errHandler:
    Msgbox "Error " & err.number & ": " & err.Description
    Resume exitHere
    
    End Sub
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Turn off Action Query confirmations
    By ssissons in forum Queries
    Replies: 2
    Last Post: 10-08-2014, 08:43 AM
  2. bypass parameter
    By Epona in forum Queries
    Replies: 1
    Last Post: 06-09-2012, 05:57 PM
  3. Bypass beforeclose event
    By dsk96m in forum Programming
    Replies: 21
    Last Post: 12-21-2011, 12:12 PM
  4. bypass file picker?
    By techneophyte in forum Programming
    Replies: 6
    Last Post: 08-17-2010, 11:12 AM
  5. Bypass startup to allow design
    By cjbuechler in forum Access
    Replies: 1
    Last Post: 06-25-2010, 03:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums