Form-Subform required field validation.

[BiigJiim]

Using Access 2016, I have a main form frmInvoice which has a subform frmInvoiceDetail. My main form is bound to a table with a Required field "PaymentMethod". However, the field is usually not populated until after the items on the invoice have been entered into the continuous subform. I want the required field validation to kick in when the user moves away from that invoice record or closes the form, but not when switching focus between the main form and subform.

I have tried removing the Required status on the field and running vba code that checks the field is populated when the user closes the form or moves to another invoice using on-form controls, but there seems to be no way of running this code when the user moves to another invoice record using the navigation buttons.

Any ideas? Thanks for reading...

 

[theDBguy]

Hi. Unfortunately, despite several attempts of requesting for it from MS, we still don't have a "on leaving record" event. One idea I was thinking you could try is use the Form's Current event to check if the main table has a record with child records but don't have a designated payment method. If so, you can navigate the main form to this record.

 

[arnelgp]

put this on the Enter event of subform frmInvoiceDetail:

Private Sub frmInvoiceDetail_Enter()
If (Forms!frmInvoice!PaymentMethod & "") = "" Then
    MsgBox "You need to fill out the Payment Method first"
    Forms!frmInvoice!PaymentMethod.SetFocus
End If
End Sub

 

[BiigJiim]

put this on the Enter event of subform frmInvoiceDetail:

Private Sub frmInvoiceDetail_Enter()
If (Forms!frmInvoice!PaymentMethod & "") = "" Then
    MsgBox "You need to fill out the Payment Method first"
    Forms!frmInvoice!PaymentMethod.SetFocus
End If
End Sub

Thanks for the suggestion, but this doesn't help me. I need the user to be able to move the focus to the subform and input data there, without the required field from the main form causing a validation issue.

 

[BiigJiim]

Hi. Unfortunately, despite several attempts of requesting for it from MS, we still don't have a "on leaving record" event. One idea I was thinking you could try is use the Form's Current event to check if the main table has a record with child records but don't have a designated payment method. If so, you can navigate the main form to this record.

Thanks for the suggestion - that might work. The only complication is I have just thought of a scenario where the user needs to be notified the payment method has not been filled it, but has the option to override it. Then this method won't work.

As you say, unbelievable that a 'leaving record' event does not exist...

 

[theDBguy]

Thanks for the suggestion - that might work. The only complication is I have just thought of a scenario where the user needs to be notified the payment method has not been filled it, but has the option to override it. Then this method won't work.

As you say, unbelievable that a 'leaving record' event does not exist...

Hi. That should be easy enough to handle. So, when the user moves to a new record, you check if there's a parent record with child records that don't have a payment method specified. At this point, instead of navigating to that record, as I originally suggested, you can simply post a message box to the user to confirm whether they want to specify it now or just move on.

 

[arnelgp]

ok, I can't find a work around on when you change records.
but you can do the validation on the main form's Unload Event:

Private Sub Form_Unload(Cancel As Integer)
    With Me.RecordsetClone
        .MoveFirst
        Do While Not .EOF
            If (!PaymentMethod & "") = "" Then
                MsgBox "You need to fill up PaymentMethod before you can close the form!"
                Cancel = True
                Me.Bookmark = .Bookmark
                Me!PaymentMethod.SetFocus
                Exit Do
            End If
            .MoveNext
        Loop
    End With
End Sub

 

[BiigJiim]

Hi. That should be easy enough to handle. So, when the user moves to a new record, you check if there's a parent record with child records that don't have a payment method specified. At this point, instead of navigating to that record, as I originally suggested, you can simply post a message box to the user to confirm whether they want to specify it now or just move on.

The only problem with that is that it would keep flagging up every time the user moved from any one record to another, unless I add some sort of new flag field to the table, which isn't ideal.

Did some digging and it looks like MS originally planned to have a RecordExit event, as it was documented in Help and referenced in the Northwind application. They even released a Knowledge Base article 304139 about how to program for a RecordExit event, although that seems to have disappeared.
http://www.justskins.com/forums/recordexit-event-seems-non-184780.html

 

[arnelgp]

do you have any Autonumber field in your main form?
if no, just replace the "InvoiceNumber" with the textboxname of your Invoice number on the main form.
the code goes to the Main form's Current event:

Dim strInvoice As String   'this first line must be after Option database or Option Explicit
Private Sub Form_Current()
    With Me.RecordsetClone
        If strInvoice = "" Then
            strInvoice = Me.InvoiceNumber
            Exit Sub
        End If
    
        If strInvoice <> Me.InvoiceNumber Then
            .FindFirst "InvoiceNumber=" Chr(34) & strInvoice & Chr(34)
            If (![PaymentMethod] & "") = "" Then
                MsgBox "You need to fill the PaymentMethod of the previous record before you can move to this record"
                Me.Bookmark = .Bookmark
                Me.PaymentMethod.SetFocus
            Else
                
                strInvoice = Me.InvoiceNumber
            End If
        End If
    
    End With
    
End Sub