Help with populating a list box with data from a table

[modfather]

First, I have to admit that I'm not a real heavy Access developer, so please be gentle.

I have three tables involved here:
Customers (master list of customers)
Codes (master list of codes)
CodeCust (list of codes that applies to customers)

I'd like to do two things:

1. When adding customers to my application, I'd like to create a "multi-select" list box that is populated from the Codes table. The user should be able to select one, none, or many of the codes.
2. On "Save", I'd like to create CodeCust records for that customer, with all of the codes chosen in the list box.

Can someone give me a hand doing this? I'd really, REALLY appreciate it.

Thanks!
modfather

 

[mas5]

This can get a little complicated. First, make sure your listbox is mulitselect (see the property sheet). Then in the on click event of your "Save" button you will need to go through all the selected items of your listbox and add a record to your table.

To add a record to your table you can create a query (see below) or open up an ADO recordset within your VB code. Here's the query (qryAddRecord):

insert into tblCodeCust (customer, code)
values (forms!FORMNAME.CUSTOMERID,
forms!FORMNAME.lst.itemdata(varitem))

(The lst.itemdata(varitem) will make sense later. )

To go through the list box and execute the insert query:

dim strqry as string
dim varitem as variant
dim success as boolena
dim lst as listbox

strqry = "qryAddRecord"
set lst = me.listbox 'whatever your list box name is
if lst.itemsselected.count > 0 then
form each varitem in lst.itemsselected
docmd.openquery stqry, acNormal, acEdit
me.listbox.selected(varitem) = false 'clears out the selection
next varitem
end if

You'll have to do some checking to make sure the relationship doesn't already exist or your query will fail for duplicate values. You can check that before the updates or just populate that list box with codes that the customer doesn't already have.

I have not actualy tested this query to see if it will work. I'm only concerned about it picking up the value from the list box properly. You may have to play with this a bit, but I hope you get the general idea. As noted above, you can also do this with ADO code, but this might be simpler.