Advice on base form design

[EvilOverlord1]

Crossposted on Reddit
I’m looking for advice on how best to design a form for my database.

I run a small press, publishing mainly anthologies. Each book/title can have multiple authors, ISBNs (book identifiers), and stories. Each ISBN has a long list of characteristics.

The problem is that for each title, I want to show not only fields for that title, but fields for each associated ISBN. Because there are so many ISBN fields, I need a good way to show them for multiple ISBNs. At the moment, I have two table controls (see image). I would have used a tab to show different versions of a table control, but there’s no tab control in base, and I found it hard to manage two table controls that exist in the same part of the form and control visibility with a button.

I’d appreciate constructive advice on how to show all the info I need. I’m okay with a control/tab/button to show different bits of the ISBN info, since I don’t need it all at once (if I can figure out how to design the table controls). I do want to show all associated ISBNs at once (but it could be, e.g., ISBN 1, ISBN 2, ISBN 3; data A,B,C and then a button for ISBN 1, ISBN 2, ISBN 3; data A,D,E - with A overlapping for easy identification. The white boxes below are table controls for the ISBN data at issue. It’s just … inelegant as is.

FYI, the fields are:

• ISBN - long number, must be visible
• Format - text (hardcover, paperback, ebook)
• Trim - print book size (e.g., 6x9)
• Pages - pagecount
• Pub date - publication date
• USD/GBP/EUR/CAD/AUD - prices
• URL - public URL for book
• IS URL - another URL
• UUID - long code, clipped, because I just need to know it’s there and be able to copy it
• IS/KDP/D2D/CS/SW - checkboxes (maybe there’s a better way to handle these?)

I’m satisfied with the database structure, but since some have asked for detail, here it is:

[RobertG]

You better show the tables in relationship of your database.

• You will need a table for “title” → “tbl_title”
• You will need a separate table for “authors” → “tbl_autor”. Primary key of “tbl_author” would be foreign key for “tbl_title”.“author_ID”. So you could create a table control as subform of “title”
• Same as for authors will be for ISBN.

Show an example of the database, then we could have a look at the form…
.
Edit:
.
Had a look at the relationship:
“Books” is datasource of MainForm.
“ISBNs”, “JOIN Creator-Book” and “Story Appearences” are datasource of the subforms of “Books”.
“Imprint” is a listbox in MainForm. You have to fill the content for “Imprint” directly in the table or in a separate form or parallel to MainForm.
“Creators” and “Stories” are content of listboxes in the tablecontrols of subforms.
“JOIN Creator-Story” is a separate form with listboxes for “Creators” and “Stories”.
Content for “Creators” and “Stories” have to be filled in a separate form or in forms parallel to MainForm.
.
Now open the form for editing and show the content of the form navigator.

[cpb]

@EvilOverlord1,
so you have 20 fields to display but your form only has enough room for 10.
this issue has nothing to do with the structure of the database it’s purely about form design.
I’ve gone with your suggestion of overlapping table controls.
I have 2 queries which act as the data-source to 2 internal forms “sISBN_part_1” and “sISBN_part_2”.
both queries select their data from the table “tISBN”.
“qf_ISBN_part_1” selects the fields which are handled by “sISBN_part_1”.
“qf_ISBN_part_2” selects the fields which are handled by “sISBN_part_2”.
each of the inner forms contain a table control and a button control, the buttons fire a very simple macro which toggles the visibility of these controls.

.
“sISBN_part_1”:
the properties of this form are the default: add, modify, delete.
.
“sISBN_part_2”:
this form is a subform of “sISBN_part_1”, its properties are that of a filter form, only modification is allowed.
“ISBN” is the linked field and the fields “ISBN” and “Format” are read only.
you to show the field “Format” in both table controls, I have assumed it is the same field shown a second time for reference only.
.
the fields “ISBN” PK and “BookID” FK in the table “tISBN” are obviously set not null, all other fields in the same table are set null.
.
it’s important to understand that both forms are referencing different fields of the same record.
the only issue that I could invoke was caused by failure to insert a PK value after inserting data into other fields and hitting the button “Hit For Page 2”.
if the undo icon is ever activated it’s because the PK value is null so just hit the button “Hit For Page 1” and insert a PK value or hit the undo icon.
.
I have carried out minimum testing so cannot vouch for the reliability of the method used.
.
EDIT: I viewed the link to “REDDIT” after and not before my first post, it prompted this edit.
assuming the required form area can be made available then it’s possible to display/edit the selected record in its entirety.
I have added a second form to the attachment, it’s identical to my original form with the following caveats:
no visibility enabling/disabling, no need for buttons or macros, the table control in form “sISBN_part_2” is repositioned, it is 3 rows in height.
you can of course alter the height/position of the table controls.
I inserted 1 new record to test.
I have replaced the original attachment.
.
EDIT 2: replaced attachment, have added Form_2, shows all ISBNs for selected book, I was sceptical but seems it can be used for input.
OverlappingControls_3.odb (37.1 KB)

[Villeroy]

@EvilOverlord1

IS/KDP/D2D/CS/SW

This is a many-to-many relationship. See yesterday’s topic Can I create a drop down list in a Form, that has check boxes to each item in the list? - #3 by Villeroy

extended by publication types IS/KDP/D2D/CS/SW:
Books_Authors.odb (21.2 KB)

[robleyd]

Cross posted on Reddit where there has been some discussion.

If you cross post, as a courtesy please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.