Ask Your Question

Default text or record in a listbox

asked 2018-04-06 11:50:26 +0200

MaaX gravatar image

updated 2018-04-06 14:47:39 +0200

Hi guys, when i open my form ECNListByCustomer it defaults to the last saved record. How can I have the form open with the Customer listbox showing the first entry in the customer list as default (sorted ascending)?

At the same time I would like to see the ECN List listbox showing the first entry (sorted ascending) without pressing the refresh button.

As a bonus the replacing the refresh button with a macro would be great for this form if possible?

As always any help appreciated.

link text C:\fakepath\Project Tracking Test V1.14.odb

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-04-06 20:08:43 +0200

Ratslinger gravatar image


Most of what you have been asking thus far is already in many samples and/or documents. An excellent reference for macros is OOME by Andrew Pitonyak.

As a bonus.. This is not a game show or contest. Again, another very common solution presented many places across the net & documents.

Setting a list box to a specific item -

Sub SetListBox
    oForm1 = ThisComponent.Drawpage.Forms.getByName("MainForm") 'Get Form
    oField = oForm1.getByName("txtCustomer")
    Doc = ThisComponent
    DocCtl = Doc.getCurrentController()
'Get VIEW for listbox
    CtlView = DocCtl.GetControl(oField)
    CtlView.selectItemPos(1, True)
End Sub

The index for entries in a list box is relative to 0. Since this list is generated from SQL, the first item is blank and therefore it is needed to be set to 1. This sub can be run from the Open Document event of the form.

Since the customer list box also has an event to refresh the ECN list box you can add the setting of the selected item there also. However the item to set there is 0 since the items there are based upon a valuelist which has no blank entry.

To have the subform refresh without using a button is just attaching a simple macro to an event of the listbox upon which it is based -

Sub RefreshSubForm
    oForm1 = ThisComponent.Drawpage.Forms.getByName("MainForm")  'Get Form
    REM Next two lines only needed because of the way you have constructed form
    oField = oForm1.getByName("txtECNList")
    oSubForm = oForm1.getByName("ECNDetails")
End Sub

It is becoming clear that this project you are putting together is being done piecemeal. As you think of something you patch in a solution. A good example is this form you presented here. It would seem this form may be better off using a table filter for the main form instead of a data table. You should also avoid macros whenever possible which requires you to better understand Base & SQL. If you do choose to use macros you need to spend a great deal of time in learning not just using your language of choice but also the interaction with UNO API (the harder part of the two).

edit flag offensive delete link more


Very sorry for the delay in reply as I had not even noticed a reply on this post. As usual all comments and helpful suggestions are appreciated. The piecemeal comment is fair comment. I did plan the database and requirements on paper. I then listed the tables I believed I would require. I also read through a tutorials related to a medical database creation. However due to a learning process (on here) I have updated the forms a few times in an effort to make the data entry process more streamline

MaaX gravatar imageMaaX ( 2018-04-12 17:22:10 +0200 )edit

I have used the above SetListBox macro but it only randomly seems to work. It took me a short while to work out how to add to Open Document event. I opened the form in question in edit mode and added using customise. Hope i have added the macro correctly?

Database Link

MaaX gravatar imageMaaX ( 2018-04-13 11:56:29 +0200 )edit

Not sure why you state it only works randomly. I don't see a problem. Perhaps you don't understand the process. The Open Document event is triggered only when the form is first opened. Each time the form is opened normally, the macro is run & the Customer list box is set to 'Choose Customer'. That is what the macro is set to do. If you are editing the form & switch using Design Mode icon, the macro doesn't execute since the form is opened already. Where is the problem?

Ratslinger gravatar imageRatslinger ( 2018-04-13 14:51:05 +0200 )edit

So i tried again and get the same result. I downloaded the same file from here just to make sure I had not changed anything and I still get the same result. Open database, go to forms, double click ECNListByCustomer. Sometime the customer has the desired "Choose Customer" in the field but more often than not it is blank. If it opened with blank every time I could understand this and would expect i could spot my error easily. I cant understand why it works sometimes and not the other though?

MaaX gravatar imageMaaX ( 2018-04-16 10:18:14 +0200 )edit

Must be something on your end. Cannot get it to fail. Always opens on "Choose Customer". Have tried now many times. Only suggestion is to try running in Safe Mode.

Also, have just reviewed your previous questions (cursory look) and do not see on any information of the specifics with which you are using. What version of LO? Your OS? What database (most likely HSQL embedded)?

Ratslinger gravatar imageRatslinger ( 2018-04-16 15:28:06 +0200 )edit

i have opened in safe mode, enabled macros, opened the form and find that in safe mode the macro does not seem to run. That is to say that the field is always at customer 0, even when i push the manual button "push button" which runs the macro it does not work. However macros in general are working as the refresh button on the same form is working. When i open the macro it opens showing a BASIC runtime error. it says property or method not found:Drawpage. and macro line is high lighted blue.

MaaX gravatar imageMaaX ( 2018-04-18 11:49:58 +0200 )edit

@MaaX First problem is my fault. Was trying to get easy method of resetting user profile by having you start in safe mode. This, however, will not allow macro execution. The other method is to reset (make backup of user profile) the profile - see LibreOffice user profile

But it seems much of your problem is lack of understanding macros. Should not use if you don't understand. Also, communicating what is going on.

Ratslinger gravatar imageRatslinger ( 2018-04-18 18:21:16 +0200 )edit

First, there is no Customer 0 that I can find in the sample. Second, there is no button on the sample I see. Third, when opening a macro, if you double click to open it is really trying to execute the macro. In that case (being run from the IDE) there is no Drawpage. Drawpage is only available on the form itself.

If you going to continue to use macros you must put in the time to educate yourself. The best document is the OOME as specified in the answer above. Also communicate better.

Ratslinger gravatar imageRatslinger ( 2018-04-18 18:23:09 +0200 )edit

Your last comment has information which does not exist. It is not certain you are referencing the same sample I am. I am using your version 1.16 as supplied in your comment after you applied the macro.

Ratslinger gravatar imageRatslinger ( 2018-04-18 18:23:33 +0200 )edit

You are correct. I had failed to update the uploaded V1.16. I had made a minor change to the customers and added customer 0 to be inline with record 0. I had also added a push button to trigger the same macro. The push button triggered macro works every time even though its the same macro being triggered from the open document event. Its only failing when used from the open document event. I have tried the exact same method in a different database and it works every time so quite confused.

MaaX gravatar imageMaaX ( 2018-04-19 09:20:40 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-06 11:50:26 +0200

Seen: 238 times

Last updated: Apr 06 '18