Solved Validation Rules on Form Close/Click on Previous record

[ahmad_rmh]

I have applied validation rule on before update event but I am getting compile error kindly check and suggest please. I have added the code and screenshot.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
        If CheckForEmpty = False Then
            MsgBox "Please fill in the coloured fields"
        Else
            MsgBox "Do Action"
        End If
    End If

End Sub

Function CheckForEmpty() As Boolean
   
    CheckForEmpty = True
    ClearControlFormatting
   
    Dim Ctrl As Control
   
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            If IsNull(Ctrl) Or Len(Ctrl) = 0 Then
                Ctrl.BackColor = RGB(153, 204, 255)
                CheckForEmpty = False
            End If
        End If
    Next
End Function

Sub ClearControlFormatting()
    Dim Ctrl As Control
   
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            Ctrl.backcolor = vbWhite
        End If
    Next
End Sub

Private Sub Form_Current()
    ClearControlFormatting
End Sub

It is showing compile error on "=" sign on CheckForEmpty in before update event.

Kindly suggest

 

[NauticalGent]

I have applied validation rule on before update event but I am getting compile error kindly check and suggest please. I have added the code and screenshot.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
        If CheckForEmpty = False Then
            MsgBox "Please fill in the coloured fields"
        Else
            MsgBox "Do Action"
        End If
    End If

End Sub

Function CheckForEmpty() As Boolean()
   
    CheckForEmpty = True
    ClearControlFormatting
   
    Dim Ctrl As Control
   
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            If IsNull(Ctrl) Or Len(Ctrl) = 0 Then
                Ctrl.BackColor = RGB(153, 204, 255)
                CheckForEmpty = False
            End If
        End If
    Next
End Function

Sub ClearControlFormatting()
    Dim Ctrl As Control
   
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            Ctrl.baccolor = vbWhite
        End If
    Next
End Sub

Private Sub Form_Current()
    ClearControlFormatting
End Sub

It is showing compile error on "=" sign on CheckForEmpty in before update event.

Kindly suggest

Good morning. @GinaWhipp has the best Form Validation sub I have ever seen. Place her code in your BeforeUpdate event and your life will be much easier!

Validate Data

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[jdraw]

I would start by removing the brackets after Boolean() in your CheckForEmpty() function.

 

[ahmad_rmh]

I would start by removing the brackets after Boolean() in your CheckForEmpty() function.

Thanks @jdraw, i have removed () now no error but the form is not showing any response on blank fields. Kindly suggest.

@NauticalGent , this is beyond my scope, may be this would work, thanks

 

[NauticalGent]

@NauticalGent , this is beyond my scope, may be this would work, thanks

Really nothing to it. You would place the code in a standard module (outside of any of your forms), then in each of your forms' BeforeUpdate event you would call it with

If fValidateData Then
     Cancel = True
     Exit Sub
End If

 

[ahmad_rmh]

I will try

Really nothing to it. You would place the code in a standard module (outside of any of your forms), then in each of your forms' BeforeUpdate event you would call it with

If fValidateData Then
     Cancel = True
     Exit Sub
End If

I have tried this, this works but as you get notification of blank fields it freezes the form and records, and even after filling again the required fields it does not unfreezes the form fields.

 

[ahmad_rmh]

I have applied validation rule on before update event but I am getting compile error kindly check and suggest please. I have added the code and screenshot.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
        If CheckForEmpty = False Then
            MsgBox "Please fill in the coloured fields"
        Else
            MsgBox "Do Action"
        End If
    End If

End Sub

Function CheckForEmpty() As Boolean
  
    CheckForEmpty = True
    ClearControlFormatting
  
    Dim Ctrl As Control
  
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            If IsNull(Ctrl) Or Len(Ctrl) = 0 Then
                Ctrl.BackColor = RGB(153, 204, 255)
                CheckForEmpty = False
            End If
        End If
    Next
End Function

Sub ClearControlFormatting()
    Dim Ctrl As Control
  
    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            Ctrl.backcolor = vbWhite
        End If
    Next
End Sub

Private Sub Form_Current()
    ClearControlFormatting
End Sub

It is showing compile error on "=" sign on CheckForEmpty in before update event.

Kindly suggest

My code is working on command button but how i will implement on before update event, kindly suggest

 

[ahmad_rmh]

There is no such thing as a "blank". There are Null and ZeroLengthStrings . Access separates the meanings. There is also space. But "blank" has no meaning.

@ahmad_rmh You MUST cancel the event in order to prevent Access from saving the record and the cancel = True must go in the form's BeforeUpdate event using the code you posted.

Thanks Pat,

 

[ahmad_rmh]

Other Issue:

On open my subform is on disabled condition as under

If Forms!frmTransactionsMain!lblTransfer.Visible = True Then
    Me.frmTransactionsSub.Enabled = False
End if

but after validating the data before update event tab is not moving to sub form but it's moving on 2nd record of MainForm.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
        If CheckForEmpty = False Then
            MsgBox "Please fill in the coloured fields"
            Cancel = True
            Exit Sub
        End If
    End If
End Sub

Function CheckForEmpty() As Boolean

    CheckForEmpty = True
    ClearControlFormatting

    Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            If IsNull(Ctrl) Or Len(Ctrl) = 0 Then
                Ctrl.BackColor = RGB(153, 204, 255)
                CheckForEmpty = False
            End If
        End If
    Next
End Function

Sub ClearControlFormatting()
    Dim Ctrl As Control

    For Each Ctrl In Me.Controls
        If Ctrl.Tag = "Fill" Then
            Ctrl.BackColor = vbWhite
        End If
    Next
End Sub

Private Sub Form_Current()
    ClearControlFormatting
End Sub

Private Sub Form_AfterUpdate()
    Me.Refresh
    If Forms!frmTransactionsMain!lblReceipt.Visible = True Then
            Me.frmTransactionsSub.Enabled = True
            Me.frmTransactionsSub!ItemsFK.SetFocus
    End If
End Sub

kindly suggest, thanks

 

[Minty]

The before update event works when you are leaving the record on that form, if you cancel that in the validation code, then that form's After Update event won't be triggered.

 

[ahmad_rmh]

T

The before update event works when you are leaving the record on that form, if you cancel that in the validation code, then that form's After Update event won't be triggered.

Thanks Minty, I have changed to before Insert event, now it's working.

 

[ahmad_rmh]

As I alluded to in the video, you do NOT need to lock the subform to prevent entry. You can simply trap it in the BeforeUpdate event. Like the BeforeUpdate event, this event ALWAYS works and it allows you to put code into ONE vent rather than several which makes the whole process simpler.

Study the form events. Understand what they are used for. The MS documentation is spotty at best so you'll have to wait for future videos from me or find explanations in the forums.

Thanks a lot Pat, I was also thinking to make it more simpler and I will try to make it more simpler as you have suggested now and will update you. You know I am just a beginner and learner so it takes time to get the results.