Subform not displaying filtered results initially

Question

I have a form with 2 sub forms:

Main form: PaymentDetails
Subform 1: UnreleasePayments
Subform 2: ReleasedPayments

The idea is that the user is able to move payment records between subform 1 and subform 2 by updating the Pay Status column:

This can be done in bulk using the command buttons I've created, for example, setting all held payments to "Release" causes them all to move to the bottom subform:

...or it can be done per record by changing the Pay Status combo box:

Issue: The problem comes when updating the Pay Status combo box updates from subform 2 to subform 1 (i.e. from "Release" to "Hold"). Every other scenario seems ok.

The first time a payment is set from "Release" to "Hold" via the combo box, the record correctly disappears from subform 2...

...but for some reason the record does not display in subform 1, even though the total value calculated for subform 1 suggests that a record should be there:

If I move the next record from "Release" to "Hold", this one does display, but still no sign of the first record I moved (also the total value calculated for subform still has £500 more than what's displayed):

When I move the final record from "Release" to "Hold", all 3 records are now actually displaying in subform 1:

The Main Form is bound to a table called PaymentRun. Subform 1 & 2 use queries to display their data, but both use a table called Payments, which has a relationship set up with PaymentRun where PaymentRunID is PK of PaymentRun and FK of Payments.

When a record's PayStatus is set to "Release", some VBA will assign the PaymentRunID from the Main Form in to the PaymentRunID of the payment record (and remove it when the PayStatus is to "Hold" or blank).

Subform 2 (ReleasedPayments) is set up as a child of the Main Form, so each time a payment record gets the Main Form's PaymentRunID it will the display the record in Subform 2.

Subform 1 (UnreleasedPayments) isn't linked to the main form, and displays records based on a string being passed to its .Filter property.

Here's the code in the order it should run from the change event of the PayStatus combo box in Subform 2 (ReleasedPayments):

cboPayStatus Change Event:

If _
    Me.cboPayStatus.Value = "Hold" _
Then
    Me.PaymentRunID.Value = Null
ElseIf _
    Me.cboPayStatus.Value = "Release" _
Then
    Me.PaymentRunID.Value = Me.Parent!PaymentRunID.Value
End If

Call PaymentsFilter
Call PaymentsRequery

PaymentsFilter sub (for filtering subform 1):

Dim strUnreleased As String

strUnreleased = ""

strUnreleased = "[PaymentPeriod] IN " _
              & "(SELECT PayPer.PaymentPeriod " _
              & "FROM PaymentPeriod PayPer " _
              & "WHERE PayPer.PaymentPeriodSort <=" & Forms!PaymentDetails!cboPaymentPeriod.Column(1) & ") " _
              & "AND [EDRSRef] = '" & Forms!PaymentDetails!cboEmployer & "' " _
              & "AND ([PaymentStatus]='Hold' Or [PaymentStatus] IS NULL)"

Forms!PaymentDetails!PaymentDetails_sub.Form.Filter = strUnreleased
Forms!PaymentDetails!PaymentDetails_sub.Form.FilterOn = True

PaymentsRequery sub (for requerying subform 2 and pushing released payment value to payment run amount)

Forms!PaymentDetails!PaymentDetailsRelease_sub.Form.Requery
Forms!PaymentDetails!txtRelVal.Requery
Forms!PaymentDetails!PaymentRunAmount.Value = Forms!PaymentDetails!txtRelVal.Value

Matt Hall Matt Hall · Accepted Answer · 2019-02-12T13:52:05

I'm not totally sure what the issue was, but switching the order of these two fixed my problem:

Call PaymentsFilter
Call PaymentsRequery

Perhaps PaymentsRequery was causing subform 2 to steal focus before PaymentsFilter had completed its effects on subform 1?

If anyone can offer an explanation I'd still be interested.

Subform not displaying filtered results initially

1 Answers