replacing the refresh button

First of all apologies about my naming of things here. I’m used to Access where a form is a form and a form within a form is a subform!

I have 2 forms on a document (? I’d call it a form in Access) which are basically 2 listboxes. The 2nd listbox contains rows/records to related to the row/record in the first listbox.

My form is based on the example in this post Using multiple listboxes which was my first question about how to achieve the above (listbox populating 2nd listbox). I don’t display results of the second listbox as per the example. All I’m interested in is the what’s in the filter table which should be the id from the first and second listboxes. The example shows two buttons that the user has to click after the 1st and 2nd selections from each listbox. They both have the action “refresh”. I want to replace the buttons with an event macro that does a “refresh” when the listbox changes. I have done this sort of thing before when I had a mainform and subform and it seemed to work OK. This however is not he case with 2 (main?) forms on the document itself.

Before I continue. Is it important where the “refresh buttons” are? In the example they are on InvSelect form, and on something called a mainform which looks like a subform below InvSelect? This may have some bearing on what I’m supposed to refresh in the macro.
As stated before my doc (ument?) has 2 forms on it. The CustomerSelect and the InvoiceSelect forms. Each form has a listbox the CustomerListBox and yes you’ve guessed it the InvoiceListBox. To try and prove things are working I have 2 text boxes containing the CustomerID and InvoiceID from the filter table (txtCustFilter and txtInvFilter). These are currently on the CustomerSelect form. They didn’t work at all elsewhere (InvoiceSelect). I’m not sure where I’m up to but I’ve used various variations of the following for both Customer and the Invoice equivalent. It seems to (half) work for the customer in as much as the txtCustFilter gets updated on the document but not in the filter table itself (yes I did a manual refresh when looking).

The variations are

oForm = ThisComponent.Drawpage.Forms.getByName("CustomerSelect")
oList = oForm.getByName("CustomerListBox")

I’m pretty sure when I used it on another form to play around with oForm.reload worked perfectly but here it doesn’t at all.

Incidentally the invoice listbox seems 1 step behind.

So for example if I have have

Cust1 with Inv1 and Inv1a and Inv1b

Cust2 with Inv2

Cust3 with Inv3 and Inv3a

Let’s say the selection is on Cust1 and I change to Cust2 I see in the second listbox Inv1 and Inv1a and Inv1b. If I then select Cust3 I get Inv2 etc.

Another thing was when I started the Cust1 record had an (automatically generated) id of zero. If I was on Cust2 and chose Cust1 it would flip back to Cust2. If I chose Cust1 again it would be OK. This only ever was a problem with Cust1. I Eventually changed the id (manually) from 0 to 6 (making necessary changes to Invoice records as well) and everything is OK as far as that is concerned. Any ideas what was happening there. I know I’ve probably broken the too many question thing but this is all pertaining to one problem. I can split if deemed necessary.

Version: (x64)
Build ID: 64390860c6cd0aca4beafafcfd84613dd9dfb63a
CPU threads: 2; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded
HSQL Version 1.8

Pressing Enter twice in the appropriate places will break your question into readable paragraphs.

Cut and pasted from notepad with breaks in right places. Didn’t notice they’d gone. Redone. Any better?


First these are additional questions and they reflect something incorrect in your setup. Submit new question with sample showing problem:

Incidentally the invoice listbox seems 1 step behind.


If I was on Cust2 and chose Cust1 it would flip back to Cust2.

Now as for the main question. An issue you may be having is the Physical and Logical charastics of the process. Looking at the form, it is designed for the User. You have a list box for Customer selection and to its’ right is a push button to select that customer. The same is true for the Invoice list box. And finally there is the resulting detail.

From the logic of the form, the Customer Selection push button (with Action set to refresh form) is actually on the form containing the Invoice list box. Now you select a customer in the customer list box and push the button which is on a different form, the list box entry is committed and the value is entered into the Filter Table Field. The system does this when switching from form to form (or sub forms or sub sub forms etc.). The button action refreshes the form (on the Invoice form) and the items in the Invoice List box are shown based upon the new selection. The same applies to the Invoice form and detail sub form.

A problem with coding this is to first understand all that has transpired. This includes the committing of data, new focus of controls and on which form (sub form) you are on. There are many items to be understood when dealing with Base macros - Forms, controls, their model and views and so much more. Here is a link to some further links → To learn LibreOffice Base are there introductions or tutorials?

Have attached a sample. This is the same sample provided in a previous answer to you. It has an added form with the push buttons removed and macro replacing them.

Edit - replaced sample adding wait and change code sequence.
Sample ------- ProgressiveLB.odb

Edit 2:

Added a third form. Another method as noted in my comment. This form uses a macro, both list boxes on one main form and the detail on another main form. The Filter Table is not needed.

Sample ---- ModifiedProgressiveLB.odb removed use of filter table on third form

Also note, by using macros this process could be done differently. The two list boxes on one form and the detail on another. Left it this way to complete the example. With macros there are typically many different methods which will get you the same result.


Forgot to complete a verification of invoices displayed correctly. It is currently incorrect. Will check and modify appropriately.

Edit 2:

Corrected. New sample posted.

Thanks I’ll take a look and inwardly digest. That may take a while but I’ll be back when done!

I don’t know if I’m going mad but this is displaying the same symptoms I described in my initial post.
Just to make sure I’m not going mad we have 3 customers. I’ll list them below with the invoice numbers
abc co 1244 1187
gizmo in 1555 1213
achme 864
When you first go into the form whatever customer is displayed (presumably by what’s leftover in the filter table) the invoice numbers are correct (click down arrow without selecting). If I then click on another customer then I still see the same invoices from the 1st. If I then click on the 3rd remaining customer I’ll see the invoices from the previous chosen customer.


Insure you are using the current posted version. I initially had a code problem but corrected that and re-posted. Have just downloaded the “Modified” sample posted and all seems OK - select a customer and invoices are correct.


Have also downloaded the other posted sample and that works also. You must be testing with the original version which has been replaced.

Quick glance and notice we have a no button, and a no button no filter. Both look like they work brilliantly. I’ll take a closer look at the macros and see if I can work out what’s going on and apply it to mine. I don’t think I need to post another question about my invoices list being 1 step behind the customer selection as I think what you may have done in your second version will explain that. It might also explain the ‘funny’ I was getting with the customer with the id of 0. If not I’ll post another question. In the meantime thanks and I’ll go away and play and report back!


In the alternateRefresh sub, the lines to set focus are not needed (at end of sub). They are leftovers from when the filter table was still present. The related Dim statements can also be removed.

I’m using the filter table option as the idea is to eventually pass control to another form. I’m almost there, I no longer get the “1 step behind” with the customer listbox but now get it on the invoice one! No idea why at the moment. Probably a weird spelling mistake. I do things slightly different from you in that I have 2 macros one for each box but that shouldn’t make a difference as the logic is still the same. I’ll report back when done. This exercise has thrown up a load of why questions which once I’ve got this out of the way I’ll attempt to put pen to paper.

I’ve worked out what my problem was. In moving and creating forms I lost a vital link to the master and slave tables. I was looking elsewhere until I went back to basics and looked at each form sub form control etc one by one.
That said I do still have a problem! Everything seems to work fine except I was not getting the right customer id and invoice id on the Mainform (yours is MainForm which I found out is different!). This seemed to be intermittent but I tracked it, finally, down to one customer that only had 1 invoice. If I selected that customer and then chose the 1 invoice if I subsequently picked another customer with 2 or more invoices and picked the 1st invoice in the list the customer and invoice id on Mainform is not updated. I then went back to your example which works 100% but it has 2 invoices or more per customer. If I change the first customer abc to only have 1 invoice and ‘give’ that invoice to gizmo I can replicathe that descrived above. I am now stuck.


So for the test I downloaded the Modified sample. Changed Invoices data record ID 5 to be CUST_ID from 2 to 1. No problem on either form using your procedure. Then change that value back to original and changed record ID 2 to a 1. Again both forms with macros had no problems. Even tested:

If I change the first customer abc to only have 1 invoice and ‘give’ that invoice to gizmo

but no problem. Something may have been changed at your end. Try downloading the Modified version again and re-test. If a problem, please give exact steps as I cannot reproduce based upon what you have written.

OK I’ll download and have a look. I’m currently travelling so may be a while before I get back, but I will sooner or later.


Moved to Win 10. INVOICES_NO_FILTER worked without a problem. INVOICES_NO_BUTTONS did in fact have the problem. This problem does not occur in Ubuntu 20.04.x

Fix by changing the Invoice list box macro event from Changed to Item Status Changed. Form then works.

Reference → Events

I was just about to put that I thought my 1 invoice customer was a red herring because I can get the same results without having a a 1 invoice customer when I noticed your comment about changing the list box macro event in an email I received. I’ve quickly done this and on first glance it looks to have resolved the issue. However in my past life I was a tester so I’m going to go away and see if I can break it! I’ll report back as soon as I can.