Post reply

Junction Table and List Boxes

  • July 21, 2008 at 8:05 am

    #381807

    Hello All:

    I am working on an access .adp which is basically being used for call

    tracking. I have several related combo boxes and list boxes on a

    form. As well as free form text boxes. 2 of these list boxes are

    multi select. What I'm trying to do is save the selected items in a

    table.

    (I set up junction tables in SQL to handle this. Not sure if this is

    the right approach)

    The idea would be that the user could go back to a record and have the

    same items selected in a list box and change them if necessary. As

    far as reporting, the (Junction?) table would store individual records

    for each item selected.

    I'm familiar with list boxes when I use them for menu controls etc.

    Though I usually requery them. So there is never a real need for me

    to save the data. I've also looped through ItemsSelected to pass

    criteria to a query.

    Please let me know if there is a recommended approach for this. Or if

    there is another more efficient way of accomplishing this.

    Thanks in advance for any help and let me know if you need more

    information.

  • July 23, 2008 at 4:07 pm

    #847433

    I have created views in SQL for the combo boxes. This may work for you too.

  • July 24, 2008 at 1:01 pm

    #848041

    How did you use views for combo boxes? I'm not sure if this is similar to what I'm trying to solve.

  • July 24, 2008 at 1:20 pm

    #848047

    In the form design open the properties for the combo box, then select the row source. It will allow you to select a table, view, store procedure or function in an ADP.

  • July 25, 2008 at 7:25 am

    #848367

    I took another approach for a similar situation. I would just change the query that's the rowsource for each list box and then requery it, and there would never be a situation where I would show the items from the junction table as "Selected". Instead, I had a 2nd list box with the remaining possible selections, whose query source was an "opposite" of the query for the primary list box. There were also two command buttons - one to add selected items in the 2nd list box to the data in the junction table, and then requery the 1st list box, and one to do the opposite. Any selected items are cleared in the process. This way, you only have to enumerate the ItemsSelected for figuring out what to add or remove, rather than constantly worrying about which items are selected. From a logic perspective, it was easier to deal with. It may or may not make sense for you, but it worked really well for me. FYI...

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • July 28, 2008 at 3:25 pm

    #849492

    Steve:

    Thanks for the response. It definitely makes sense to me, but I'm kinda strapped with screen real estate. So I can only use one list box. As a matter of fact, I have others on the form that will have to follow the same logic.

    What I'm doing now is creating stored procedures to add and remove the items from the list.

    My next step will be to use a query from the junction table to list the selected items in the list box.

    Have you (anyone?) done anything similar to this?

    Essentially, 2 queries would be feeding the list box. One for all the data and one for the items selected.

    Help is greatly appreciated.

    :unsure:

  • July 29, 2008 at 6:31 am

    #849721

    Ok, so screen real-estate is at a premium. I don't see any reason why two queries shouldn't work, as long as selecting an item gets it added to some table somwhere. Just curious, but how will you drive the updates to that table?

    The only alternative I can think of would be to instantiate two tables for the duration of the application, and if it's multi-user, the table names would likely need to contain the user ids, or some other similar identifier, and I'm not sure that would be all that good a solution.

    In my case, I have a single table that is being inserted into after selecting items in one list and then clicking a command button, which effectively adds them to the other list via the requery method. A separate button allows selections from the "other" list to be removed from that table, and again, a requery updates the contents.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply