reload form to synchronize fields with a listbox

English
, , ,
#1

LIbreOffice 6.4.4 Base. Embedded database is firebird. Suppose employee table and department table (not the real ones, but for the sake of explanation). There is an n:n relation between the two so a, empdept table which is mainly just the foreign keys to empid and deptid.
The form with the problem below is exported to a standalone .odt. As such I had to add a macro to it to flush the fields to the database (known problem).
Now create a form for that empdept table, with a listbox for the deptid which shows the deptname from the department table, that is OK.
For the empid, I do the same, but I would like to have the surname and name of the emp showing in the listbox, but it only shows the first one (surname). Annoying, but a minor niggle.
But I want to show also some other fields from the emp table below as read-only from the emp table, that is also OK when filling in a new record. But when I would change a record, these additional fields do not get updated unless I do something like going to next record and then back.
I tried to put somewhere a macro with a form.reload in the form, but that gets in the way of the flush action I have to do.
Any pointer to get out of this catch22???
TIA
Herman Viaene

FirebirdStandalone.odb

Assignment.odt

#2

Hello,

Not certain about the actual question. Main question states synchronize fields with a listbox but details discuss ...additional fields do not get updated unless I do something... so it needs some clarity.

You also mention about names in a list box. This probably is nothing more than concatenation of the names to be listed. As for updating fields, you always must do something. On the form, without changing records, use the Navigation bar and click Save Record icon or add a push button with an Action of Save Record.

Please notify what I am missing in this question.

#3

On your last recommandation: when I press the “Save record” icon, the related fields are not updated. They only change when I navigate out and to that record again.
For the list box, it has as sql select surname, name,empid from emp and the link field is 2. That is OK as far as the logic is concerned, but the listbox only shows the surname values. And it is not the width of the listbox that is too small. The additional fields I refer to are the surname, name and the emp’s clasification category, that should follow the selection made on the emp listbox.
I guess I would have to use some stattement to refresh the values of the individual fields, not the form itself. I’ll have to do some reading to get there.

#4

Hello,

For the list box SQL, as mentioned in my comment, concatenate the fields:

Select "surname" || ', ' || "name", "empid" from "emp"

Then change the link from 2 to 1.

For the standalone, have no insight as to where your problem is. My tests work fine. Attached is my sample. Register the database as FirebirdStandalone. The Writer document has the Flush macro installed. The form contains three methods to save data - Navigation toolbar, Navigation control, push button.

Sample DB ------ FirebirdStandalone.odb

Sample Writer Doc ----- StandaloneDoc.odt

Edit 2020-11-27:

Have looked at your sample.

  • Your Flush macro is not attached to or called by any event. Because of that no saving. The same S_FLUSH macro you have can be used for both the form and the sub-form. Attach to After record action event in each.

  • You should have Navigation controls for both main and sub form. This allows for Saveing data and having the macro(s) run

  • There is no need for “framing” sub-forms or sub-sub-forms (etc) and if visual isolation is wanted there is a Group Box

Once these changes were made (they are in concert with what was presented in my sample) all records were saved without issue. Also created new Assignment records. Form can modify employee records but is not meant for new records.

Again, it matters not that you have form & sub-form as to why the records are not saved. It is the assigning of the macro.

Edit #2 on 2020-11-27:

Just to answer the main title question, you can just use a push button to reload a form or sub-form. Just set the Action property to what you want and place the button on the proper form/sub-form.

Edit 2020-11-28:

Sample ----- Latest.odt

Don’t really know if this is what you want. If it is, have no idea why.

#5

If still having problems, you should submit a sample (no personal or confidential information) just as I have done. Just edit your original question and attach there.

#6

Hello,

Based on the new information, no button pushing by user. Just don’t understand your problem. Don’t know what the user is selecting and how they are selecting it.

Here is some code for the reload. Don’t have any other clue as to what you want.

For code to reload your sub-form:

Sub my_reload(EVENT)
  Dim oForm As Variant
  Dim oSub As Variant
  oForm = EVENT.Source.Model.Parent
  oSub = oForm.getByName("SubForm")
  oSub.reload()
End Sub

Can’t suggest where to attach as I am not certain what is being changed anymore.

If a selection is made in the main form list box, reloading the sub-form will have no effect as the record is not changed so the old link is still active.

#7

Assignment.odtFirebirdStandalone.odb(/upfiles/16064810463644999.odb)Tx for your answers. I think you somehow missed the first sentences of my OP where I describe the tables used. Your odb has only one table. So I used your odb to define my tables and the form that presents the problem.
While doing that, I realized I missed an important item in the description of the problem: the fact that it is a Main - Subform. In the standalone form I submit, I have put the subform in a frame (there doesn’t seem a subform property for that ???).
I have defined the “flush” macro in the form and call it from the form events. What I am still struggling with (and not included in this example) is a macro to do a “subform reload”. Not the coding is a problem, but where to call it from in the form- or from-items events.
Now uploading the files …

#8

@hermanv,

No, did not miss the first sentences. When the problem is records not being saved, it does not matted how many table or forms/sub-forms are used. Will look at your sample.

Have asked before that you not use answers to post additional information. In this case you could have edited the original question and noted this there. Comments are also available but no means to include file links.

Edit:

Please look at what you post - test your posted downloads. You have actually posted the Writer document twice. There is no Base file even though you naming indicates this. Cannot go further.

Edit #2 - Figured out your posting error. Please look at it to see your posted problem.

#9

OK uploaded the odb and the corrected odt in my OP.
To make things clearer (or not???) the data in the subform do not have to send updates to the database. They are there to just show additional info on the emp choosen in the main form empid.
When inserting a new record, that works OK, but when I change an existing record in the main form, the info in the subform does not follow unless the user does click somewhere (to save the record explicitely, or to navigate to a next record end back, e.g.).
What I want to achieve is that at the moment the user makes a choice in the main form empid, the info in the subform immediately follows this change without any further action from the user.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.