Make code Shorter (1 Viewer)

James Dickinson said:

I would try something like this

Code:Copy to clipboard

Private Sub form_afterupdate()

   Dim ctl As Control

   Dim frm As Form

   

   For Each ctl In Forms!frmOrder.Form.Controls

      On Error GoTo NextControl

      If ctl.ControlType = acSubform Then

         Set frm = ctl.Form

         Debug.Print frm.Name

         With frm

            .AvaQu = Nz(.AvaQu, 0) - Nz(.ProQu, 0)

         End With

         Exit For

      End If

NextControl:

   Next

End Sub

Click to expand...

The_Doc_Man said:

In the picture attached to post #10, you have a syntax error that is so glaring I am surprised that nobody else caught this. I have enhanced the color of the culprit.

Code:Copy to clipboard

[COLOR="Red"]Set[/COLOR] MyFrm.Avaqu = MyFrm.Avaqu  - MyFrm.ProQu

The correct verb for a statement that does math is LET, not SET, and besides that, the LET verb is optional (assumed) in VBA. SET is a way to assign object references.



I checked the appropriate web sites but there is no interpretation of the SET verb other than to assign object references. It does not substitute for LET.



Then, the problem with this:



Minty suggested:

Code:Copy to clipboard

with [Forms]![frmOrder]![frmOrderDetails].[Form]

    ![AvaQu] =![AvaQu] - ![ProQu]

end with

Exit Sub

but you wrote

Code:Copy to clipboard

with [Forms]![frmOrder]![frmOrderDetails].[Form]![AvaQu] =![AvaQu] - ![ProQu]

end with

Exit Sub

You misinterpreted what you saw. That was not a case of someone continuing a long statement on the next line. Minty was suggesting TWO statements. First, a WITH - which establishes something called a reference context block (in some other languages than VBA). Then the second statement is a mathematical statement dealing with two items that are defined when within that context block.

Click to expand...