Microsoft access tips

Many to many relationships / junction tables e.g. books & authors
The steps below allow the user to enter details about a book and details about the authors of the book all on one form.

1. Create table "Books" with "bookid" as autonumber & primary key
2. Create table "Authors" with "authorid" as autonumber & primary key, and surname, firstname etc as text other fields
3. Create table "BookAuthors" with "bookid" and "authorid" as numbers & non-primary keys
4. Relationships - add tables Books, Authors, BookAuthors - link bookid to bookid and authorid to authorid with referential integrity ticked
5. Form wizard - add tables Books, Authors, BookAuthors, include bookid from Books and authorid from BookAuthors and leave out other -ids as well as the surname from authors, use Books as main form and rest as subform
6. Form design - select subform, cut, goto design ribbon, choose tab control, draw tab control on form, select a tab control page, paste
7a. Select authorid (in the subform), change to combo box, click the "..." by properties-data-row source to create query, inside the query design add table Authors, add authorid as first field, and any other fields to sort by and display (e.g. surname of author), exit and save the query
7b. Properties-format - change column count to match number of columns used in the query, change column widths (use 0 for the first column so it won't be displayed e.g. 0;2.5cm;... ) and change list width to the total width of the column widths
7c. Properties-data-Limit to list - change to Yes (so not in list event can be triggered)
7d. Properites-Event-On Not in List - [Event Procedure] build -

Private Sub AuthorID_NotInList(NewData As String, Response As Integer)
strSQL = "Insert Into Authors ([Surname]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
End Sub

8. Right click on bookid on main form and set properties-all-visible to No

Explanations:
Steps 1-4: Junction table is useful for Many to many relationships. The BookAuthors table links books to authors and allow one book to have many authors

Step 5-8: This creates the main form based on Books and a subform for storing the authors of each book, bookid from Books must be present on mainform so the BookAuthors subform can link to it, bookid can be set to hidden or grayed out etc later on (e.g. in step 8), and authorid from BookAuthors must be included so the other fields on Authors (included in the subform) can link to it. The subform has BookAuthors so user can select the appropriate author from the Authors table. The subform also has other fields from Authors so user can update the author details as well. The surname field is left out because authorid (steps 7a-d) will be setup to substitute for the surname field (7a 7b displays surname and 7c 7d allow user to input new surname)

Other tips

1. #name? error: if a "string" is used in assignment use """string""" (three double quotes) instead.
2. if you change the name of a control, be sure to update the name in the vba module as well
3. if the form uses more than one tables and you can't set one of the keys to null (3162 error "You tried to assign the Null value to a variable that is not a Variant data type.") then check the record source and change INNER JOIN to RIGHT JOIN. (see http://objectmix.com/ado-dao-rdo-rds/212806-you-tried-assign-null-value.html )
4. if the combobox is based on a query and shows #deleted after records got deleted then setup the OnEnter event to requery the combobox e.g.

   Private Sub PatientID_Enter()
   Me.PatientID.Requery
   End Sub