Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

This is a difficult question to answer when reading between the lines. First, Base is not Access. There are a lot of things in Access which can be done with a click or two. This is not the case with Base. Most things can be done but require specific coding in macros, hiding controls, filter tables and more.

This sample is in direct answer to the original question and uses a macro. The two list boxes are almost identical with first/last names reversed in the List content property (Data tab of the controls' properties). The same macro is used for both listboxes. The calling of the macro is done by the controls' Item status changed Event (on Event tab of contols' properties). Since first and last name can be duplicated, the CUSTNUMBER was included to make it unique. When the macro is executed, oEvent contains the SelectedValue for whichever listbox was selected. The CUSTNUMBER is extracted and used to filter the table in the subform. This all probably could have been done using a single listbox and just changing the List content with a radio button selection (thus the comment).

Sample:SelectCustomerByDifferentListBoxes.odb

Other reasons brought up in your comment for multiple listboxes must be taken on a case-by-case situation. LO does not have successive filtering ( see this post).

Most of the situations will need to deal with object properties. OOME is the best reference. The are also object inspection tools - MRI and Xray tool.

And if you really need to stick with some of the Access coding there is Access2Base already included in LO.

This is a difficult question to answer when reading between the lines. First, Base is not Access. There are a lot of things in Access which can be done with a click or two. This is not the case with Base. Most things can be done but require specific coding in macros, hiding controls, filter tables and more.

This sample is in direct answer to the original question and uses a macro. The two list boxes are almost identical with first/last names reversed in the List content property (Data tab of the controls' properties). The same macro is used for both listboxes. The calling of the macro is done by the controls' Item status changed Event (on Event tab of contols' properties). Since first and last name can be duplicated, the CUSTNUMBER was included to make it unique. When the macro is executed, oEvent contains the SelectedValue for whichever listbox was selected. The CUSTNUMBER is extracted and used to filter the table in the subform. This all probably could have been done using a single listbox and just changing the List content with a radio button selection (thus the comment).

Sample:SelectCustomerByDifferentListBoxes.odb

Other reasons brought up in your comment for multiple listboxes must be taken on a case-by-case situation. LO does not have successive filtering ( see this post).

Most of the situations will need to deal with object properties. OOME is the best reference. The are also object inspection tools - MRI and Xray tool.

And if you really need to stick with some of the Access coding there is Access2Base already included in LO.

Edit 1/17/17 - I am adding this answer here because the system won't accommodate it where I originally deleted it. The beginning was the original (mostly) posted and more added.

First, there is no such thing as LOBA - Libre Office Basic. It actually is StarBasic (it's even in the LO documentation). You should simply refer to it as BASIC and not try being cute with made up acronyms. Second, not sure why you care about the record number. It has no real bearing on obtaining information. If you want to retrieve information, use an SQL statement in Basic. Done all the time. SELECT CITY FROM XXX WHERE ID = 'xxx' This produces a result set you can retrieve and deal with the appropriate data in the way you want. A good use for this is totals.

Edit 1/6/2017: Attached is a sample (four different forms) and also information on use. I did not do a ton of checking of data so if you find some type of error or condition I didn't cover don't be surprised.

TablesAndMacros.odt - usage document

TablesAndMacros.odb - replaced 1/7/17 because previous had last minute change which didn't accommodate all forms involved.

Edit 1/17/17 - Frankly, I had given up because of your unwillingness to grasp what is being stated in these answers. You have this propensity to think ACCESS. Once again, Base is NOT Access! If you're going to work with Base then forget Access. When you work with Access forget Base. There are many, many differences. Learn what Base can & can't do then work with that. You can't even resist referring to it in your code.

As a final try, and after seeing your latest attempt, I've taken the sample from months ago, added a result set and a few additional functions and produced the attached. Frankly, I'm exhausted with the same approach time and again so I'll state right now this is not a finished product but indeed a working one. I do not change list boxes as you have tried, the code could be (and should be) cleaner and I can only envision use of this in specific instances. Certainly no one should ever want a list or combo box to select from hundreds or thousands of names.

The sample provides for walking through records with multiple matches on the selection criteria using pushbuttons for next & previous records. I've also included a third list box with made up (working) criteria which only demonstrates an open mind can find different ways of accomplishing tasks. Although not included, I can certainly see the possibility of multi-level filtering if one was so inclined. You just have to properly analyze the situation and not get stuck on something like FindFirst (which is nothing more than `Select X Where Y = Z).

CustomerSelectionByMacro.odb

This is a difficult question to answer when reading between the lines. First, Base is not Access. There are a lot of things in Access which can be done with a click or two. This is not the case with Base. Most things can be done but require specific coding in macros, hiding controls, filter tables and more.

This sample is in direct answer to the original question and uses a macro. The two list boxes are almost identical with first/last names reversed in the List content property (Data tab of the controls' properties). The same macro is used for both listboxes. The calling of the macro is done by the controls' Item status changed Event (on Event tab of contols' properties). Since first and last name can be duplicated, the CUSTNUMBER was included to make it unique. When the macro is executed, oEvent contains the SelectedValue for whichever listbox was selected. The CUSTNUMBER is extracted and used to filter the table in the subform. This all probably could have been done using a single listbox and just changing the List content with a radio button selection (thus the comment).

Sample:SelectCustomerByDifferentListBoxes.odb

Other reasons brought up in your comment for multiple listboxes must be taken on a case-by-case situation. LO does not have successive filtering ( see this post).

Most of the situations will need to deal with object properties. OOME is the best reference. The are also object inspection tools - MRI and Xray tool.

And if you really need to stick with some of the Access coding there is Access2Base already included in LO.

Edit 1/17/17 1/19/17 - I am adding this answer here because the system won't accommodate it where I originally deleted it. The beginning was the original (mostly) posted and more added.

First, there is no such thing as LOBA - Libre Office Basic. It actually is StarBasic (it's even in the LO documentation). You should simply refer to it as BASIC and not try being cute with made up acronyms. Second, not sure why you care about the record number. It has no real bearing on obtaining information. If you want to retrieve information, use an SQL statement in Basic. Done all the time. SELECT CITY FROM XXX WHERE ID = 'xxx' This produces a result set you can retrieve and deal with the appropriate data in the way you want. A good use for this is totals.

Edit 1/6/2017: Attached is a sample (four different forms) and also information on use. I did not do a ton of checking of data so if you find some type of error or condition I didn't cover don't be surprised.

TablesAndMacros.odt - usage document

TablesAndMacros.odb - replaced 1/7/17 because previous had last minute change which didn't accommodate all forms involved.

Edit 1/17/17 - Frankly, I had given up because of your unwillingness to grasp what is being stated in these answers. You have this propensity to think ACCESS. Once again, Base is NOT Access! If you're going to work with Base then forget Access. When you work with Access forget Base. There are many, many differences. Learn what Base can & can't do then work with that. You can't even resist referring to it in your code.

As a final try, and after seeing your latest attempt, I've taken the sample from months ago, added a result set and a few additional functions and produced the attached. Frankly, I'm exhausted with the same approach time and again so I'll state right now this is not a finished product but indeed a working one. I do not change list boxes as you have tried, the code could be (and should be) cleaner and I can only envision use of this in specific instances. Certainly no one should ever want a list or combo box to select from hundreds or thousands of names.

The The following sample provides for different drop down selectors and walking through records with multiple matches on the selection criteria using pushbuttons push buttons for next & previous records. I've also included a third list box with made up (working) (but working) criteria which only demonstrates an open mind can find different ways of accomplishing tasks. Although not included, I can certainly see the possibility of multi-level filtering if one was so inclined. You just have to properly analyze the situation and not get stuck on something like inclined.

The sample does NOT use any "looping" in the code to find records. FindFirst (which is nothing ( in Access and Access2Base) is little more than `Select Select X Where Y = Z).Z.

CustomerSelectionByMacro.odb

This is a difficult question to answer when reading between the lines. First, Base is not Access. There are a lot of things in Access which can be done with a click or two. This is not the case with Base. Most things can be done but require specific coding in macros, hiding controls, filter tables and more.

This sample is in direct answer to the original question and uses a macro. The two list boxes are almost identical with first/last names reversed in the List content property (Data tab of the controls' properties). The same macro is used for both listboxes. The calling of the macro is done by the controls' Item status changed Event (on Event tab of contols' properties). Since first and last name can be duplicated, the CUSTNUMBER was included to make it unique. When the macro is executed, oEvent contains the SelectedValue for whichever listbox was selected. The CUSTNUMBER is extracted and used to filter the table in the subform. This all probably could have been done using a single listbox and just changing the List content with a radio button selection (thus the comment).

Sample:SelectCustomerByDifferentListBoxes.odb

Other reasons brought up in your comment for multiple listboxes must be taken on a case-by-case situation. LO does not have successive filtering ( see this post).

Most of the situations will need to deal with object properties. OOME is the best reference. The are also object inspection tools - MRI and Xray tool.

And if you really need to stick with some of the Access coding there is Access2Base already included in LO.

Edit 1/19/17 - The following sample provides for different drop down selectors and walking through records with multiple matches on the selection criteria using push buttons for next & previous records. I've also included a third list box with made up (but working) criteria which only demonstrates different ways of accomplishing tasks. Although not included, I can certainly see the possibility of multi-level filtering if one was so inclined.

The sample does NOT use any "looping" in the code to find records. FindFirst ( in Access and Access2Base) is little more than Select X Where Y = Z.

CustomerSelectionByMacro.odb

Edit 1/20/17

Note: Filtering need not be used as in the sample. The data from the result set can be directly moved into the individual fields. Using a filter just simplifies the process.