[Solved] Cannot commit form field update from macro.

[ColinH]

I am new to oo and I owe a lot of you many thanks already as this forum has been a big help getting started. So Thank You!

However now I am truly stuck. I am trying to update a formatted field (fmtFingerTreatmentID) on a subform from a macro. This field along with other fields is then used to create a database record via a button to "Save Record". In my application I am actually setting this field to the result of an SQL query. However to simplify problem diagnosis (and this request for help) I temporarily replaced the code with a simple hard coding which still demonstrates the problem. Here is the simplified code:-

sub PrimaryFingerUpdate2(oEv)
dim oForm as object
dim oField as object

oForm = oEv.Source.Model.Parent
oField=oForm.getByName("fmtFingerTreatmentID")
oField.text = 7
oField.commit

End sub

The code appears to work correctly. I see a 7 appear onscreen in fmtFingerTreatmentID field when this macro is triggered. It remains in the field when I press the button to create database record and the database record is created. Everything works...................except....... the database field (integer) populated by fmtFingerTreatmentID is set to zero, rather than 7 (although 7 is still displayed correctly on screen in fmtFingerTreatmentID).

But here is the thing. If I then repeat the process from scratch but pause between macro setting 7 and database update, and simply over type the 7 in the field (ie replace 7 with 7) manually, the database update is performed correctly. Clearly my code does not (despite appearances) actually set a value of 7 in fmtFingerTreatmentID as thorougly as typing it does.

Suspect I am maybe using the wrong property of oField but currentvalue is read only and don't know what other than text to use.

Any help much appreciated. Thanks.

[Arineckaig]

Welcome to the forum. Please bear with me as it is difficult to offer constructive advice without knowing the level of your experience with Open Office macros.

If you are indeed new to OpenOffice, my strong advice would be to avoid resort to coding macros unless you cannot avoid them. OpenOffice BASIC is relatively straightforward but its API is complex and not well documented. Learning your way around the API is a fascinating but steep learning curve. It is generally a more productive use of one's time initially to learn how to make extensive use of Base without resort to macros than trying to master the API. IMHO leave the latter until you can devote unlimited time and tolerance to survive through its inevitable frustrations. See http://user.services.openoffice.org/en/ ... 00&t=40444 and in particular item #4.

I realise, however, this advice will appear less than helpful if you have macro skills and experience, so in that case would offer these pointers.

Your code is updating the 'model' of of the form control ("fmtFingerTreatmentID") but that action alone does not commit the change to the database. For example, you will probably have seen no change in the colour of the 'Save Record' icon when you ran your macro.

If your code were to apply the change to the 'view' of the form control, the update will be reflected in the changed colour of the 'Save Record' icon and the record can then be saved in the normal way: in effect, this essentially replicates use of the Base GUI to enter new data directly into the control.

Other potential routes could be for the code to change the 'boundField' property of the form control, or for the code to update directly the relevant data column in the underlying database.

[ColinH]

Arineckaig, thanks very much indeed for your prompt and very comprehensive reply. (And a Happy New Year to you!).

I have an IT background and was a mainframe Cobol developer for a long time, but I am totally new to oo and object orientated coding. However I have learned enough to appreciate that your advice is good - there is a lot to learn about macro coding and initially I should avoid it if possible.

Maybe you could tell me if you think there is a way I could do what I'm trying to do without macros. The task in hand is a very straightforward one and I'm sure it is a very common challenge when designing forms. Its a customer database for a manicurist and the form in question is the new appointment form to capture date for the appointment table. This includes the FingerTreatmentID of the treatment (manicure, false nails, hand wax etc). The treatments are stored on the treatment table which includes treatmentid, treatmentname treatmentcost etc.

I could easily change the formatted field, fmtFingerTreatmentID to a combo box which displays all the treatmentIDs from the treatment table and invites user to pick one. That would be simple. However displaying meaningless numbers is not very user friendly. I want to display meaningful treatment names in the combo box, but actually capture the corresponding treatmentid on the appointment table. My approach was to introduce a combo box which displayed treatment names from treatment table. When it changes a macro is triggered to obtain corresponding treatmentid from treatment table and stick it in the fmtFingerTreatmentID (which will actually be hidden in final version of form).

Can you think of a simple "non-macro" way to do this?

