[Solved] Updating a lookup table

[robleyd]

I have developed a database that contains several lookup tables linked to a master table as a 1-n relationship. The form for the MASTER Table has several list boxes that extract the data from those lookup tables. All works well until I find I am missing something in one of the lookups. I can easily open the source and add a new record but...... I cannot find any reference to the new record in the lookup from the list box on the Master form without closing and re- opening the MASTER form.

Code: Select all   Expand viewCollapse view

Sub CloseForm
   Dim oForm
   oForm = ThisComponent.DrawPage.Forms.getByIndex(0)    ' Main Form
   oForm.updateRow()
   oForm.dispose()                                       'releases all references
   oForm.close(true)                                     'closes the form
   thisComponent.CurrentController.Frame.close( true )   'closes the window
        oForm.reload()   'refresh SubForm   <<<<<< This has no effect which is what I expected as this is the macro to close the window with the lookup data.
End Sub

How can I show the new entries without having to re-load the master form?

[Villeroy]

The navigation toolbar has 2 refresh buttons. The second one becomes accessible when a list box or combo box has the focus. It refreshes the current control only, staying at the same record position and without reloading the entire form.

A macro to refresh anything anywhere: viewtopic.php?f=21&t=88831

[UnklDonald418]

Another option that doesn't require a macro is to assign a Push Button control to the form or sub-form where the list box control is located. Set the Action property for the Push Button control to Refresh Form.
After you add a new record to the lookup table, press the Push Button to update the selection list for the list box.

[Villeroy]

The draw back when refreshing the entire form is that you lose the record where you wanted to enter a new item ID from the list box.

We can have a separate button to refresh a list/combo box with no macro:
Set button action = "Open Web Page or URL"
URL: .uno:RefreshFormControl
This does the same as the second refresh button on the toolbar when a list/combo box has the focus.

[lsemmens]

I am now getting very frustrated. I was hoping to upload a stripped down version of my program so that you can see my issue and the smallest it will compact to is 1.6Mb so I've re-written it with just a couple of fields. Two issues have come to light, the first is the subject of this thread, the second has only just popped up, which is the error message if I do not move away from the newly added record when i hit the "close" button.

[Villeroy]

Every master can have 0, 1, 2 or more colors while every color may belong to 0,1,2 or more masters?
That would be a many-to-many relation.

[lsemmens]

NO it is 1-n every master can only have one colour. every colour can have multiple masters.

[Villeroy]

1) I stripped the macros and forms andadded a unique index on color names so the table won't accept duplicate names.
2) I let the form wizard create a new MASTER form.The wizard can not produce list boxes. The context menu of any visible control has a submenu "Replace with...", so I replaced the color ID box with a list box. A working list box has a first column of visible text (color names) and an invisible column keeping the primary key of some other table (IDs of colors), ordered by the visible names. See data properties of the form.
3) I added a most simple logical form to the same form document which serves the only purpose to insert a new color name into the color table. Property "New Record Only" and no navigation bar. Notice that the form is not a subform of another form as shown in the forms navigator from which I added a screenshot.
3a) The form has an OK button by default pushed when you finish the name entry with Enter. The action property of the OK button is set to "New Record", so the field is cleared after the new record has been saved.
3b) The cancel button can be used to cancel the entry of a new name.
So far the form works well. You can enter a new name, go back to the main form's list box and then click the second refresh button.
3c) The text box where you enter the colour name is a combo box showing already names: SELECT DISTINCT "COLOUR" FROM "COLOUR" ORDER BY "COLOUR"
4) I added a hidden field to the new-color-form named "AutoRefresh". Its string value points to the other form's list box. I also added a comment to that control. If you installed my AutoRefresh.py module you can easily activate that macro without further customization. Hidden fields are made for macro customization.
5) There is a close button on the form which works without any macro code. See button properties.

[Villeroy]

The color names may not follow any strict rule about admissible color names. If you want to enter any color names freely while still having kind of pre-fill feature, you may consider to drop the color table entirely and replace the table's foreign key (color-ID) with a text field. In the form you replace the list box with a combo box SELECT DISTINCT "Colour" FROM "MASTER" ORDER BY "Colour". A combo box pre-selects existing entries while you are still able to enter any not yet existing name.

[lsemmens]

Thank You Villeroy, that appears to solve it. Rather than a list box, the combo seems to solve two problems. I'll do some mods and perform some testing. If all is well I shall flag this a s solved!

[lsemmens]

That works fine, except there will be no cascade of updates if, say I change Red to Green, not that ever should be an issue. Thanks again