2

I have a simple two table database in Access 2010 tracking landowners and properties. They are related in a many-to-many (M:M) relationship with a link table. Forms for viewing data have been generated from two queries following tutorial here. There are two forms with subforms: 'landowners and their associated properties' and 'properties and associated landowners'.

I would like the option for users to either (1) add a new record to the subform (creating a relationship record in the link table and a new record in the source table for the subform) or (2) select from a list of existing records to relate to the property / landowner.

So far, I have (1) working. In the subform, if a new record is inserted it populates the appropriate table and creates a relationship. If the record is deleted from the subform, the relationship is deleted from the link table but the record is NOT deleted in the subform's source table (child table) -- as desired.

I have attempted a drop-down combobox providing the option to select from existing records in the subform's source table but I get a duplicate error. Any help?

Improve this question
3
  • 1
    You are probably better off asking this at superuser.com since the issue is related more to using MS Access than anything to do with database design. You have implemented your many-to-many with an intersection table. That is the proper way to do it. You're more likely to find help using Access subforms at SuperUser than here.
    – Joel Brown
    Commented Jan 30, 2013 at 15:11
  • thx I was wondering about that. Is there a problem with cross-posting?
    – r4gt4g
    Commented Jan 30, 2013 at 16:01
  • I'm glad you got your answer here. Regarding cross-posting it is generally frowned upon, sometimes with extreme prejudice. It's always better to post in one spot first and then try another site if the experts on the first think you'd be better off at another Stack Exchange site. It can be hard to decide which is the best since they do overlap in many areas.
    – Joel Brown
    Commented Jan 30, 2013 at 18:03

1 Answer 1

Reset to default
3

The general idea is that you create a subform that's bound to your link table, and make one of the fields in the subform a combo box bound to the child record ID that's in your link table. Fill the combo box using records in the child table.

Then you handle the NotInList event on the combo box to prompt the user to add the record to the child table (or whatever is appropriate for your application).

NotInList Event

Then if you want to actually remove rows from the child table when they are no longer referenced by the link table, handle the appropriate deletion event on your subform, and check for unreferenced records.

I've implemented something like this once, but I think it was in Access 2000 on a machine I don't have with me at the moment. I'll try to check it later out of curiosity.

Improve this answer
3
  • thanks. I changed the PropertySubform 'source object' property to the link table. I can view the fields in the link table but they are only keys - it won't allow me to add the fields from the related child table nor change them to combo boxes.
    – r4gt4g
    Commented Jan 30, 2013 at 15:56
  • 2
    You'll have to edit your subform, and have the child table's foreign key in the link table replaced with a combo box bound to that foreign key. Put multiple columns in the combo box to show a description from the child table rather than the key value. If you want to have multiple columns from the child table represented in your subform, you'll have to instead bind the subform to a query that joins the link table to the child table. It's not pretty, but I've done this with a task management database to display the completion percent and such.
    – db2
    Commented Jan 30, 2013 at 16:00
  • Excellent that did it. Don't apparently even need to code a NotInListEvent. Thanks!
    – r4gt4g
    Commented Jan 30, 2013 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.