Required field on subform

[SamLis]

Private Sub Form_BeforeUpdate(Cancel As Integer)

' This procedure checks to see if the data on the form has
' changed. If the data has changed, the procedure prompts the
' user to continue with the save operation or to cancel it. Then
' the action that triggered the BeforeUpdate event is completed.

Dim Ctl        As Control

    On Error GoTo Err_BeforeUpdate

    ' The Dirty property is True if the record has been changed.

    If Me.Dirty Then

    ' Prompt to confirm the save operation.

        If MsgBox("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then

            Me.Undo
        Else

            For Each Ctrl In Me.Controls
                If Ctrl.Tag = "*" And IsNull(Ctrl) Then
                    MsgBox "A required Field has not been filled."
                    Cancel = True
                    Ctrl.SetFocus
                    Exit For
                End If
            Next Ctrl
        End If
    
    End If

Exit_BeforeUpdate:

    Exit Sub

Err_BeforeUpdate:

    MsgBox Err.Number & " " & Err.Description

    Resume Exit_BeforeUpdate


End Sub

Hi,
THis code avoids a form being closed if a required field (with tag "*") is not filled. How can I expand it to fields on a subform?

Another "issue", how can I personalize the error messages :

1) Can't save record at this time ... (required fields in my table)
2) Multicolumn index causes an error message "Ca't save because of duplicate value in primary key, index ...

My user's do not know nor care about keys and indexes.
"This record already exist !"

thanks

 

[burrina]

Of course you can add this code to your subform or individually add the required fields with a If or Case statement.

 

[SamLis]

Hi,

How do I refer to the ctls in subform?

instead of me! ctrls?

How do I do : Me.ctrls and subform ctrls ?

thanks

 

[sumox]

simple goto the form in which u want this
and dbl-click on Properties button leftmost to Scale/Measuring line

then goto Event Tab
There are many events in form, like Onload, OnUnload, OnKeyUP
AfterInsert
if you wish that before adding/after adding a record your query/SUB VBA shud
check then choose BeforeInsert/AfterInsert respectively (click on [...] button, it will take you to that event's Sub/funk)

If you wish Before Saving/Updating some vba shud check then
try this : Example : (To be inserted in BeforeUpdate)
If(Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='" _
& Me!ProductName & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ProductName.Undo
End If