Alphabetical List Boxes

Creating and using forms
Post Reply
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

Alphabetical List Boxes

Post by crawfy1 »

I am trying to create a List Box/Combo Box that allows me to enter data into a table. As the Database grows these List Boxes display the data from the field they entered them into.
I achieve this with no problem.

What i need is for the data displayed in the List Box/Combo Box to be in alphabetical order and not displaying any duplicates.

I follwed some of the posts already on the forum but can't get it to work here.
In the List box control i have selected SQL in the "Type of list contents" and typed SELECT "DVD/CD Name" FROM "Movie-Collection-TBL" ORDER BY "DVD/CD Name"
This does give me an alphabetical list but does not refresh unless i close then reopen the form. It usually just adds the new data to the bottom of the list and does not alphabetise them until i close and reopen the form. Also, I cannot get it to not display duplicates.

I tried building a Query and selecting this rather than the SQL statement. I can remove the duplicates but again it does not refresh unless i close then reopen the form.

Any help would be greatly appreciated.
OpenOffice 3.2.0
Win 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Alphabetical List Boxes

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

Re: Alphabetical List Boxes

Post by crawfy1 »

Thanks, I'll give this a go but don't think it will work. The difference is that i only have 1 table i am working with. Not 2 like in the other persons post.

The data entered into the table by the form then needs to be accessible by the same form for future entries into the one table.
OpenOffice 3.2.0
Win 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Alphabetical List Boxes

Post by Villeroy »

Then you don't have the problem of putting unique IDs from another table.
Use a combo box for this purpose with list content
SELECT DISTINCT "DVD/CD Name" FROM "Movie-Collection-TBL" ORDER BY "DVD/CD Name"
The combo box makes suggestions for text entries. Nevertheless you can enter any text. List boxes enforce referencial identity.
Both, list and combo box, can be refreshed without reloading the form. The navigation toolbar has 2 refresh buttons. One for the entire form and the other one for a selected list/combo.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

Re: Alphabetical List Boxes

Post by crawfy1 »

Do i need to manually select the refresh buttons in the navigation bar or can it be set up automatically? Could i link it to a push button that refreshes the control/form and selects "New Record" at the same time? (Which i think i can do with a macro) If so, how do i select "yes" automatically when i get the warning "the content of the current form has been modified. do you want to save your changes"?
OpenOffice 3.2.0
Win 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Alphabetical List Boxes

Post by Villeroy »

You've got to write a macro or perform one additional click when the combo needs to be updated.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

Re: Alphabetical List Boxes

Post by crawfy1 »

I know it sounds i'm lazy, but how would you include the extra "click" in the macro to select "yes". I tried this when recording the macro but it did not repeat when i ran the macro.
OpenOffice 3.2.0
Win 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Alphabetical List Boxes

Post by Villeroy »

You've got to write a macro which is not the same as recording a macro.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Alphabetical List Boxes

Post by Villeroy »

Well, if you can write a macro there is a tool which helps a lot. Indeed, it can be used to record a macro while you browse through the object hierarchy. This is what the Santa Clause extension (aka MRI) spilled out for you:

Code: Select all

Sub Snippet(Optional oInitialTarget As Object)
  Dim oSource As Object
  Dim oModel As Object
  Dim oParent As Object
  Dim oObj_1 As Object

  oSource = oInitialTarget.Source
  oModel = oSource.Model
  oParent = oModel.Parent
  
  oObj_1 = oParent.getByName("Combo Box 1")
  oObj_1.refresh()
  oParent.moveToInsertRow()
  
End Sub
Replace "Combo Box 1" with whatever the name of your box is.
Set your button's Action property to "None" and assign the snippet to the "Execute Action" event.
 Edit: Obviously, the above code does not store the modified form to the database. Does anybody know how to do this? oParent.insertRow() raises a sdbc.SQLException "Function sequence error". Is this just another Base API bug or is it something serious? 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
crawfy1
Posts: 16
Joined: Fri Jun 18, 2010 11:27 am

[Solved] Re: Alphabetical List Boxes

Post by crawfy1 »

Thanks for the help, and Merry Christmas.
OpenOffice 3.2.0
Win 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Alphabetical List Boxes

Post by Villeroy »

You see why I try to stay away from Base macros although Base is the OOo component I use most of the time. It is simply not worth the effort in most cases.
oParent is the parent object of the button which was calling the macro.
oParent supports a method called insertRow() which is documented like this: http://api.openoffice.org/docs/common/r ... #insertRow
But you can not call the valid method of a valid object to do what it is supposed to do. I have a rough idea what could be done to make this work actually. But this time I am too lazy to write tons of complicated code I would never use myself.

Merry Christmas
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply