Access 2007 VBA : Building a listbox with selection choices from another list box

views:

337

answers:

Access 2007 VBA : Building a listbox with selection choices from another list box

So there are 8 categories that may be associated to each order, but not necessarily all of them. So i was going to build a list box that allowed the user to double click each of the category they wish to associate when they have an "Order Detail" form opened up (unbound form that has hidden text boxes with all needed ID numbers).

I want to have another empty text box right beside it that will allow me to append the selections (up to 8) so the user can see that they have been added.

So one list box with the default choices, and when a choice is double clicked, it adds that choice to the second list box to see the tally so to speak.

What is the VB for getting something like this done?

Thanks Justin

+3 A:

I suggest that you are making life difficult for yourself. Create a subform with the Order Detail table and a combobox that allows the user to select the various categories.

Remou 2010-05-27 13:36:32

So I am not sure I follow you on this one.....Just to ellaborate a bit further....So there is an Orders table, and a form that opens up data to one order at a time (unbound form). Once you are into one individual order, a data entry control is needed to tag one indivdual order record with upto 8 categories (or whatever is applicable). I know that sounds a little suspect, but what I mean by 'category' are really division tags that maps out what divisions the product has to go through. So i thought the easiest way (given these are unbound forms) would be to take one list box, with the default

Justin 2010-05-27 14:51:56

values (categories/divisiontags), and another use to select choices from the first and build an actual list of applicable values to that orderID...kinda like the Access wizard form idea for crosstab queries. The Order Table is already established, and I wasn't even going to change that....ust add a new Category Table with the Order ID as a FK (I already have it in a hidden text box, since these are unbound forms). So I am defaintely not saying this is the best idea....quite the opposite, and that is why i am asking. I just am not following the above idea...though it may be far more normal??

Justin 2010-05-27 14:54:46

In the scenario you describe, a subform may not be the best bet, the subform would relate to the categories table, but unbound forms are not the usual set-up. Can you have a bound subform in your set-up?

Remou 2010-05-27 15:09:48

i guess i could because its just DAO recordset stuff that moves data into the controls on load depending on previous form/screen selection. to save or update, its just SQL statements behind the button click that save the records, but also give me additional records in other tables for log and change log...i kinda like the unbound stuff better but i suppose just because that is how i learned to do it. Another point is, I would like to use this scenario to learn how to build a "report builder" with the same idea. list box, with selectable fields, and a little visualization of the report selects

Justin 2010-05-27 15:17:45

correction: save/update are just SQL statements in VBA of the button click's sub

Justin 2010-05-27 15:19:03

A report builder is a select, not an update statement, so you are likely to be creating a list from a multiselect listbox to use with IN : WHERE cat IN (1, 2, 3) : WHERE cat IN ('abc','def'). Updates are more work and require more checking. You can run an update statement with For Each <item> in the listbox, but then what about deleting, amending etc etc?

Remou 2010-05-27 15:59:26

One rule of Access is, I think, "if it is getting difficult, you are doing something wrong" :)

Remou 2010-05-27 16:00:51

+1 Remou's Axiom is spot on.

HansUp 2010-05-27 16:17:57

+2 A:

If you want two list boxes --- one for available choices and another for selected items --- and the ability to move items back and forth between available and selected, it can be done with VBA, but is not trivial. See How do I select items using dual list boxes? for an example.

Personally I favor Remou's suggestion if you can make that work for your situation.

HansUp 2010-05-27 14:23:33

thanks Hans! since Remou explained it, i am not sure if this is what I will end up using, though it was what i was talking about in the beginning...thanks!

Justin 2010-05-27 17:50:26

ansaurus

tags:

views:

answers:

Access 2007 VBA : Building a listbox with selection choices from another list box

related questions