[Solved] Read the second column in a listbox

Creating and using forms
Post Reply
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

[Solved] Read the second column in a listbox

Post by evwool »

My Listbox cboSearch contains 2 'columns', the first being the one that shows.
SELECT "Category", "CatID1" FROM "TblCat" ORDER BY "Category"
If I attach the following macro to the 'Changed' event of the Listbox, a message box appears showing the visible column in the listbox ("Category").
It's source is

Code: Select all

SELECT "Category", "CatID1" FROM "TblCat" ORDER BY "Category"
How can I make the Message Box 'read' the second column ("CatID1") in the listbox?

Code: Select all

Sub ReadChoice(oEvent As Object)
Dim MyForm As Object
Dim MyListBox As Object
Dim MyListBoxName As String
MyListBoxName = "cboSearch"
MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
MyListBox = MyForm.GetByName(MyListBoxName)
MsgBox MyListBox.CurrentValue
EndSub
I've used both XRay and that MRI tool but haven't yet found the Property that lets me do this.
 Edit: To save anyone reading all of this, particularly MS Access refugees- you can't read the 2nd (invisible) column in a list box (which has completely different characteristics to the Access List Box) in the same way as you can read a combo box in Access but in cases where you would have used a combo box (eg. to filter a subform or find a record, which was what I was trying to do) you would actually put an extra form into your form (using Form Navigator) then put a List Box into it along with the bound field. This filtering form would usually be based on a Filter Table ie one whose purpose is to hold a single line only which is used for filtering or finding records. When the list box has made its choice, the value you require will be in its bound field. This filter table can be used as a main form with the form you want to filter as its subform with linked Master/Child Fields so no code is required or with macro code to temporarily filter the subform. For this latter purpose you may want to use a combo box (a very different animal from the Access Combo) and code like that here http://user.services.openoffice.org/en/ ... 39&t=29786 which filters a form which contains the actual text by which you want to filter. The list box is what you would use if the form you want to filter contains list boxes displaying data from linked tables rather than the actual text you want to find. 
Last edited by evwool on Wed Jun 16, 2010 8:41 am, edited 5 times in total.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Read the second column in a listbox

Post by Arineckaig »

If nobody comes up with a better suggestion, one method that you may wish to consider is to get the value from the bound field but only after after it has been updated by the selection in the List Box.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Read the second column in a listbox

Post by eremmel »

Have a look to Listbox in subform. More background how to implement the previous suggestion.

In Dynamic Loading Listbox they use oForm.GetByName("ListBox").Boundfield.INT.

Contents of one list box dependent upon another's selection? topic gives lots of information and is very detailed.

Note: When the display value is unique you can run a query to get the second value from the database directly.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Read the second column in a listbox

Post by evwool »

Arineckaig wrote:If nobody comes up with a better suggestion, one method that you may wish to consider is to get the value from the bound field but only after after it has been updated by the selection in the List Box.
Thanks for having a go at this. The ListBox isn't actually bound to a field. I want to use results of my selection in a further macro. I thought that there would be some kind of simple property that lets me read the other column (s) in the list box but it looks like there isn't :(
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Read the second column in a listbox

Post by evwool »

Thanks eremmel but although I may be missing something, your links seem to be about changing the source of a listbox rather than reading it. This listbox is not bound to any other fields in the form.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Read the second column in a listbox

Post by evwool »

eremmel wrote:Have a look to Listbox in subform. More background how to implement the previous suggestion.
In Dynamic Loading Listbox they use oForm.GetByName("ListBox").Boundfield.INT.
The code in this link is by someone using MySql. Adding to my code above:
MsgBox MyListBox.BoundField.Int
just gives me Object Variable Not set
I'm using OOBasic
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Read the second column in a listbox

Post by eremmel »

The boundfield, as far as the name concerns indicates that this refers to the table. You do not have a table so that why it is not working. But in those posts is also mentioned that you might introduce a helper-table (in a subform) with the help of this table you might get what you want.

Yes the posts are referring to changing the value of a listbox, but they use (retreive) the selected value of on other listbox as input. So you should look to the details an try to understand the complete approach and then consider what is in their for you.

Introducing yourself to a new programming environment will cost a considerable amount of time. You can hardly achieve your goals without building up a broughter look of the development environment, experiment and study some examples in dept (unless this is your 10th language and development environment). An other important aspect in using the community is to discover analogous part in the examples. E.g. and ask yourself the question what is the difference between MySQL as database and using Base's internal HSQLDB; does that make the example unusable or can I succeed with a minor transformation.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Read the second column in a listbox

Post by Arineckaig »

Thanks for having a go at this. The ListBox isn't actually bound to a field. I want to use results of my selection in a further macro. I thought that there would be some kind of simple property that lets me read the other column (s) in the list box but it looks like there isn't
If not bound to a field, I suggest a List Box is not necessarily suitable. You may find it better to use a pseudo List Box: add a two (or more) column table control as a separate form to the form document. Use SQL as the data source for the table control which can display whatever columns/fields you require. Then use a macro to read the contents of the required field from the record that you select in the table control.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Read the second column in a listbox

Post by evwool »

I think you're correct, Arineckaig, I'll put my Primary Key field and a listbox in a subform in the main form then hopefully, I'll be able to find a way to use the Parent property of the subform to manipulate the main form. If I get a code that works, I'll post it.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: [Solved] Read the second column in a listbox

Post by evwool »

For anyone who is still interested, here's the code to find the second column in a my listbox.

Code: Select all

Sub ShowSecondCol(oEvent As Object)
Dim MyForm As Object
Dim MyBox as Object
Dim MySelection As String

MyForm = ThisComponent.DrawPage.Forms.GetByName("MainForm")
MyBox = MyForm.GetByName ("cboCust")
'my list box is called cboCust
MsgBox MyBox.SelectedItems(0)
'Reads the 2nd column
MySelection = MyBox.CurrentValue
MsgBox MySelection
'reads the visible column
End Sub
Last edited by evwool on Sat Oct 24, 2009 4:12 pm, edited 1 time in total.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Solved] Read the second column in a listbox

