Data Validation on Exit (SOLVED)

[Michael Abrams]

Simple code for a button to Close form:

Code: Select all

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Before the user exits, I want to ensure certain fields are populated. For purposes of this question, I have only used one field as an example.

I have the following code in the Form_BeforeUpdate

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ANSWER As String
Dim MYSTRING As String
MYSTRING = "YOU MUST FILL IN ALL REQUIRED FIELDS."
ANSWER = MsgBox("Do you want to save the changes you have made to the record?", 36, "CONTINUE?")
 If ANSWER = vbYes Then
        If IsNull([txtDATE_WORKED]) Then                 
            MsgBox MYSTRING, vbExclamation, "DATA ENTRY ERROR"            
           DoCmd.CancelEvent        
 End If
        Else        
           Me.Undo        
        End If
End Sub

The problem is that if the users clicks the cmdExit button and the txtDATE_WORKED field is null, and the user chooses vbYes, the Msgbox pops up and then it stll exits the form.

Is the above code correct for what I am trying to do?

Thank you for any assistance.

Michael

[Pat]

I would try the following:

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ANSWER As String
Dim MYSTRING As String
MYSTRING = "YOU MUST FILL IN ALL REQUIRED FIELDS."
ANSWER = MsgBox("Do you want to save the changes you have made to the record?", 36, "CONTINUE?")
If ANSWER = vbYes Then
        If IsNull([txtDATE_WORKED]) Then                 
            MsgBox MYSTRING, vbExclamation, "DATA ENTRY ERROR"            
            Cancel = True
        End If
End If
End Sub

[Michael Abrams]

Thank you Pat - I tried it and the form still closes.

Michael

[HansV]

You can use code like this:

Code: Select all

Private Sub cmdExit_Click()
    On Error GoTo ExitHere
    If CanClose Then
        DoCmd.Close
    End If
ExitHere:
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ANSWER As Long
    ANSWER = MsgBox("Do you want to save the changes you have made to the record?", _
        vbQuestion + vbYesNo, "CONTINUE?")
    If ANSWER = vbYes Then
        Cancel = Not CanClose
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Cancel = Not CanClose
End Sub

Private Function CanClose() As Boolean
    Dim MYSTRING As String
    MYSTRING = "YOU MUST FILL IN ALL REQUIRED FIELDS."
    CanClose = Not IsNull(Me.txtDATE_WORKED)
    If CanClose = False Then
        Me.txtDATE_WORKED.SetFocus
        MsgBox MYSTRING, vbExclamation, "DATA ENTRY ERROR"
    End If
End Function

[Michael Abrams]

The order of events seems off.
When the user clicks the Exit button, the first action should be: Ask user if saving the record.
If yes, and the field is null, then Msgbox “Field is required” etc and set focus to the field.
In addition – there needs to be the option if they do not want to save the record, undo changes and exit.

Michael

I can attach a small sample db if necessary

[HansV]

I can attach a small sample db if necessary

As you see, Access makes it rather difficult to handle this nicely. A sample database would be useful.

[Michael Abrams]

Thank you so much !

I may as well note now, that there will be multiple fields to be validated similarly.

[HansV]

Take a look at this version. There is a lot of repeated code since we have to take different situations into account.

TRANSACTIONS.zip

[Michael Abrams]

It works great HansV.

Is this normal to repeat code all over the place to validate data, or should I research a more efficient way to do this?

Thank you for taking the time to fix this for me.

Michael

[HansV]

It would probably be possible to make it a bit more efficient, but you'd still need at least two versions, or write more convoluted code.

[Michael Abrams]

OK - so all of the validations work except the Report Week validation.
If the HMO = HN etc, and the Report Week is null, then the code works perfectly.
The focus is set to the report week field and I can enter the report week.
Then if I click the add record again, the msgbox pops up again and sayd report week must be filled in etc.

Seems like it is a loop, but I do not know how to fix it.

Code: Select all

