From main form, run VBA to validate total in subform

Question

I have a form in MS Access 2002 with the following snippet of code behind a command button.

'Requery subform to make sure total is calculated
Me.fsubUpdateShipments.Requery
DoEvents

'Confirm quantities have been entered
If Form_fsubUpdateShipments.txtTotalShipmentQty.Value <= 0 Then
    MsgBox "Cannot create shipment, no quantities have been entered", vbCritical
    Exit Sub
End If

For months this has been working fine but today a colleague came to me and explained that the error message was showing even when they had entered quantities.

After doing a bit of digging I determined that the .Value <= 0 expression was being evaluated before the textbox in question had finished calculating its value: =Sum([QtyToShip]). This seems to only occur when the subform has a non-trivial number of records (around 10 or more) and is obviously a serious problem.

Adding a breakpoint on the line containing the If statement allows the code to run correctly but I obviously cannot use this method permanently.

Is there some way I can force the code to pause until the subform controls have finished recalculating their values?

Answer 1

I would skip using the user interface altogether and look at the tables. If this is run on a main form command button, the subform has lost focus and saved its values. A requery is unnecessary. Just re-create the Parent-Child relationship in the criterion (third parameter) of a DSum:

If DSum("QtyToShip", "ShipmentDetails", "ShipmentID = " & Me!ShipmentID) <= 0 Then
    MsgBox "Cannot create shipment, no quantities have been entered", vbCritical
    Exit Sub
End If

Answer 2

There are two options, both already identified by others, but as their suggestions are either incomplete or untidy (slow), I'm making this post.

A. You could put in an enforced wait to delay the IF test:

Put this in a module:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
...
Public Sub GoSleep(millisecondDelay as long)
    Sleep (millisecondDelay)
End Sub

And in your form:

'Requery subform to make sure total is calculated
Me.fsubUpdateShipments.Requery
DoEvents

GoSleep 1000 '1 second delay. Or 2000, whatever is required really

'Confirm quantities have been entered
If Form_fsubUpdateShipments.txtTotalShipmentQty.Value <= 0 Then
   MsgBox "Cannot create shipment, no quantities have been entered", vbCritical
   Exit Sub
End If

B. As another answer says, you can recalculate it yourself, and test on the basis of this result.

However rather than using DSum as pteranodon suggests, I would suggest something faster than DSum is appropriate as DSum (DCount etc) is very slow.

I typically use ADO for data operations, though my code can be adapted for DAO readily enough. To use this code you'll need a reference to Microsoft activeX 2.8 if you don't have one already:

In a module:

Public Function GetDBValue(qry as string) as variant
    dim rst as new adodb.recordset
    rst.open qry, currentproject.connection, adOpenKeyset, adLockReadOnly
    if rst.eof then
        GetValue = null
    else
        GetValue = rst.fields(0)
    end if
end function

public Function IsNullSQL(basevalue as variant, replacementvalue as variant) as variant
    isNullSQL = iif(isnull(basevalue), replacementvalue, basevalue)
end function

in your form:

''Requery subform to make sure total is calculated
'Me.fsubUpdateShipments.Requery
'DoEvents

'Confirm quantities have been entered
If IsNullSQL(GetValue("SELECT Sum(QtyToShip) FROM tbl WHERE ..."), -1) < 0 Then
    MsgBox "Cannot create shipment, no quantities have been entered.", vbCritical, "No Quantities to Ship"
    Exit Sub
End If

'If Form_fsubUpdateShipments.txtTotalShipmentQty.Value <= 0 Then
'    MsgBox "Cannot create shipment, no quantities have been entered", vbCritical
'    Exit Sub
'End If