Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Unanswered: many-to-many link

    i've helped a client normalize a relationship but he has a problem with the access front end, and i don't know how to explain how he should proceed

    there used to be a product master table with 3 fields, named cat1, cat2, cat3, which allowed a product to be listed under up to three categories

    i've now replaced those columns with a many-to-many table

    product_categories
    product_id
    category_id

    (by the way, these are linked tables, in a sql server database)

    the question now is, in the access front end, how does he make the change?

    before, there were 3 dropdowns, which fed the 3 cat fields when a new product was added or an existing product updated

    now, i think he needs to have one dropdown, and allow for multiple selections, each of which should populate a new row in the many-to-many table for the given product

    how is this done? is the dropdown called a combo box? how do you get it to generate multiple rows?

    what's the approach here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A list box would allow for multiple selections; a combo box will not.
    Paul

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    By the way, an alternative might be to use a subform to choose/present the selections.
    Paul

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks paul, i will pass this on (it doesn't mean anything to me, i'm not an access front-end guy)

    i cannot say for certainty that it was combo boxes being used for cat1, cat2, cat3

    but if these are now to be replaced with a list box, how do you get each of the multiple choices to automatically insert a separate row into the many-to-many table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That's the easy part. Here's a sample:

    Code:
      Dim ctl         As Control
      Dim varItem     As Variant
      Dim strSQL      As String
    
      Set ctl = Me.lstDriveConcern
      For Each varItem In ctl.ItemsSelected
        strSQL = "INSERT INTO tblDiagDriveConcern " _
              & "VALUES( " & Me.DiagID & ", " & ctl.ItemData(varItem) & ");"
        CurrentDb.Execute strSQL
      Next varItem
    Paul

  6. #6
    Join Date
    Jul 2004
    Posts
    6

    agree with pbaldy. easiest way is to use a subform

    link the subform by your product-id and use a single combobox for the category per line.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the feedback, i will pass it on...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2006
    Posts
    2

    Red face Paul's (r937) Befuddled Client

    Hey Guys...

    Appreciate the suggestions on putting together a fix for my Access form. I took your advice and created a subform, but I can't get the values of my categories table to display in my list box and I can't make another selection.

    The db is posted for download at:

    http://www.asgca.org/showcase/db.zip

    I'm willing to pay an hourly rate bounty if one of y'all can get my subform to relate to the Showcase form entries and allow multiple category selections in the subform, both in add and edit mode.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This is related to r937's question? As a golfer, I'm intrigued, but the linked tables don't connect to data, so it's hard to tell what's going on. Perhaps you could create a sample with local tables, that don't require a remote connection.
    Paul

  10. #10
    Join Date
    Dec 2006
    Posts
    2

    Yup IT's related

    Paul,

    Yup. it's related to Paul's question. The dbs hook up with SQL server tables. If you're interested I can porvide you with information to create an ODBC connection.

    Pat

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have a list myList with multiselect: yes
    myList.rowsource = "SELECT product_categories, product_id, category_id FROM yourM2Mtable ORDER BY whatever"

    'user makes the selections

    dim varItem
    dim strSQL as string
    for each varitem in me.myList.itemsselected
    strSQL = "INSERT INTO sometable(product_categories, product_id, category_id) " _
    & "VALUES(" & me.myList.column(0, varitem) & ", " & me.myList.column(1, varitem) _
    & ", " & me.myList.column(2, varitem) & ");"
    currentdb.execute strSQL
    next

    above for numerics.
    if text (e.g. the last one) add two single quotes just inside the double quotes
    ", '" & me.myList.column(2, varitem) & "');"

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm interested. You can pm them if you don't want to post, or email to me @gmail.com.
    Paul

Posting Permissions

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