Solved Need help with before update code logic?

[Mike Krailo]

I'm trying to do a simple textbox validation to test if the description field is filled out in a very simple test form. This works when using simple code logic, but if using my close and save button, the form closes before there is a chance of correcting the problem on the form. So then I put in logic that first tests if the form is dirty and simply saves the data without closing the form (see below code). This got me farther as now the form doesn't close, but now I have to click the close and save button once to do the save, and once more to close the form.

What is the proper logic to get the desired results of validating many possible form fields and also allowing the user to make corrections before clicking the close and save button once again? Current sample file is attached if you want to play with it. There are two different forms that each show the two different problem scenarios. In each case, leaving the description field blank and entering a current value and attempting to use close and save will demonstrate the issue.

Private Sub CloseBtn_Click()
   On Error GoTo ErrorHandler
   If Me.Dirty = True Then
      ' force save and trigger before update without closing form yet.
      Me.Dirty = False
   Else
      DoCmd.Close acForm, Me.Name
   End If
   Exit Sub

ErrorHandler:
   Select Case Err.Number
      Case 2101 'The setting you entered isn't valid for this property
         Resume Next
      Case Else
         MsgBox "Error " _
            & Err.Number & ": " _
            & Err.Description, vbExclamation, "There is an ERROR in CloseBtn_Click"
         Resume Next
   End Select
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If IsNull(Me.InvDescription) Then
      MsgBox "Must provide description"
      Cancel = True
      Me.InvDescription.SetFocus
   End If
   ' Many more validation code blocks to follow here
End Sub

 

[tvanstiphout]

The zero-code solution is to make that field required in the table. That is also the best idea from a database design point of view.

 

[Mike Krailo]

The zero-code solution is to make that field required in the table. That is also the best idea from a database design point of view.

No, that's not what I'm looking for as I am aware of that solution. I will have many more complicated validations to contend with.

 

[Mark_]

Can you please describe what is going on for the form that would prevent you simply making it required? Without knowing what the scope of your solution needs to be, you'll get a bunch of solutions that won't work for your situation.

 

[Josef P.]

Is it bad if the test is carried out twice?

Private Sub CancelBtn_Click()
   If Me.Dirty Then
      Me.Undo
   End If
   DoCmd.Close acForm, Me.Name
End Sub

Private Sub CloseBtn_Click()

   If Me.Dirty Then
      If Not IsValidData Then
         Exit Sub
      End If
   End If
  
   DoCmd.Close acForm, Me.Name
  
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
   If Not IsValidData() Then
      Cancel = True
   End If
 
End Sub

Private Function IsValidData() As Boolean

   If IsNull(Me.InvDescription) Then
      MsgBox "Must provide description"
      Me.InvDescription.SetFocus
      IsValidData = False
      Exit Function
   End If
 
   '.... other checks

   IsValidData = True

End Function

 

[MajP]

Private Sub CancelBtn_Click()
   If Me.Dirty Then Me.Undo
   DoCmd.Close acForm, Me.Name
End Sub

Private Sub CloseBtn_Click()
   On Error GoTo ErrorHandler

      If IsValid Then DoCmd.Close acForm, Me.Name

   Exit Sub

ErrorHandler:
   Select Case Err.Number
      Case 2101 'The setting you entered isn't valid for this property
         Resume Next
      Case Else
         MsgBox "Error " _
            & Err.Number & ": " _
            & Err.Description, vbExclamation, "There is an ERROR in CloseBtn_Click"
         Resume Next
   End Select
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

   If IsValid Then
      Cancel = True
   End If
End Sub

Public Function IsValid() As Boolean
  Dim Msg As String
  IsValid = True
  Msg = "Please Fix the Following:"
  If IsNull(Me.InvDescription) Then
    Msg = Msg & vbCrLf & "Must Provide a Description"
    IsValid = False
  End If
  'Other checks
  If Me.CurrentValue < 0 Then
    IsValid = False
    Msg = Msg & vbCrLf & "Current value must be positive"
  End If
  'more checks
  If IsValid = False Then MsgBox Msg, vbCritical, "Fix"
End Function

I think the exact same idea as Josef. I set it up to show how to make one message for a group of issues.

 

[Mike Krailo]

That's what I was looking for @MajP. I see that checking for all the validations in a separate function and only if everything passes, then close the form. I think Josef P.'s idea would work as well.

 

[MajP]

. I think Josef P.'s idea would work as well.

I think we did the exact same thing. I just added examples of other checks.
If you are just validating a bunch of required fields I use a version of this. It marks all the required fields red until you fix them. It can be used on any form. If you have more specific rules you would have to write a form specific function.

Solved - Form Validation using .Tag with highlighted controls on close

Hello, I am trying to validate controls on y form that if their .tag is "Required" and they are null or =0 then highlight the border red and if there is data stay the same. I found a piece of code years ago and am trying to implement it but cant figure out what to change for the below revision...

www.access-programmers.co.uk

 

[MajP]

I actually made a demo using that code showing a way to validate many controls are filled in.

 

[Pat Hartman]

The validation code belongs in the form's BeforeUpdate event so it is executed regardless of what prompted the save.

To solve the problem of not allowing the form to close if an error is detected, I use a global variable or Tempvar. It is set to false as the first statement of the BeforeUpdate event. Then the validation logic is executed. It may be in the BeforeUpdate event or if it is used from several forms, it is called from the BeforeUpdate event. The logic generally raises an error message, sets focus to the field in question, sets Cancel to true and exits for any error. Therefore, if you get to the end of the BeforeUpdate event, no errors were found and so you can change the value to true. Then in the form's unload event, you check the variable and either allow the form to unload or prevent it.

If you spread out your validation logic so that some is in the control's BeforeUpdate events, you just have more places to check/set the variable value.

 

[Mike Krailo]

The validation code belongs in the form's BeforeUpdate event so it is executed regardless of what prompted the save.

To solve the problem of not allowing the form to close if an error is detected, I use a global variable or Tempvar. It is set to false as the first statement of the BeforeUpdate event. Then the validation logic is executed. It may be in the BeforeUpdate event or if it is used from several forms, it is called from the BeforeUpdate event. The logic generally raises an error message, sets focus to the field in question, sets Cancel to true and exits for any error. Therefore, if you get to the end of the BeforeUpdate event, no errors were found and so you can change the value to true. Then in the form's unload event, you check the variable and either allow the form to unload or prevent it.

If you spread out your validation logic so that some is in the control's BeforeUpdate events, you just have more places to check/set the variable value.

That makes sense Pat. I just never seen the code for this logic before. The thought of using a flag like variable crossed my mind, but I just wanted to see what my options were.

 

[Pat Hartman]

You've been given three different methods. The one you choose will depend on if you have other considerations. I think the method I use with the variable is the simplest. It is two lines of code in the BeforeUpdate event and an If statement in the unload event to determine whether to allow the form to unload. And the Dim statement is in a general module so it is accessible from all forms or if you use a TempVar, Dim it in the login form or whatever form is the first form that gets used in the app.

These two variables are always in a module named modPublicVariables. The QUOTE one is so you don't go blind counting quotes when you are creating SQL strings. It lets you use the word QUOTE so that it is obvious that the quotes are embedded in the string rather than delimiting the string.

If DCount("*", "tblAuditParms", "CoAbbr = " & QUOTE & Me.CoAbbr & QUOTE) > 0 Then

Option Compare Database
Option Explicit

Public Const QUOTE = """"
Public gPreventClose As Boolean

 

[Mike Krailo]

Knowing how you approach this exact issue Pat is very helpful. Thank you.