Solved

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

Posted on 2014-11-08
11
401 Views
Last Modified: 2014-11-10
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
0
Comment
Question by:Eur0star1
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 39

Expert Comment

by:als315
Comment Utility
Are you using form for inserting data? Normally this table could be subform in form with Contracts. Look at sample
DBContracts.accdb
0
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 23

Expert Comment

by:Eirman
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Eur0star1
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 

Author Comment

by:Eur0star1
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Closing Comment

by:Eur0star1
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now