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

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
Eur0star1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Are you using form for inserting data? Normally this table could be subform in form with Contracts. Look at sample
DBContracts.accdb
0
EirmanChief Operations ManagerCommented:
You can avoid the complexities of many to many relationships by using multi value fields ....
IF the list of values is not long

http://www.techrepublic.com/blog/microsoft-office/how-to-use-the-new-multivalue-field-in-access-2007-2010/
http://www.webucator.com/blog/2010/06/creating-a-multi-value-field-in-access-2010/
=======================================================================
You don't explicitly enter data into a link table.
Once you enter data into your forms, entries are autmatically entered into "The behind the scene" join table.

There is a huge amount of data out there on "Many To Many" relationships ...  a few examples
http://office.microsoft.com/en-gb/access-help/create-relationships-for-a-new-database-RZ101772998.aspx?section=9

http://www.health.ufl.edu/~prcowart/
Have a look at the "Examples" folder and download some sample databases (ans a useful doc file)

You can't beat a real working example. I have a database (somewhere) with a many-many relationship.
I'll post it on Monday.
0
Dale FyeCommented:
in your context, is a 'Scanner' a person, or a device (not that it really matters).

Normally, when I need to do this, I'll use a combo box, and two listboxes.  In the combo box, I'll display either the Contract, or the Scanner.  For this example, lets assume that we are using Contract.  Then, in the listbox on the left (lstAssigned), I'll display the Scanners that are mapped to that contract with a query that looks something like:

SELECT S.ID, S.Name
FROM tbl_Scanners as S
INNER JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE CS.ContractID = Forms!yourFormName.cboContract

Then, in the listbox on the right (lstUnassigned), I display the list of Scanners that are not mapped to that contract, with a query that looks something like:

SELECT S.ID, S.Name
FROM tbl_Scanners as S
LEFT JOIN tbl_ContractScanners CS
ON S.ScannerID = CS.ScannerID
WHERE NZ(CS.ContractID, 0) <> Forms!yourFormName.cboContract

This 2nd query should identify all of the scanners, not assigned to the contract displayed in your Contract combo box.

Then, I include a couple of buttons (positioned between the two lists boxes) which allow the user to assign the scanner to the contract or to remove the scanner from the contract.  These buttons start out being disabled and are enabled / disabled in the Click event of each of the listboxes.  If the users clicks into the left listbox, then I enable the Remove button and disable the Add button.  Reverse that for the listbox on the right (scanners not assigned).

Then, the code behind the Add button looks something like:
Private sub cmdAddScanner

    Dim strSQL as string
    
    strSQL = "INSERT INTO tbl_ContractScanners(ContractID, ScannerID) " _
                & "Values (" & me.cboContract & ", " & me.lstUnAssigned & ")"
   currentdb.execute strsql, dbfailonerror
   me.lstAssigned.Requery
   me.lstUnassigned.Requery

   me.cmdAdd.Enabled = false

End Sub

Open in new window

The code to remove a scanner from a contract would look like:
Private Sub cmdRemoveScanner

    Dim strSQL as string

    strSQL = "DELETE FROM tbl_ContractScanners " _
                 & "WHERE ContractID = " & me.cboContract _
                 & "AND ScannerID = " & me.lstAssigned
    Currentdb.Execute strSQL, dbfailonerror
    me.lstAssigned.requery
    me.lstUnAssigned.requery
    me.cmdRemove.Enabled = false

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Dale FyeCommented:
NO, NO, NO!  

Do not use multi-valued fields if you ever want to query on those values.

I do not know a single professional Access developer that would encourage you to use those.  Learn the proper techniques for dealing with many to many relationships.
0
EirmanChief Operations ManagerCommented:
NO, NO, NO!  
Do not use multi-valued fields if you ever want to query on those values
I agree totally and I don't recommend them.
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.
0
Eur0star1Author Commented:
Thanks for the quick response, its now Sunday morning here and I trying Dale's suggestion of a combo box and two listboxes.  This works very well.

I've added a button which loads a notinlist form to enable scanners (name and a serial no) to added.

I have two issues:

When I'm adding a "new" contract; at the point I try to add a scanner it reports an error "You cannot add or change a record because a related record is required in table 'tblContracts'" .  Any thoughts on an efficient way to handle this ?

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 !

I am browsing Eirman's list of bits & pieces this is proving interesting.

Thanks
Brian
0
Dale FyeCommented:
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.

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.
0
Eur0star1Author Commented:
My Combo is set up as you suggest.

Your approach works well for updates.  I can also add a new contract firstly, without scanners, then update the contract with scanners later.

On adding a new contract with scanners I would (I think) have a similar issue with another form.

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.
   Else
delay all updates till user completes the addition of the scanners and then add the contract then the links.

How do you handle this situation ?
0
Dale FyeCommented:
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.
1.  Contracts: this form would be used to add all the necessary details regarding the contracts.  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.  Until the record is saved, the ContractScanners button or option would be disabled.
2.  Scanners: this form would be used to add all the necessary details regarding the scanners.  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.  Until the record is saved, the ContractScanners button or option would be disabled.
3.  ContractScanners: This form would allow you to select any Contract from a combo box on the form.  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.  Whenever the combo box changes, the two lists (lstAssigned and lstUnassigned would be requeried.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
To sum up what the others have said; Access can't model with forms a M-M relationship directly.   You only can do a Parent / child (one to many) relationship with a main/subform combination.

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.

And no points please....I just want to sum up what was said.

Jim.
0
Eur0star1Author Commented:
Thanks for all your inputs:

I am happy with the concept of many to many -  my issue is how to handle them in Access which Dale certainly clarified - I now have a working system now.

Thanks
Brian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.