Main form record validation when moving focus to subform in MS Access

1
votes

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

2
vbams-accesssubformrequired-field
Do you mean the intrinsic navigation bar buttons? I usually disable this and build my own navigation buttons and code. – June7
I use a one record recordset (filtered to one invoice-nr), so mainform can't move. – ComputerVersteher

2 Answers

1
votes

Forms and objects in Access have an order of operations. Order of Events MS Article

Unfortunately it appears that you would need to invoke the RecordExit event but it is not available. More detail here: RecordExit Didn't make the cut

0
votes

Example code using ADODB.Recordset as Form.Recordset and ADODB.Recordsets WillMove event to track recordset moves.

For some reason (I'm novice in ADODB-Forms), therstrecordset and the forms recordset are out of sync inWillMoveevent. You have to userstrecordset check values. The form controls will show the values of the record you want to move to. Then you have to set the forms recordset back to rst or form will move.

Use this code in the Form:

Option Explicit
Private WithEvents rst As ADODB.Recordset

Private Sub Form_Close()
Set rst = Nothing
End Sub

Private Sub Form_Open()
Dim con As ADODB.Connection
Set con = New ADODB.Connection
con = CurrentProject.Connection
con.CursorLocation = adUseClient
con.Open

Set rst = New ADODB.Recordset
With rst
    .ActiveConnection = con 
    .LockType = adLockBatchOptimistic
    .CursorType = adOpenDynamic
    .Open "Select * from Table"
End With
Set Me.Recordset = rst
End Sub

Private Sub rst_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
If Len(rst.Fields("PaymentMethod") & vbNullString) = 0 Then
    adStatus = adStatusCancel
    Set Me.Recordset = rst
End If