Private Sub cmdAddNewRecord_Click()

  
  If Me.Dirty Then
  

        If MsgBox("Do you want to save the changes you have made to the record?", _
                vbQuestion + vbYesNo, "CONTINUE?") = vbYes Then
            
            'VALIDATE DATE WORKED
            If IsNull(Me.txtDATE_WORKED) Then
                MsgBox "YOU MUST FILL IN DATE WORKED.", vbExclamation, "DATA ENTRY ERROR"
                Me.txtDATE_WORKED.SetFocus
                Exit Sub
            End If
            
            'VALIDATE REP
                If IsNull(Me.cboREP) Then
                    MsgBox "YOU MUST FILL IN REP NAME.", vbExclamation, "DATA ENTRY ERROR"
                    Me.cboREP.SetFocus
                    Exit Sub
                End If
                
             'VALIDATE REPORT NAME
                     If IsNull(Me.cboREPORT_NAME) Then
                        MsgBox "YOU MUST FILL IN REPORT NAME.", vbExclamation, "DATA ENTRY ERROR"
                        Me.cboREPORT_NAME.SetFocus
                        Exit Sub
                    End If
                    
              'VALIDATE CLIENT
                        If IsNull(Me.cboCLIENT) Then
                        MsgBox "YOU MUST FILL IN CLIENT.", vbExclamation, "DATA ENTRY ERROR"
                        Me.cboCLIENT.SetFocus
                        Exit Sub
                    End If
                
                     'VALIDATE REPORT MONTH
                        If IsNull(Me.cboREPORT_MONTH) Then
                            MsgBox "YOU MUST FILL IN REPORT MONTH.", vbExclamation, "DATA ENTRY ERROR"
                            Me.cboREPORT_MONTH.SetFocus
                            Exit Sub
                        End If
                        
                        
                           'VALIDATE REPORT WEEK                          
If cboHMO.Column(0) = "HN" Or cboHMO.Column(0) = "UHW" Or cboHMO.Column(0) = "UHW-ALLIANCE" Or cboHMO.Column(0) = "UHCMS" Or _
                            cboHMO.Column(0) = "C1ST" Or cboHMO.Column(0) = "SCFHP " And IsNull(Me.cboREPORT_WEEK) Then
                                MsgBox "YOU MUST FILL IN REPORT WEEK.", vbExclamation, "DATA ENTRY ERROR"
                                Me.cboREPORT_WEEK.SetFocus
                                Exit Sub
                            End If
                                
                            
                
            If Me.txtTOTAL_TRANSACTIONS2 <> Me.txtTOTAL_TRANSACTIONS Then
                Me.txtTOTAL_TRANSACTIONS.SetFocus
                MsgBox "TRANSACTION TOTAL IS INCORRECT - PLEASE CHECK YOUR TRANSACTION COUNTS", vbCritical, "DATA ENTRY ERROR"
                Exit Sub
            End If
            
        End If
        
        Else
            Me.Undo
        
   End If

    DoCmd.GoToRecord , , acNewRec
    
     Exit_cmdAddNewRecord_Click:
    Exit Sub

Err_cmdAddNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNewRecord_Click
    
End Sub

[HansV]

The HMO combo box is disabled, so I cannot select anything in it. If I enable it, the list is empty. So I added some records to the HMO table, among which HN. I then get the error message that the week combo box is disabled, etc. etc.
Could you attach a working database?

[Michael Abrams]

Sorry about that...

here it is.

[HansV]

And has precedence above Or, so you must enclose the Or part in parentheses:

Code: Select all

            If (cboHMO.Column(0) = "HN" Or cboHMO.Column(0) = "UHW" Or cboHMO.Column(0) = "UHW-ALLIANCE" Or cboHMO.Column(0) = "UHCMS" Or _
                    cboHMO.Column(0) = "C1ST" Or cboHMO.Column(0) = "SCFHP ") And IsNull(Me.cboREPORT_WEEK) Then

[Michael Abrams]

Those darn parentheses...

Thank you HansV.

Much appreciated.

Michael

[Michael Abrams]

If the user 'changes' something after any validation but before saving, the code is bypassed.

Example:
If they select HN (for example) the Report Week is enabled. If they forget to enter a date, the Data validation works fine.
But
If they select something other than HN,UHW etc - and leave out the month, for example, the user receives the message to fill in the month. This works fine. But if they realize the HMO should be HN and change it, the Report Week becomes enabled (which is OK) and if the user tries to add a record or exit, they are allowed to without validation of the report week. It bypasses the code.

I hope I am explaining this OK. I imagine it has to do with "If Me.Dirty Then"....

Thank you for looking again !

Michael
**Edited to add:

If user chooses vbNo, Me.Undo code runs?
I ask because when user chose Save Changes? No - a record was created and mainatained the few data elements entered.

[HansV]

Hi Michael, I'm away from home at the moment, I'll look into it when I get back (Sunday).

[HansV]

In the code you have shown me, there is no check on report month. You'll have to add a check for that, as well as for all other conditions that should be met.

[Michael Abrams]

Thanks Hans.

I will zip up a good sample in the morning

Michael

*EDIT: Now I cannot replicate the issue !

If it occurs again I will repost. Thank you for you continued assistance !