Solved Validation Rules on Form Close/Click on Previous record

[Uncle Gizmo]

Has she ever created Youtube content? Good voice, knowledgeable and experienced.

Exactly! She's a natural!

 

[GPGeorge]

Tony,

See. George spotted a secret within my message.

"It would be nice if the Northwind 2 had some similar basic, but critical, info"

I assumed you meant documentation and video support. We won't try to teach Access. We'll try to explain how "Northwind" illustrates features. But that's a big task, too.

 

[Uncle Gizmo]

Good voice, knowledgeable and experienced.

I understand that Pat was a regular presenter at local MS Access Groups...

 

[ahmad_rmh]

Thanks Pat and Tony for the sharing of this knowledgeable and informative video.

You both have deeply cleared that what the access is doing at the back end on behalf of us if we are doing something wrong.

And cleared so many points which i was wandering around.

Thanks a lot.

 

[jdraw]

I understand that Pat was a regular presenter at local MS Access Groups...

Yes. I recall a few years back that I was participant/listener on one of her evening CTUserGroups meetings.

 

[Uncle Gizmo]

(it's not really my video, it's mostly Pat!)

The video represents an interesting collaboration between the USA and UK.

Pat hosted the meeting on gotomeeting, on which she has an account.

I recorded it with screencast-o-matic, and shared it on screencast-o-matic hosting, which I haven't used much before. I usually use YouTube.

>>>Edit<<<
Also we are over 3000 miles apart, and working together. IT'S amazing!

 

[ahmad_rmh]

I give a salute to @CJ_London and @Pat Hartman to drive me into a new direction of database and form making and pushed me to make a single form for all of my inventory project.

I was a lit bit nervous at that time and at the end I have got success to finalise in that way. Still some points are remaining which would be posted later on.

Thanks a lot all of those who have given response time to time.

 

[paisenhi8]

The same thing I have written that if anyone of the conditions will not met then it will pop up message.

 

[ahmad_rmh]

The same thing I have written that if anyone of the conditions will not met then it will pop up message.

Where ????

 

[NauticalGent]

Very helpful to see the Order of Events.

Absolutely. I did something similar a few years back but it was very crude - A MsgBox on each event so I could figure out what the blazes was going on. Their demo is MUCH more practical and informative.

 

[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.