Before Update vs. Form Close (1 Viewer)

[accessuser1023]

All,

I have code running behind an image on a form that is used to close it. I also have code running behind the Before_Update() event. My code behind the Before_Update() event was meant to address the user navigating to a new record via the nav bar arrow button at the bottom only. Data is saved automatically for changes in records that have already been entered, so there is no need to code that. Here is my issue:

I have a routine that checks for mandatory field entries for every form I have if it is in add / data entry mode. It is called on every form by the Before_Update() event. However, if a user tries to close a form when entering a new record (say they change their mind and want to cancel the entry) and they press the close image, the Before_Update() runs in addition to the code I have behind the close image.

I don't know how to solve the issue, as I am getting warning messages that I coded that are from both routines. Would anyone have a solution to this by chance?

Comments certainly welcome here. I guess I didn't think this one through.

 

[Roku]

Hi
You could try checking the Form.Dirty property when closing the DB. I'm not sure where to put this from your description, but the process steps would be:
In the Application Close procedure, set a flag e.g. 'flgClosing'.
In your other events, test the setting of the flag and the form's Dirty property and if both are true, use the Undo method on the form.

If flgClosing And Me.Dirty Then Me.Undo

where Me is the form running the code.
If this doesn't help, please provide more detail on the forms and sequence of events you want to follow.

 

[accessuser1023]

Hi
You could try checking the Form.Dirty property when closing the DB. I'm not sure where to put this from your description, but the process steps would be:
In the Application Close procedure, set a flag e.g. 'flgClosing'.
In your other events, test the setting of the flag and the form's Dirty property and if both are true, use the Undo method on the form.

If flgClosing And Me.Dirty Then Me.Undo

where Me is the form running the code.
If this doesn't help, please provide more detail on the forms and sequence of events you want to follow.

The unbelievably long code that I've written is below. This is very pathetic, simply because VB is such a demanding language. There is literally no other way to do this!

  If closeForm Then 'CLOSE BUTTON pressed
    If fType = create Then 'CREATE form
      If Me.NewRecord Then 'NEW record (close button pressed)
        If Me.Dirty Then
          If Not RequiredFieldsOK(Me.Name, ReqFields_frmReferrers) Then
            If MsgBox("All required fields need to" & vbCrLf & _
                      "be filled in for new records." & vbCrLf & vbCrLf & _
                      "Close the form and cancel the entry?", vbExclamation + vbYesNoCancel, "Error") = vbYes Then
              Me.Undo
              Cancel = True
              closeForm = False
              DoCmd.Close acForm, Me.Name, acSaveNo
            End If
          Else
            '(CHECK FOR DATA VALIDITY, IF NECESSARY)
            closeForm = False
            DoCmd.Close
          End If
        Else
          closeForm = False
          DoCmd.Close acForm, Me.Name, acSaveNo
        End If
      Else 'NOT new record (close button pressed)
        If Me.Dirty Then
          If Not RequiredFieldsOK(Me.Name, ReqFields_frmReferrers) Then
            MsgBox "All required fields need to be filled in.", vbCritical, "Error"
            Cancel = True
          Else
            '(CHECK FOR DATA VALIDITY, IF NECESSARY)
            closeForm = False
            DoCmd.Close
          End If
        Else
          closeForm = False
          DoCmd.Close acForm, Me.Name, acSaveNo
        End If
      End If
    Else 'AMEND form
      If Me.Dirty Then
        If Not RequiredFieldsOK(Me.Name, ReqFields_frmReferrers) Then
          MsgBox "All required fields need to be filled in.", vbCritical, "Error"
          Cancel = True
        Else
          '(CHECK FOR DATA VALIDITY, IF NECESSARY)
          closeForm = False
          DoCmd.Close
        End If
      Else
        DoCmd.Close acForm, Me.Name, acSaveNo
      End If
    End If
  Else 'NEW RECORD BUTTON pressed
    If fType = create Then 'CREATE form
      If Me.NewRecord Then 'NEW record
        If Not RequiredFieldsOK(Me.Name, ReqFields_frmReferrers) Then
          MsgBox "All required fields need to" & vbCrLf & _
                 "be filled in for new records.", vbCritical, "Error"
          Cancel = True
        Else
          '(CHECK FOR DATA VALIDITY, IF NECESSARY)
          closeForm = False
        End If
      Else 'NOT NEW record
        If Me.Dirty Then
          If Not RequiredFieldsOK(Me.Name, ReqFields_frmReferrers) Then 'NOT NEW record
            MsgBox "All required fields need to be filled in.", vbCritical, "Error"
            Cancel = True
          Else
            '(CHECK FOR DATA VALIDITY, IF NECESSARY)
            closeForm = False
          End If
        End If
      End If
    Else 'AMEND form
      If Me.Dirty Then
        If Not RequiredFieldsOK(Me.Name, ReqFields_frmReferrers) Then
          MsgBox "All required fields need to be filled in.", vbCritical, "Error"
          Cancel = True
        Else
          '(CHECK FOR DATA VALIDITY, IF NECESSARY)
          closeForm = False
        End If
      End If
    End If
  End If

 

[Roku]

The unbelievably long code that I've written is below. This is very pathetic, simply because VB is such a demanding language. There is literally no other way to do this!

IMHO there is always another way to do things in code
From a quick scan, it appears that your code block is trying to handle multiple forms without reference to the correct context. Am I correct in thinking you have several forms open and you have this code replicated in each? I see 'fType' with comments, but I don't get the context. Where is fType declared and how is it set?

As an aside, if you really do need these cascading 'If' statements, I'd suggest using Select Case instead. That allows you to separate groups of statements in a more structured manner. However, I recognise that this is not your root problem.
The other point I'd make is that code which is repeated for different objects can be put in a subroutine with parameters to select the specific object of interest (Form, Report, Control etc.).

Back to your problem ...
I notice you are checking both NewRecord and Dirty. I've not checked this, but I suspect NewRecord is always dirty, so checking both is superfluous. It may even be that Dirty is not set when NewRecord is set (I would need to test this). My rule of thumb is that 'Dirty' is essentially used when editing an existing record.

Re-reading your original post, I infer that the code you have supplied is called from each form's BeforeUpdate event - is my understanding correct? I'm thinking that 'Me' might not be pointing where you expect, thus giving the wrong results. If you can give a little more detail on where the code block sits and how it's called, it would help. Also, clarify how many instances of the code are used?

 

[accessuser1023]

Roku,

Everything that you said is valid, unfortunately though it shows that you have not done the maximum amount of research possible. For instance, new records are NOT always dirty. If you're a good developer, yes they would be. But that doesn't necessary mean this is the case.

My code only covers one open form at a time. So that was also incorrect. The code is written as all-encompassing. It's probably the best way to do it, given the design I have. Thanks anyway.

Also, if you look closely you'll realize that I'm working with a customer that is kind of a perfectionist. Thus, I need to cover every single possibly that could arise so they don't freak out and say something stupid (as happens quite a lot). For instance, an error message appears so they think the world ends.