Create a form to add records to multiple tables

Question

I'm new to access (I only have version 2003) and I'm going crazy. Please also note that I am new to working with the database as a whole, so now I am in such confusion ... please help. I am trying to create a database to catalog all my books. I created tables and established relationships ; I have several tables related to “Books,” one in a many-to-many relationship (for example, “Authors: each book can have several authors, and each author can“ have “several books”) and several others in simple One to one". Many (for example, the type of book: one book can only be of one type, but there can be many books of this type). Now I would like to create one form (with subforms, if necessary) to populate my tables. I would like to be able to add a new book and select, for example, existing authors or add an author again; same for one-to-many data.

How can i do this?

I'm really lost, I need several forms and add them as the main subforms, do I need queries or what?

Answer 1

The above design requires four subforms. Each subordinate form should be based on a connection table with the book identifier as the child and main link fields and a combo box based on the corresponding table for the second table identifier.

For example, your first subformation is the authors, which is based on Libri_Autori

Link Master Field: Id Link Child Field: SchedaLibro 

dropdown:

 Control Source: SchedaAutore Row Source : SELECT Id, Nome FROM Autori Bound Column: 1 Column Count : 2 Column Widths : 0, 2 

Create your book form, and then start adding subforms, the wizards will do most of the work for you.

To add entries to the authors table, you need to set the Limit To List to Yes and run the code in the Not In List event. I like to use a small pop-up form to add items to the back tables. It could be easier with Access 2010 because you can set ListItemsEditForm

---

1, The shape of the book, before adding subforms. Note that Use Control Wizards is selected. This is the default value, so if you have not canceled it, this should be fine.

Various wizard steps to add a subform

Subform field selection

Selection of child and main link fields

A form showing a yellow subform control and control properties

2, adding combos

You can either change the field added by the subform wizard to combos by right-clicking and setting the properties yourself ...

... or you can delete an existing control and add combos using the wizard. The first step is to choose a combo type.

The second step is to select a table or query

Step Three - Select Fields

The fourth step selects the sort order and is not displayed here, this is step five, which consists in setting the column width

Step Six - Install a Control Source

As a result, you will get a comparison with the properties shown

<T411>

Final form

Answer 2

This is an old post, but I am trying to do something very similar. In any case, can you share your finished product, if it is still available?