I am creating an editing form for a dataset that uses subforms. The row to be edited is selected using a listbox. Works fine so far.
When creating a new entry I need to first create the entry on a different form, to allow the subforms that need to be edited also to be connected through the index relationship.
Of course the user is unhappy, as this two-step approach is actually a 3 step approach: (1) creating the new row, (2) updating the listbox, (3) selecting the newly created entry and (4) continue entering data into the new entry.
I there a better way? I expect some support for the user can be done with some macro code that updates and automatically positions the listbox, but it seems to me this is not a preferred solution. Any support for a better way is appreciated!
Hello,
Some items are not clear:
.
Should be all available on one form. As soon as as your main record is entered the sub form, if properly set, will be linked and any records properly recorded.
.
.
Why select new entry and continue entry? Or is this just to select an entry so the sub form data can be entered?
.
Some clarity on the process would help as well as a sample of the issue you appear to have. To do things the way stated in the question would require a macro but fail to see why your process is the way you describe.
The built-in method goes like this:
- You try to find a listbox entry that does not exist yet (Doe, John).
- You add a new record to the table that is referenced by the list box.
In most practical use cases, a new record requires more than one value (forename +surname +birth date +phone). This is why you have to edit another table. - Go back to the form with the list box and focus the listbox.
- Push the second refresh button on the navigation toolbar to refresh the listbox having the focus. The first refresh button refreshes the entire form with all contained listboxes and subforms and jumps to the first record when finished. The second button is greyed out when no listbox (or combo box) has the focus.
Improvement #1:
It is possible to add another form to the form hierarchy which is bound the the table referenced by the listbox and which is an independent main form, not a subform. This way you don’t have to switch windows in order to store a new record. Set the form “New entry only” which is more convenient because it saves you the navigation.
Improvement #2:
Install a tiny little Python macro of mine and use it with the additional main form. It refreshes the specified listbox automatically after you added, updated or deleted a listbox item. It can update more than one listbox, combo box and/or entire forms.
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=88831
- You find a listbox entry missing
- Move into the additional form and enter all required data for a new record.
- Hit Enter which triggers a default button with action set to “New record” which also saves the just entered one triggering the setup autorefresh macro.
- Move back to the listbox and type the new listbox entry. It will be availlable.
Doesn’t even make sense. If you add a new record, and in this case on a different form, why only add a name and then use another form to add the rest of the information. And why would it be a different table? More duplication.
Thanks for asking, I was not clear enough. Second try, will be more specific this time:
Table has many entries, users do some updating the table data (both data on the table itself and on tables linked to it) more often than creating new entries.
Best way for editing is a list box with relevant, sorted information that loads the text and other fields as well as the linked tables when selected.
I could make a duplicate of that form without the list box for newly created rows, but am trying to avoid a duplication of the form for “new entry”.
As the form is connected to the list box that uses data from a query of existing entries, I do not see how I possibly could position the list box to “new entry” to allow to create a new row. (Maybe I miss something here, I didn’t see a way to achieve this)
So I thought the best way out would be another (top level) form on the same page that firstly creates the row by collecting only the data needed for the selecting list box, and creates this way the new entry I then can select and this way keep all the form with as little duplication as possible.
Hope this makes clear where I come from, and answer the questions about my problem that you have had.
Many thanks for asking back and providing support!
The list box is based on a query on existing entries, so I do not know how to position it to an entry that does not exist (or even better: a new entry).
The users I address are having difficulties using the navigation toolbar. So I have to build all the buttons needed on the form itself.
Things do work, but the correct way is not smooth for the users as they need to press two buttons and position a list box after entering the minimum data (that then supplies the list box) before entering the rest of the data: 1) submit the new entry. 2) refresh the list box (in order to get the created entry listed also), 3) position the list box to the entry just created, and commit the position / refresh the data.
Tank you for all the hints. Maybe I was not clear enough: It actually works, but the UI is poor. I try to avoid macro programming as good as possible, and if needed I try to use “very few line” macros only: the main difficulty is NOT creating the macros but maintaining them over time (and changing personnel)
The listbox writes another table’s primary key into its linked foreign key field.
https://forum.openoffice.org/en/forum/download/file.php?id=35497 is a database with contained AutoRefresh macro and a many-to-many relation where each movie may be associated to many genres.
Form “Genre_Movies” shows genres in the main form, associated movies of a selected genre in a listbox of a subform grid. The details of a selected movie appears in an additional subform. You can add a new movie in a separate form. The new movie entry will be added to the listbox automatically.
Form “Movies_Genre” shows movies in the main form, associated genres of a selected movie in a listbox of a subform grid. You can add a new genre in a separate form. The new genre entry will be added to the listbox automatically.
Hello,
With the list box as main form, person test as sub form and Zahlungen as sub sub form there is a form PersonTest. No macros. Select from list box existing items. Use button to obtain. If new entry is needed, select blank line in list box and use Get Record button for new entry. Once entered, you can update the list box with that item if really needed by pressing the Refresh ListBox button.
Hopefully this is what you are looking for →
NewWithListBox.odb (198.6 KB)
Edited version adding in your totals.
Tanks very much. The empty line is the perfect solution. For unkown reasons I overlooked the empty line in the list box, and tried hard to create something alike… Sometimes the solution is closer than expected.