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
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
CERTIFIED EXPERT
Are you using form for inserting data? Normally this table could be subform in form with Contracts. Look at sample
DBContracts.accdb
DBContracts.accdb
Chief Operations Manager
CERTIFIED EXPERT
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.
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.
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.
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.
Chief Operations Manager
CERTIFIED EXPERT
NO, NO, NO!I agree totally and I don't recommend them.
Do not use multi-valued fields if you ever want to query on those values
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.
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
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
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.
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.
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 ?
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 ?
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.
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.
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.
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.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions