Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Forms for editing/inserting/deleting many-to-many records

    I have two tables in a many-to-many relationship using a junction table. First table is "vehicles" the second table is "events". One vehicle can experience multiple events (i.e. lease, sale, crash), and the same event can happen to multiple vehicles (i.e bulk buy)

    I have created a form that shows each vehicle, with a datasheet subform that shows all the events related to that vehicle. The display works great.

    Now, however I would like to insert/modify/delete events related to the vehicle. For insert/modify I envision opening up a new form that contains all the information for the event. For delete, I obviously just need to push a button.

    Does anyone have any good examples on how to create all of this?

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For deleting you can use (air code):
    Code:
    Dim dbs As DAO.Database
    Dim strSQL As String
    
    strSQL = "DELETE FROM Events WHERE <Event Id> IN (SELECT <Event Id> FROM <Junction Table> WHERE <Vehicle Id> = <Desired Vehicle Id>);"
    Set dbs = CurrentDb
    dbs.Execute strSQL
    Set dbs = Nothing
    Replace the elements between brackets by their proper names or values.
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Posts
    57
    Interestingly enough I've managed to get Access deleting automatically. Two things are required to make it happen:
    • Include the key columns in the subform table - even if they are not displayed
    • Set the relationship to cascade


    Unfortunately, it is the inserting new records that is much more of a challenge and I still haven't figured out.

    Carlos

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •