Base Listbox Macro

[MaaX]

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.

Many thanks in advance.

Regards MaxProject Tracking Test V1.09.odb

[Ratslinger]

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.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[MaaX]

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?

[MaaX]

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