Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

create form to add records in multiple tables

I am new to access (I have only 2003 version) and I am getting crazy. Consider also that I am a novice with DB in general, so now I am at the point that I am very confused... please help. I am trying to design a database to catalogue all the books I have. I created the tables and set the relationships Image of my DB ; I have a few tables related to the “Books” one in Many-to-Many relationship (e.g. Authors: each book can have multiple authors and each author can “have” multiple books) and a few others in a simple One-To-Many (e.g. book type: one book can be of one type only, but there can be many book of that type. Now I would like to create a single form (with sub-forms if needed) to populate my tables. I would like to be able to add a new book and select from, for examples, existing authors or add the author anew; same for data in the One-to-Many relationship.

How can I do this?

I am really lost, do I need multiple forms and to add them as sub forms of a main one, do I need queries, or what?

like image 225
rodedo Avatar asked Aug 26 '12 15:08

rodedo


People also ask

How do you Create a form that displays multiple records?

On the Create tab, in the Forms group, click Multiple Items. If Multiple Items is not available, click More Forms, and then click Multiple Items. Access creates the form and displays it in Layout view. In Layout view, you can make design changes to the form while it is displaying data.

How do you add multiple tables in Access?

To create a multi-table query: Select the Query Design command from the Create tab on the Ribbon. In the dialog box that appears, select each table you want to include in your query and click Add. You can press and hold the Ctrl key on your keyboard to select more than one table.


1 Answers

The above design requires four subforms. Each subform should be based on the junction table with book id as the link child and master field and a combo box based on the relevant table for the second table id.

For example, your first subform is Authors, the table it is based on is Libri_Autori

Link Master Field: Id
Link Child Field: SchedaLibro

Combobox:

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 records to the authors table, you need to set Limit To List to Yes and run code on the Not In List event. I like to use a small pop-out form to add items to the "back ground" tables. This may be easier with Access 2010, because you can set a ListItemsEditForm


1, The book form, just before adding the subform. Note that Use Control Wizards is selected. This is the default, so unless you unselected it, it should be fine.

Step 1 Using a wizard

The various wizard steps for adding a subform Wizard steps for adding a subform

Selecting the subform fields

Selecting the subform fields

Selecting the link child and master fields

Selecting the link child and master fields

The form showing the subform control highlighted in yellow and the control properties

Subfrom control

2, Adding the combo

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

Right-click for change to combo

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

Step 1 combo type

The second step is to choose the table or query

Step 2 choose table or query

Step three is to choose the fields

Step 3 choose fields

Step four chooses the sort order and is not displayed here, this is step five, which is to set the column widths

Step 4 skipped, step 5 set column widths

Step six is to set the Control Source

Step 6 set Control Source

You will end up with a combobox with the properties illustrated

Subform combo and properties

Final form

Final form

like image 197
Fionnuala Avatar answered Sep 19 '22 12:09

Fionnuala