Before Update Event

[data808]

I'm trying to clean up a Before Update event so it prompts the user whenever the record is left in a form. When I say "left" I mean go to another record by clicking some buttons to go to first, next, previous, or last records. This also includes closing the form and opening another form. Right now I seem to have it working for the most part except for when the user tries to leave when the required subject field is not filled out. Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)    
If Me.txtClerk <> Me.txtUpdatedBy Then
    Beep
    MsgBox "You Are Not Authorized To Edit This Record.", vbCritical, "Unauthorized Access"
    MsgBox "Changes Will Not Be Saved.", vbCritical, "Notification"
    DoCmd.RunCommand acCmdUndo
    Exit Sub
End If


If Me.NewRecord And Me.Dirty Then
    If MsgBox("Save New Record?", vbYesNo, "Save Record") = vbYes Then
        If IsNull(Me.txtTimeOfService) Then
            Me.txtTimeOfService = Time()
        End If
        If IsNull(Me.txtDateOfService) Then
            Me.txtDateOfService = Date
        End If
        If IsNull(Me.cmbSubject) Then
            MsgBox "Please Enter A Subject.", vbInformation, "Requirements"
            DoCmd.CancelEvent
            Exit Sub
        End If
        Exit Sub
    Else
        Me.Undo
    End If
End If


If Me.Dirty Then
    If MsgBox("Save Changes To Record?", vbYesNo, "Save Record") = vbYes Then
        If IsNull(Me.txtTimeOfService) Then
            Me.txtTimeOfService = Time()
        End If
        If IsNull(Me.txtDateOfService) Then
            Me.txtDateOfService = Date
        End If
        If IsNull(Me.cmbSubject) Then
            MsgBox "Please Enter A Subject.", vbInformation, "Requirements"
            DoCmd.CancelEvent
            Exit Sub
        End If
        Exit Sub
    Else
        Me.Undo
    End If
End If


End Sub

So when the user clicks the previous record button, for example, the error that occurs is "Run-Time error '2105': You can't go to the specified record." and I think the reason why this is happening is because it has to pause or cancel the event because it needs the user to fill in the Subject field, but it's not canceling it and still trying to go to the previous record. This is why I put in the Docmd.CancelEvent which doesn't seem to be doing what I thought it would, which is cancel this whole event sequence. The code behind the previous record button just so you can see the whole picture is:

Private Sub cmdPrevious_Click()

DoCmd.GoToRecord , , acPrevious
Me.txtCustNum.SetFocus


End Sub

Could someone help with this?

Thank you.

[June7]

If you want to abort the Update then need to use Cancel = True. https://learn.microsoft.com/en-us/of...reUpdate-event

[data808]

If you want to abort the Update then need to use Cancel = True. https://learn.microsoft.com/en-us/of...reUpdate-event

Thanks @June7 but that did not work and I think it's because I am not trying to cancel the before update event but rather the previous button click event. So basically user clicks previous record button and if the form is dirty and subject field is not filled out, it will stop the before update event because it will prompt the user to please enter a subject. However, it does not stop the previous button click which is to go to the previous record and that is what the error kind of states.

[orange]

data808,

Have you identified what the process steps are/should be for entering a record?

Perhaps, once the user starts to enter data, all required fields should be completed and validated. If the user should not be jumping to other records, then perhaps those options should be disabled until the record has been entered and validated. You know the logic better than readers, so you really need code to support the logic involved.

You will have fewer errors if you don't allow (disable) some options until another process has been completed. You may have to rethink the process in conjunction with the users involved.

Just some thoughts for consideration.

For validation see Gina Whipp fValidateData for insight.

[June7]

Don't think I have ever used form Form_BeforeUpdate event. I also use custom buttons for navigation and saving/deleting record. I disable form X close and navigation bar. I would have code in button click event to check if control has input and then proceed accordingly. Have a Sub or Function that can be called by multiple buttons.

[Micron]

Learn how to step through code because it will help you. I'd say you're almost correct.
Your click event attempts to go to a new record.
That triggers the BeforeUpdate event code.
You cancel the update, control passes back to the calling sub.
That sub attempts to set focus to a control, but this chain of events raises an error. Access reports that you cannot go to the record because of the cancellation. Not saying that makes a whole lot of sense.

The usual fix is to trap the error. This is the same issue as cancelling a form Open event. The code that opens the form will raise error 2501 for the same basic reason if you cancel the form opening.

[June7]

Alternative to trapping error could be to declare a Public variable in form: Public booValid As Boolean

Code in BeforeUpdate sets booValid to True if validation conditions are met.

Button click event tests for booValid:

If booValid Then 'do something like move to another record.

[data808]

data808,

Have you identified what the process steps are/should be for entering a record?

Perhaps, once the user starts to enter data, all required fields should be completed and validated. If the user should not be jumping to other records, then perhaps those options should be disabled until the record has been entered and validated. You know the logic better than readers, so you really need code to support the logic involved.

