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

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

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.

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

Main forms should NOT be allowed to cycle to the next record. To prevent that Change the Cycle property on the Other tab of the main form properties dialog to "Current Record".

I also don't like to automatically move to a new record on subforms but there are pros and cons so do what you think is best for the user. Just remember, if you allow the form to cycle to the next record when tabbing out of the last control in the Tab Order, the user might not recognize what he did and might think he's on the same record. When you are adding a number of new records, it is convenient to advance to a new record automatically. To have both behaviors requires code so you can advance when the record you are leaving was new but not advance when it was updated.

 

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

 

[Pat Hartman]

You're welcome. I agree. A solid design takes thought and time. But I did make the suggestion regarding the BeforeInsert event back in post #9. So, please ask if you don't understand a suggestion