In any event, having got this far with the macro based solution I will probably persevere with it in this instance, assisted greatly by your advice in this respect. The fact that a form control has a "view" and a "model" is news to me. I have to confess I don't really understand these terms. Clearly I need to get googling!

This I will do and hopefully correct my code and mark this post as solved. Is there anywhere you would recommend I go to get a good initial understanding of the principles behind views and models and any other properties form controls might have?

Thanks again for your prompt and excellent advice. It really is much appreciated.

[Arineckaig]

However displaying meaningless numbers is not very user friendly. I want to display meaningful treatment names in the combo box, but actually capture the corresponding treatmentid on the appointment table.

Please allow me a little time to come back with a more comprehensive answer, but a quick reply is possible forthe point quoted above. In Base this operation is best handled with a list-box as opposed to a combo-box. The former permits display of user friendly data from any field or combination of fields taken from any table so long as that table is linked/related to the table that is the data source for the form containing the list-box. The second field of the 'List content' property of list-box should be the PK of that other table, but the bound field property of the list-box should be the FK field of the table that is the form's data source. In this way selection of a user friendly item displayed in the list-box automatically updates the FK field of the form's data source table according to the PK field of the selected item. Similarly the list-box will read the the FK data from the data source table but display the relevant user friendly field(s) from the other table where that table's PK matches the source FK.

I will try to come back soon with a more comprehensive answer and hopefully a relevant example, but meanwhile have a look at a demo database (prepared for another purpose) that can be downloaded fromhttp://db.tt/cfOR6TFy. The demo is concerned with many-to-many relationships but you will see there how use is made of list-boxes to display user friendly data.

Edit: Since this post I prepared a more suitable file attached to the next post below to demonstrate the use of a list box specifically to list related, but more user friendly data, from which selection of an item will update a numeric 'foreign key' field.

[Arineckaig]

May I too wish you a happy and successful New Year with Base.

I have now had time to give further consideration to your post and have attached a sample database illustrating the use by Base of a list box for handling the one-to-many relationship between two tables. In such cases the many side table will use a foreign key ID field and its integer data type is seldom user friendly for display in a text or numeric field control in a data form sourced from that table.

Other field(s), however, from the one side table can be used for the display if a list box is used instead: any selection from the resulting more user friendly list will still ensure the foreign key field in the many table is then matched correctly to the relevant primary key in the one table.

The attached databse file is a crude example and simply for demo purposes, its form document includes a (read-only) numeric field control to show directly the content of the "TreatmentID" field, though in normal use this control would not be included. The list box depends on three key properties, under the data tab of its Properties dialog accessed through the edit mode of the from document.

First, the "Data field" property is set as the "TreatmentID" field from the APPOINTMENTS (the many) table. Secondly, the "Type of list contents" property is set as "Sql" and the "List content" property as a SQL statement:

Code: Select all

SELECT CONCAT("FirstName" || ' ' || "LastName"), "ID"  FROM "TREATMENTS" ORDER BY "FirstName"

The first element of this SELECT statement often would be a single field but in this example the CONCAT function is used to demonstrate how the column in the list box can, if required, display data derived from more than one field of the TREATMENTS (the one) table.

The second element of the SELECT statement is the ID field (the Primary Key) of the TREATMENTS (the one) table that is to be linked or 'bound' to the Foreign Key field of the the APPOINTMENTS (the many) table. Thus the third key property of the list box is the "Bound field" property: it contains an index (starting at zero) to the column of the SELECT statement that contains the data to be linked to the 'bound' (or foreign key) field in the APPOINTMENTS table.

Is there anywhere you would recommend I go to get a good initial understanding of the principles behind views and models and any other properties form controls might have?

Ultimately you will need to rely on the "Developers Guide" to be found at:
http://wiki.services.openoffice.org/wik ... pers_Guide
It is a comprehensive but formidable document. An easier but more limited starting point might be:
http://www.baseprogramming.com/resources.html where you will find two introductory PDF files "OOo Basic Db Development" and "Forms and Dialogs" for downloading.

I would, however, still caution against embarking on that route until you have gained a fair degree of familiarity with how Base makes use of list boxes, SQL commands and its form/sub-form paradigm.

[ColinH]

Once again Arinecraig, a big thank you.

The way bound field property works in a list box is, of course, exactly what I was looking for. I am marking this record as solved. Thanks for clear explanation.

And as well as resolving my immediate problem, you have given me a good start towards understanding macro coding.

Cheers!