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.
Hello,
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)
oField.commit()
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")
oField.commit()
oSubForm = oForm1.getByName("ECNDetails")
oSubForm.reload()
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).
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
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 http://www.mediafire.com/file/tm48ys2y41lwzbb/Project%20Tracking%20Test%20V1.16.odb
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?
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?
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)?
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 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.
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.
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.
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.
You mention I should not use macros if i don’t understand them. Macros look very complex and to fully understand them and to be able to create them effortlessly would I imagine take a considerable amount of time. Where the macro is very small and simple then yes I can read through it and understand line by line what is going on and can sometime adapt the macro myself. Where I can not adapt a simple macro I come here and ask for help.
More often than not help is usually forthcoming. I can then study the solution or suggestion and a lot of the time see how this has been arrived at. This gives me the benefits of learning a little at a time and being to progress a little at a time with my project. When a solution, usually a macro is too complex for me to adapt as I am very new to Lo/Oo then I will ask for additional help or look for another solution.
“…and it works every time so quite confused.” means you don’t understand the code. This has been shown multiple times now in different questions you have presented. You probably still have not referenced the OOME document.
You also state: “Macros look very complex and to fully understand them and to be able to create them effortlessly would I imagine take a considerable amount of time.”. So you are saying it’s OK for someone else to do the work and you get all the benefits for free?
And again “… or look for another solution.” which means more wasted effort by the person giving the answer.
The people answering questions here not only have put in considerable time in learning the product, but also expend time in providing answers to questions and resolving problems which are sometimes (as in this case) non-existent. Why should we provide FREE consulting work to anyone not willing to put in effort themselves? If it was easy there wouldn’t be hardly a question here.
It is also your responsibility to relay just what is going on on your end. How can can someone answer a question when you are dealing with a problem only you can see? The above is a good example. You present a ‘problem’ sample for people to deal with but you are actually looking at something different! How can anyone resolve issues in this manner?
You’re not even reading the sent text! Had asked days ago for info which it seems you have ignored - just like referring to OOME for some basics.