# Base Listbox Macro

Hi All, I am looking to update a Listbox in a form based on another Listbox. There are many examples of how to do this using various different methods. I have had help previously for the same problem. However when i have tried to apply this to the new version of my database things are not good.

My current requirement is in the ECN-RFQ form that the customer purchasing Listbox is restricted to only showing persons from the customer listed in the previous customer Listbox.

I have tried for many hours with two of the tried and tested options, 1 using a refresh button and 2 using a macro. both have failed miserably.

This has me asking a couple of questions.

1) How to copy macro from one database to another. I have tried using the organise macros option. I can for example see my previous database with a "refresh" macro and I can see my new database underneath but cannot see a way to copy the macro. I was able to select and drag the macro to a new database but not able to copy it. I wanted to copy it and try to update the various commands to work on a different database and form.

2) The following macro was kindly given to me by Sir Ratslinger, this worked on my previous database so I wanted to adapt it for my new effort. I know there are other stuff running in the database Listboxes involved but can the following macro easily be used in my new database by simply using macro organiser, new, pasting in and modifying the various references to suit?

The macro is this,

Sub CustChange oForm1 = ThisComponent.Drawpage.Forms.getByName("MainForm") oField = oForm1.getByName("txtCustomer") sSelectedValue = oField.SelectedValue sSql = "SELECT DISTINCT ""Item"" FROM ""PartNumber"" WHERE ""Customer"" = '" & sSelectedValue & "' ORDER BY ""Item"" ASC" oField = oForm1.getByName("txtReplaces") oField.listsource = array(sSql) oField.refresh() End Sub

I dont think this can be adapted easily as the database basic structure is different.

3) Do i have to have certain libraries or addons running for some macros to work, i think the obvious answer is yes but for the above macro do i need anything special running. The above macro makes sense to me and i can see how the sSql line works. However not well enough for me to use in the attached database on the ECN-RFQ form.

4) Could someone be so kind as to format the above macro to work with the ECN-RFQ form to enable only the relevant purchasers to show according to their company (customer) name? I can then use this to study how to do the same on other forms as I am sure I will use this kind of Listbox again on other forms.

Regards MaxC:\fakepath\Project Tracking Test V1.09.odb

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

Here is the macro needed:

Sub CustChange
oForm1 = ThisComponent.Drawpage.Forms.getByName("MainForm") 'Get Form
oField = oForm1.getByName("txtCustomer")
sSelectedValue = oField.SelectedValue
sSql = "SELECT ""FirstName"" || ', ' || ""LastName"", ""CustPurchID"" FROM ""CustomerPurchasing"""&_
" WHERE ""CustomerID"" = '" & sSelectedValue & "' ORDER BY ""FirstName"" ASC"
oField = oForm1.getByName("txtCustPurch")
oField.listsource = array(sSql)
oField.refresh()
End Sub


Attach to Item status changed event of txtCustomer control.

The easiest way to transfer code from one place to another is the same as copying the above code & pasting into the IDE.

To learn more about macros (and a very good reference) here is location of Open Office Macros Explained by Andrew Pitonyak - OOME.

more

Hi, many thanks for your help. I have been on holiday hence the delay in reply. I have gone to add the above macro and find that LibreOffice is crashing as soon as i try to click on the + next to standard under macros. I get the message "Couldnt open library element stream". I downloaded the example I uploaded here and get the same message and crash. Any ideas on this?

( 2018-04-03 10:01:47 +0200 )edit

Strange that this did not work for me until i restarted my machine for the crashing issue as mentioned above. Its working fine now and as exactly as you said above. Many thanks as I believe this is a macro I will use many times again by simply reformatting the above. thanks again :-)

( 2018-04-03 10:58:20 +0200 )edit