Refresh table control

[samtilak]

Hi all, i have a table control (Table Control 1) under a Subform (sfmCusInv) inside a main form. The data of the subform is from a Query (query1). When ever the main form reloads, a macro updates the query1 so as to change the data on the table. However, though the query is updated, the table is not showing the updated recordset.

oSubForm1 = oForm.getByName(“sfmCusInv”)

oTable1 = oSubForm1.getByIndex(0)

I have tried oTable1.reload and also oSubFrom1.reload…but they are not working.

Would appreciate any help anyone can offer.

Thanks in advance.

[Ratslinger]

Hello,

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?

Then, your 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.

Edit 2018-05-17:

@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.

[samtilak]

Hi Ratslinger, thanks very much for the quick reply. Sorry for the earlier brief note. Here is some additional detail on my application. I am developing an accounting app. The main form (oForm) is for adding new records into a Transaction table. One of the actions in this form is to post incoming funds from a customer for one or several of their unpaid invoices. Once the user selects a customer from a listbox, a macro updates the query (table control data source) of SELECT statement to pick all invoices (another table) that are still unpaid and belonging to the selected customer (from listbox). One of the fields in this query table is a checkbox that can be ticked-off to mark as paid.

Since the earlier post, i realized i dont need to also tie the table updating to main form (oForm) reload. Instead, have this triggered by the listbox change only. I made the change and it seems to have solved the problem, with one small inconvenience - the table does not update until the main form data is saved. I am not sure if this is to do with the way linked subforms tables work…but if there is a way to do this without having to save the main form record, that will be even better.

Thanks again and sorry for the long winded reply.