<div>
Are you using form for inserting data? Normally this table could be subform in form with Contracts. Look at sample<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w45/881957/DBContracts.accdb" target="_blank" class="file-inline" title="sample (512 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>DBContracts.accdb</a>
</div>


DBContracts.accdb

<div>
You can avoid the complexities of many to many relationships by using multi value fields ....<br />
<b>IF</b> the list of values is not long<br />
<br />
<a href="http://www.techrepublic.com/blog/microsoft-office/how-to-use-the-new-multivalue-field-in-access-2007-2010/" rel="ugc">http://www.techrepublic.com/blog/microsoft-office/how-to-use-the-new-multivalue-field-in-access-2007-2010/</a><br />
<a href="http://www.webucator.com/blog/2010/06/creating-a-multi-value-field-in-access-2010/" rel="ugc">http://www.webucator.com/blog/2010/06/creating-a-multi-value-field-in-access-2010/</a><br />
==========================<wbr />==========<wbr />==========<wbr />==========<wbr />==========<wbr />=====<br />
You don't explicitly enter data into a link table.<br />
Once you enter data into your forms, entries are autmatically entered into &quot;The behind the scene&quot; join table.<br />
<br />
There is a huge amount of data out there on &quot;Many To Many&quot; relationships ... &nbsp;a few examples<br />
<a href="http://office.microsoft.com/en-gb/access-help/create-relationships-for-a-new-database-RZ101772998.aspx?section=9" rel="ugc">http://office.microsoft.com/en-gb/access-help/create-relationships-for-a-new-database-RZ101772998.aspx?section=9</a><br />
<br />
<a href="http://www.health.ufl.edu/~prcowart/" rel="ugc">http://www.health.ufl.edu/~prcowart/</a><br />
Have a look at the &quot;Examples&quot; folder and download some sample databases (ans a useful doc file)<br />
<br />
You can't beat a real working example. I have a database (somewhere) with a many-many relationship.<br />
I'll post it on Monday.
</div>


<div>
NO, NO, NO! &nbsp;<br />
<br />
Do not use multi-valued fields if you ever want to query on those values.<br />
<br />
I do not know a single professional Access developer that would encourage you to use those. &nbsp;Learn the proper techniques for dealing with many to many relationships.
</div>


<div>
<blockquote>
NO, NO, NO! &nbsp;<br />
Do not use multi-valued fields if you ever want to query on those values
</blockquote>
I agree totally and I don't recommend them.<br />
They are handy for amateurs etc (as are macros) or quick simple databases ..... and are very hard to step back from without a database redesign.
</div>


<div>
Thanks for the quick response, its now Sunday morning here and I trying Dale's suggestion of a combo box and two listboxes. &nbsp;This works very well.<br />
<br />
I've added a button which loads a notinlist form to enable scanners (name and a serial no) to added.<br />
<br />
I have two issues:<br />
<br />
When I'm adding a &quot;new&quot; contract; at the point I try to add a scanner it reports an error &quot;You cannot add or change a record because a related record is required in table 'tblContracts'&quot; . &nbsp;Any thoughts on an efficient way to handle this ?<br />
<br />
Having added a scanner to the tblScanners these do not show in the lstUnassigned - so I need to modify the query to include these scanners !<br />
<br />
I am browsing Eirman's list of bits &amp;&nbsp;pieces this is proving interesting.<br />
<br />
Thanks<br />
Brian
</div>


<div>
I don't generally use that same form to add contracts or scanners to those tables, although you could use the NotInList event to do so for the Contracts.<br />
<br />
My combo box assumes that you have a ContractID and a ContractTitle in the combo rowsource, and that the ContractID is the bound column, usually hidden.
</div>


<div>
My Combo is set up as you suggest.<br />
<br />
Your approach works well for updates. &nbsp;I can also add a new contract firstly, without scanners, then update the contract with scanners later.<br />
<br />
On adding a new contract with scanners I would (I think) have a similar issue with another form.<br />
<br />
I think I need to save the new contract record just before adding a scanner (some DAO command built into the add and delete buttons ?) but this may need undoing if user cancelled the addition of the contract.<br />
&nbsp; &nbsp;Else<br />
delay all updates till user completes the addition of the scanners and then add the contract then the links.<br />
<br />
How do you handle this situation ?
</div>


<div>
I would generally have three separate forms, all three of which would be accessed from some form of a switchboard, and the ContractScanners form would also be available from both the Contracts, and Scanners forms.<br />
1. &nbsp;Contracts: this form would be used to add all the necessary details regarding the contracts. &nbsp;There would be a button or popup menu on this form that would allow me to get to the ContractScanners form, but only when the Contracts form is not a NewRecord. &nbsp;Until the record is saved, the ContractScanners button or option would be disabled.<br />
2. &nbsp;Scanners: this form would be used to add all the necessary details regarding the scanners. &nbsp;There would be a button or popup menu on this form that would allow me to get to the ContractScanners form, but only when the Scanners form is not a NewRecord. &nbsp;Until the record is saved, the ContractScanners button or option would be disabled.<br />
3. &nbsp;ContractScanners: This form would allow you to select any Contract from a combo box on the form. &nbsp;If the form is opened from within the Contracts form, then the combo box would default to the contract that is currently displayed on the Contracts form. &nbsp;Whenever the combo box changes, the two lists (lstAssigned and lstUnassigned would be requeried.
</div>


<div>
To sum up what the others have said; Access can't model with forms a M-M relationship directly. &nbsp; You only can do a Parent / child (one to many) relationship with a main/subform combination.<br />
<br />
So for a M to M, you need two and in the subform, you include a combo to the other side of the M to M.<br />
<br />
And no points please....I just want to sum up what was said.<br />
<br />
Jim.
</div>


<div>
Thanks for all your inputs:<br />
<br />
I am happy with the concept of many to many - &nbsp;my issue is how to handle them in Access which Dale certainly clarified - I now have a working system now.<br />
<br />
Thanks<br />
Brian
</div>


<div>
<div class="content wysiwyg-content">
Am using 2010/13 Access, 32-bit and the basic database structure is:<br />
<br />
Clients &nbsp;1&gt;m &nbsp;Contracts &nbsp;1&gt;m &nbsp; Link &nbsp; m&lt;1 &nbsp;Scanners &nbsp;1&gt;m Reports<br />
<br />
I'm able to create and add Contract Records and Scanner Records to the appropriate Tables but I cannot work out how to add the link records to the Link table. &nbsp;Typically a contract has 1 to 3 scanners and I would be grateful if you could tell me which event(s) to use and suitable code (DAO ?) to use to add the links. &nbsp;The Link table holds the ScannerID and ContractID and am intending adding an insert date to the link. &nbsp;Some direction on deleting these links would also be useful.<br />
<br />
I have searched but have found the best article s are SQL based but a direction to suitable (for beginners !) articles would be appreciated.<br />
<br />
Thanks<br />
Brian
</div>
</div>


Am using 2010/13 Access, 32-bit and the basic database structure is:

Clients  1>m  Contracts  1>m   Link   m<1  Scanners  1>m Reports

I'm able to create and add Contract Records and Scanner Records to the appropriate Tables but I cannot work out how to add the link records to the Link table.  Typically a contract has 1 to 3 scanners and I would be grateful if you could tell me which event(s) to use and suitable code (DAO ?) to use to add the links.  The Link table holds the ScannerID and ContractID and am intending adding an insert date to the link.  Some direction on deleting these links would also be useful.

I have searched but have found the best article s are SQL based but a direction to suitable (for beginners !) articles would be appreciated.

Thanks
Brian

How / where can I add records to Junction Table in MS Access ?

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.