Some queries may have one expression or calculation and lagging may occur. Other queries have many expressions and lots of records, but no lagging. Mostly the more expressions the more challenges.
Those challenges were solved many moons ago. For the 5 main documents in our system we created a GENERATE FORM(mirror image of the original document, i.e. Sales Invoice)which do multiple calculations and expressions. The generate table and queries never store more than one record. As soon as the user completes the document and approve of it he clicks on a save button. The button uses an append query to save the Invoice to an invoice table. Absolutely no calculations or expressions are found in those queries. No lagging or halting no matter the quantity of records.
The "Generate form" uses its own queries because it is filled with expressions. The need to edit existing records means that there is an exact same form we call the "Invoice Edit form" or "Payment Edit Form" etc. The Edit forms use the queries with no expressions or calculations.
Users does not realize there are two different forms and it works perfect. This choice did bring a few other challenges and we would prefer to change back to having one Invoice form for generating and Editing historical invoices.
My question. If we have one Invoice form, and only VBA does the calculations, would lagging and halting still be a problem. What other options are used. I assume few programmers use two form like we do.