multi select list to table

[ChristineMD]

Working iwth catalog data - 24 catalogs per year, within each catalog there are up to 7 "offers" - so I have this huge long list of catalogs/offers that a user could selet any combination thereof to generate query/report

I have populated a multiselect list box on a form with all the catalog/offer info from a query - no problem. HAve the extended flag to true/yes. The form works.
Want to take selections from this multi select list box and load into a temp table that will be used to filter further queries/reports.

I've seen a lot of variations on this theme in my googling, but they seem way more involved for what I need. I think. I'm a VB noob. All the ones I've run across seem to have multiple list boxes on the form, or the results are going to a text string, etc. Can't find examples of exactly what I'm looking for.

Using access 2010

Results will be one field: "offer" but there will be multiple offers selected, want that list of offers loaded into a table.

I've seen murmurings that I need to be concerned with users unselecting items in the list? Is this true? The selections will be reset each time the form is loaded -the selections don't need to be saved from instance to instance if this makes sense. They're going to open the form, make selections, click a button which will create the temp table. Then they'll have another form to select a report/query to run which will already be joined to the temp table.

So looking for an example of loading mutli selects from a list box into a table.

I always get such great responses from this group, so I thank you in advance. Please let me know if further info is needed!

 

[xenou]

Hi,
I'm not sure of your setup exactly but you can script the updates from the values in your listbox(es). With a simple two-column listbox this is an example:

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Command2_Click()
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM TEMP;"
    [COLOR="Navy"]With[/COLOR] Me.List0
        [COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] .ListCount - 1
            [COLOR="Navy"]If[/COLOR] .Selected(i) [COLOR="Navy"]Then[/COLOR]
                s = "INSERT INTO TEMP (Category, Description) VALUES ("
                s = s & "'" & .Column(0, i) & "',"
                s = s & "'" & .Column(1, i) & "'"
                s = s & ");"
                DoCmd.RunSQL s
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]Next[/COLOR] i
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    DoCmd.SetWarnings True    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

note that I will "truncate" the temp table before updating it - you will want to have some such mechanism to "clear" the old data from previous runs when you are using a temp table.

Sample DB:
<a href="http://northernocean.net/etc/mrexcel/20120906_.zip">[Sample Database]</a>
sha256sum: f044b0b22d9a4f7baa917f645c7324f37e3dd47cecda02822312591ae5ba0eca

 

[ChristineMD]

Thanks very much! I will give it a shot

 

[xenou]

I always get very confused with listboxes so be sure to read the help files in Access VBA as you go. There's usually three different ways to do everything, and a few pitfalls for the inexperienced - just be patient and post your troubles back here.