You will have fewer errors if you don't allow (disable) some options until another process has been completed. You may have to rethink the process in conjunction with the users involved.

Just some thoughts for consideration.

For validation see Gina Whipp fValidateData for insight.

Thanks for the ideas. I like the disabled options idea. That may come in handy in the future. Will keep that in mind.

[data808]

Don't think I have ever used form Form_BeforeUpdate event. I also use custom buttons for navigation and saving/deleting record. I disable form X close and navigation bar. I would have code in button click event to check if control has input and then proceed accordingly. Have a Sub or Function that can be called by multiple buttons.

I think I agree with you on not using the before update event. The only reason why I do is because I am trying to take care of the scenario when the user closes the app by right clicking the Access icon in the taskbar which closes everything and saves it automatically. So by doing some code for the before update event, this can handle prompts to ask the user before it closes out, if they want to save the changes to a record or not.

If there is a way to stop/cancel the before update event if the user actually uses all of the buttons in the form, that would solve this problem. I could do all the code behind every button to handle everything and only if the user closes the app from the taskbar, then have the before update event get triggered to ask if they want to save any changes or not. Is this possible?

If not, I been messing around did some more changes. Right not I am only using the previous record button for testing and I also started changing some of the code for the save button. The before update event has been changed as well:

Private Sub Form_BeforeUpdate(Cancel As Integer)    
If Me.txtClerk <> Me.txtUpdatedBy Then
    Beep
    MsgBox "You Are Not Authorized To Edit This Record.", vbCritical, "Unauthorized Access"
    MsgBox "Changes Will Not Be Saved.", vbCritical, "Notification"
    DoCmd.RunCommand acCmdUndo
    Exit Sub
End If


If IsNull(Me.cmbSubject) Then
    MsgBox "Please Enter A Subject.", vbInformation, "Requirements"
    Me.cmbSubject.SetFocus
    Exit Sub
End If


If Me.NewRecord And Me.Dirty Then
    If MsgBox("Save New Record?", vbYesNo, "Save Record") = vbYes Then
        If IsNull(Me.txtTimeOfService) Then
            Me.txtTimeOfService = Time()
        End If
        If IsNull(Me.txtDateOfService) Then
            Me.txtDateOfService = Date
        End If
        DoCmd.GoToRecord , , acNewRec
        Exit Sub
    Else
        Me.Undo
    End If
End If


If Me.Dirty Then
    If MsgBox("Save Changes To Record?", vbYesNo, "Save Record") = vbYes Then
        If IsNull(Me.txtTimeOfService) Then
            Me.txtTimeOfService = Time()
        End If
        If IsNull(Me.txtDateOfService) Then
            Me.txtDateOfService = Date
        End If
        Exit Sub
    Else
        Me.Undo
    End If
End If
End Sub

Private Sub cmdPrevious_Click()


If Me.NewRecord And Me.Dirty = False Then GoTo SkipToHere


If IsNull(Me.cmbSubject) Then
    MsgBox "Please Enter A Subject.", vbInformation, "Requirements"
    Me.cmbSubject.SetFocus
    Exit Sub
End If


SkipToHere:
DoCmd.GoToRecord , , acPrevious
Me.txtCustNum.SetFocus


End Sub

Private Sub cmdSave_Click()


If Me.Dirty = False Then
    MsgBox "No Changes Were Made.", vbInformation, "Notification"
    Me.txtCustNum.SetFocus
    Exit Sub
End If
    
If Me.NewRecord And Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec
    Me.txtCustNum.SetFocus
End If
    
If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
    Me.txtCustNum.SetFocus
End If

End Sub

Will be back to report what issues I get with this setup. I did a little testing earlier and one of the scenarios wasn't working.

[data808]

Ok so far one scenario I do not like is that when the user clicks the save button on a record that is dirty, it will ask if they want to save the changes to the record. If the user clicks no, it does an Me.Undo. I would rather have it to do nothing if they click no because they are NOT leaving the record. I know why this is happening and its because of the Me.Undo in the before update event but I need that there because if the user closes by taskbar, it will ask them before it closes out if they want to save the changes and if they click no then it will undo what they changed. Does that make sense?

[June7]

Try using Cancel = True instead of UNDO in the BeforeUpdate.

I think I agree with you on not using the before update event. The only reason why I do is because I am trying to take care of the scenario when the user closes the app by right clicking the Access icon in the taskbar which closes everything and saves it automatically.

I have customized the ribbon and also have a module to disable icon right click and upper right X close action. However, have not been able to make File>Exit ribbon item not available as described in https://answers.microsoft.com/en-us/...4-9f8f87b9e808. Note that pressing Shift key while opening database will bypass this customization.

Option Compare Database
Option Explicit
'API function to disable application X Close button and toolbar Close
Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal wRevert As Long) As Long
Private Declare PtrSafe Function EnableMenuItem Lib "user32" (ByVal hMenu As Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long

Sub OnRibbonLoad(ribbon As IRibbonUI)
    ' procedure called by custom ribbon code in QATRibbon record of USysRibbons table
    ' QATRibbon is set as the default ribbon when database opens
    DoCmd.OpenForm "Login"
End Sub

Public Sub SetAccessXCloseButton(pfEnabled As Boolean)
' Comments: Control the Access X close button.
'           Disabling it forces the user to exit within the application
' In      : pfEnabled       TRUE enables the close button, FALSE disables it
' Owner   : Copyright (c) 2008-2009 from FMS, Inc.
' Source  : Total Visual SourceBook
' Usage   : Permission granted to subscribers of the FMS Newsletter

On Error Resume Next

Const clngMF_ByCommand As Long = &H0&
Const clngMF_Grayed As Long = &H1&
Const clngSC_Close As Long = &HF060&

Dim lngWindow As Long
Dim lngMenu As Long
Dim lngFlags As Long

lngWindow = Application.hWndAccessApp
lngMenu = GetSystemMenu(lngWindow, 0)
If pfEnabled Then
  lngFlags = clngMF_ByCommand And Not clngMF_Grayed
Else
  lngFlags = clngMF_ByCommand Or clngMF_Grayed
End If
Call EnableMenuItem(lngMenu, clngSC_Close, lngFlags)

End Sub

[data808]

Try using Cancel = True instead of UNDO in the BeforeUpdate.


I have customized the ribbon and also have a module to disable icon right click and upper right X close action. However, have not been able to make File>Exit ribbon item not available as described in https://answers.microsoft.com/en-us/...4-9f8f87b9e808. Note that pressing Shift key while opening database will bypass this customization.

Option Compare Database
Option Explicit
'API function to disable application X Close button and toolbar Close
Private Declare PtrSafe Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal wRevert As Long) As Long
Private Declare PtrSafe Function EnableMenuItem Lib "user32" (ByVal hMenu As Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long

Sub OnRibbonLoad(ribbon As IRibbonUI)
    ' procedure called by custom ribbon code in QATRibbon record of USysRibbons table
    ' QATRibbon is set as the default ribbon when database opens
    DoCmd.OpenForm "Login"
End Sub

Public Sub SetAccessXCloseButton(pfEnabled As Boolean)
' Comments: Control the Access X close button.
'           Disabling it forces the user to exit within the application
' In      : pfEnabled       TRUE enables the close button, FALSE disables it
' Owner   : Copyright (c) 2008-2009 from FMS, Inc.
' Source  : Total Visual SourceBook
' Usage   : Permission granted to subscribers of the FMS Newsletter

On Error Resume Next

Const clngMF_ByCommand As Long = &H0&
Const clngMF_Grayed As Long = &H1&
Const clngSC_Close As Long = &HF060&

Dim lngWindow As Long
Dim lngMenu As Long
Dim lngFlags As Long

lngWindow = Application.hWndAccessApp
lngMenu = GetSystemMenu(lngWindow, 0)
If pfEnabled Then
  lngFlags = clngMF_ByCommand And Not clngMF_Grayed
Else
  lngFlags = clngMF_ByCommand Or clngMF_Grayed
End If
Call EnableMenuItem(lngMenu, clngSC_Close, lngFlags)

End Sub

I'll try the Cancel = True.

I have the entire ribbon hidden and locked away, the close X, minimize, maximize at the top right of window hidden, the shift key is also disabled so they can't override any of my restrictions by holding it down before they open the app, I have certain other F keys and anything else disabled that could give access to unlock anything, also have the record selector hidden, etc... so I'm pretty sure I am covered in that regard. The only thing I cannot prevent is the user right clicking the Access icon in the taskbar and closing the window. This is the source of the problem. If there is some kind of VBA code that can tell when the user does this taskbar closing then I can make a custom prompt that will pop up before it closes out. But I would only want this custom prompt to trigger if the app is closed by taskbar and not by my logout or quit buttons that I have scattered across my DB.

[June7]

As I said, code I posted disables taskbar Close.

[data808]

As I said, code I posted disables taskbar Close.

Ok thanks @June7. Do I just paste the code into an existing module that I already have? Or create a new one?

[data808]

As I said, code I posted disables taskbar Close.

I created a new module and pasted your code. Then closed the app to restart it. Then I opened it back up and right clicked the taskbar and close the app and it closed. Am I missing something?

I also realized that your code mentions disabling of the toolbar closing (not taskbar). Was that a mistake? Then I looked further down the code and it mentions the ribbon and the X close button but could not find anything that seem to be associated with the taskbar. Taskbar is usually at the bottom of you monitor. Here is where you can see if you have an Access window open. The user will sometimes right click on the Access icon that is showing on the taskbar and it will give a small menu of options. One of those options is "Close Window".