Reputation: 307
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...
Upvotes: 1
Views: 381
Reputation: 2696
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), therst
recordset and the forms recordset are out of sync inWillMove
event. You have to userst
recordset 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
Upvotes: 0
Reputation: 26
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
Upvotes: 1