Post by Arineckaig »

MsgBox MyBox.SelectedItems(0)
I have a problem because from my tests this line appears to read the position of the selection in the list of entries in a List Box as opposed to the contents of the second/reference column.

Later Edit:
The second column value, however, would appear to be supplied if the position in the list is used to read the ValueItemList property of the List Box. For example:

MsgBox MyBox.ValueItemList(MyBox.SelectedItems(0))

See http://www.oooforum.org/forum/viewtopic ... ueitemlist
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: [Solved] Read the second column in a listbox

Post by evwool »

Ouch! You're absolutely right, when I sorted my list box by Surname, that became clear. Thanks for alerting me
But alas the code line
MyBox.ValueItemList(MyBox.SelectedItems(0))
gives an error
Inadmissable value or datatype, Index out of defined range :(
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Read the second column in a listbox

Post by Arineckaig »

That is interesting. You will see from
http://wiki.services.openoffice.org/wik ... ment_Forms
that
Through their ValueItemList property, list box forms provide a counterpart to the VBA property, ItemData, through which you can administer additional information for individual list entries.
Coming from Access you will be familiar with ItemData.

I suggest you MRI your MyBox object (it should be model of the the List Box) and check what it shows for its SelectedItems and ValueItemList properties. Another method to check these properties is step through your code using enable watch on the MyBox object. Once this is not <out of scope> clicking on the + sign will allow you to examine the contents of the object's properties.

You might also test your code replacing 'SelectedItems(0)' with a number that you know to be within range of the array.

If all else fails I can PM you a copy of my .odb where the code works.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Read the second column in a listbox

Post by Arineckaig »

If it should be of any help. I have prepared the attached a simple Base file to demonstrate display of the second column in a List Box. The macro is similar to evwool's one listed above. The file also has a routine for obtaining the MRI output from the View and Model for every control in a form document.
Attachments
ListBox2ndCol.odb
(15.59 KiB) Downloaded 539 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Read the second column in a listbox

Post by evwool »

Arineckaig wrote:If it should be of any help. I have prepared the attached a simple Base file to demonstrate display of the second column in a List Box. The macro is similar to evwool's one listed above. The file also has a routine for obtaining the MRI output from the View and Model for every control in a form document.
Bless you! If you hadn't done that, I'd have been up all night. Our code was virtually identical so no problems there. I had used MRI but had got the error message I describled on that line of code. The ValueItemList Property showed when I referenced the list box itself in MRI where it stated that it was 'void'.
So I compared the Properties of our list boxes. In your listbox, in the Data Field, you had chosen Text, (the name of a field in the table on which your form, presumably, was based). The SQL in your list box referred to another table.
I had chosen nothing for the DATA field thinking, mistakenly, that doing so would have bound the listbox to my form (My Access Roots are showing!)
As soon as I chose CSurname to put into this DATA field the code worked absolutely fine. Thank you so much, you've cracked it!
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: [SOLVED] Read the second column in a listbox

Post by evwool »

UH OH! The saga continues.
Although my ListBox now reads the second column in it using ValueItemList, whenever I use the listbox, it replaces the text in CSurname in the form with the value in the second column in the list box - because it is BOUND to CSurname! This didn't happen with your listbox, Ari, because your form contained no fields for the listbox to change. Oh well, at least I know it can work, in theory.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [SOLVED] Read the second column in a listbox

Post by Arineckaig »

You spotted the problem before I could draft a warning.

I think the issue goes back to my original post which discouraged your suggested use of a List Box without a bound field to obtain access to the second/reference column. By putting an entry in the Data field you are in effect linking the List Box to that bound field. An unbound List Box in Base has limited utility and is not suited to getting the second column which is then largely surplus to requirement.

For that reason I suggested the use of a quite separate unlinked form but still located in the same form document with a table control that displays a list - a pseudo list box. By selecting a record from that list an event can trigger your macro to read the other required column from that selected record.

I should mention that in my database the List Box does over write its bound field which happens to be in a different table. I doubt that would be a suitable work around for your purpose.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: [SOLVED] Read the second column in a listbox

Post by evwool »

See my post 'Adding an unbound subform to an existing form', Ari. Read it and weep!!
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [SOLVED] Read the second column in a listbox

Post by Arineckaig »

See my post 'Adding an unbound subform to an existing form',
I seriously doubt that Base will permit you to add an unbound sub form to an existing form - a sub form is meaningless if unbound. It was for that reason that I suggested
a quite separate unlinked form
- in other words an extra main form. Base is remarkably flexible in this respect. If you right click on the very top line of the Form Navigator it gives you the option of "New" and then select "Form". You can then add form controls to that new extra form.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: [Solved] Read the second column in a listbox

Post by Arineckaig »

FWIW I have attached a revised version of the ODB to illustrate the use of a pseudo List Box.
Attachments
ListBox2ndColREV1.odb
Extended version
(16.56 KiB) Downloaded 643 times
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
evwool
Volunteer
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: [Solved] Read the second column in a listbox

Post by evwool »

Fantastic. I'll learn more from picking that apart than I would from anything.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
Post Reply