[RESOLVED] multi listbox getting started

[tennisadam]

Hello - I'm using VBA in Access 2000 on Win2k.

I have a link table in my project linking products to categories.


I would like to have a combobox (drop-down) with categories, and two listboxes with the products (one for unassigned to the category, one for assigned to the category). I would like to be able to select a category, and then have the two listboxes populate. I would like to be able to move items back and forth between the two listboxes, and then hit some command button to update the database table accordingly (add, or delete rows as necessary from the productcategorylink table).

I do not know how to approach this task. I have made a form in access, but it doesn't work at all.

If someone could first give me a high-level overview of how I can approach this task.

For example, I think I need to make 3 unbound form objects (the one combobox and two listboxes). I also don't really know what bound is vs. unbound?

I'm not sure how to populate them, ie, is this something you can do on the form loading? You update the listboxes on a combobox selection (click). I can write the sql.

Also, how do you get the data into the database? I've been using the access datagrids and other forms which require no update or insert statements, because they are linked to the tables. Here is my thought: a command button which calls a script that grabs the value of the combobox and iterates through the not assigned list to delete any rows of the combobox-unassigned combinations, and then also iterates through the combobox-assigned combinations to insert rows where necessary.

I need some high-level guidance now... if my approach sounds okay, what is the way to access these objects and the methods for adding / removing, selecting and deselecting them?

Thanks in advance.

[dglienna]

are you doing this in access or vb?

[tennisadam]

I am doing this in Access -- using VBA - I think it is like VB6. I can create a form in design view, and then click to go to the Microsoft Visual Basic editor.

[dglienna]

No, VBA and VB are very different. This thread will be moved to the OFFICE DEVELOPMENT forum shortly. You can find a link to it the next time you look for it.

[Hack]

Moved from ClassicVb

[tennisadam]

Thanks for moving it. That might explain why some of the things I was trying that I found in the classicVB forum were not working. Any suggestions would help.

[DKenny]

Is the product to category relationship many-to-many or many-to-one?

[tennisadam]

The database relationship is many-to-many.

A product can belong to many categories. And, a category can have many products.

[DKenny]

I would like to be able to select a category, and then have the two listboxes populate.

Use the CHANGE Event of the Combo to call the REQUERY Method of the two lisboxes - passing the VALUE of the Combo to the SQL as a condition in the WHERE clause.

I would like to be able to move items back and forth between the two listboxes, and then hit some command button to update the database table accordingly (add, or delete rows as necessary from the productcategorylink table).

Use the CLICK event of the listboxes to either INSERT to or DELETE from the productcategorylink table and then call the REQUERY Method of the two lisboxes, (to refresh the 2 lists).

[tennisadam]

problem has been resolved. project is completed! Thanks