Based strictly on your description it is not clear as to the purpose of the macro (table filter instead?). Also what forms have what controls. I would place this as a comment but there are too many questions.
So it seems you have a main form which is designated
oForm. Under this is a sub form named “sfmCusInv” but you never stated if or how the two are related and why is the sub form using a query?
oTable1 = oSubForm1.getByIndex(0) seems to be getting access to a control. Why the access to a control?
What is meant by
When ever the main form reloads, a macro updates the query1
What causes the main form to reload? How/why does the macro update the query? Is it changing something in the query?
With some more information (a clearer description of the set-up) it is probable there is a simple solution.
@samtilak First please do not respond with answer. This is for providing a response to original question. Use a comment to the answer you are responding to (may be multiple). If need be,
Edit (lower right of question) original question & provide additional info and specify that it is edited (as I did here). On to the answer.
Not certain as to your entire set-up. Seems you have Customers, Invoices and Payments. This is three tables with Payments related to Invoices related to Customers. Seems simple enough. Don’t see a need for any macros or separate queries (except data source selection) to get what it seems to be your request.
Have attached a sample Base file. On the form, the main form is optional as to how this is provided. Kept things simple here. Could have been a List Box if really wanted. When customer is displayed sub form (Invoices title) show relevant UNPAID invoices. For this see the
Filter property on the
Data tab of the sub form. Whatever invoice is selected on this sub form will then display ALL payments which have been made in the sub sub form (Payments title).
With this form new customers can be added, invoiced added and payments added.
Sample — InvoicePayments.odb
Edit #2 2018-05-17:
Decided to add second sample. Actually same as first but with a second form added. This second form actually is nothing more than a duplicate of the first but adds a nice feature - total of payments for an invoice. Done with SubSubSubForm & some SQL.
Please note, ALL of the above has been done without any macros.
Sample #2 — InvoicePayments2.odb
Edit #3 2018-05-17:
OK, another. This one uses a list box in Form & pushbutton to select customer chosen. Again no macros. The list box uses a table filter.
Sample #3 —InvoicePayments3.odb
If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.