Access: Multi-Select list box populates multiple rows in table

Hi -

I'm not so good with Access/VBA and need some help.  I have a form that has a combo-box and a multi-select list box.  I'd like the selected items on that combo box and list box to each be inserted into separate records in a table.  For example:

Combo Box: SandwichName
List Box: SandwichIngredients

A form entry might look like:
Combo Box: Ham Sandwich
List Box:  Bread      [Selected]
                Ham         [Selected]
                Turkey    
                Mayo       [Selected]
                Mustard
                Cheese


And I'd like the resulting "Sandwiches" table to look like:

Name                         Ingredients
Ham Sandwich          Bread
Ham Sandwich          Ham
Ham Sandwich          Mayo  


I think I need some VB code to do this.  I found a code snippet elsewhere on EE that I thought might do the trick.  I adapted it a little, and I tried putting this into the Click() sub for the list box "List4":

    Dim i As Integer
    Dim sSql As String

    For i = 0 To List4.ListCount - 1
        If List4.Selected(i) = True Then
            sSql = "INSERT INTO Sandwiches (Name, Ingredients) VALUES (SandwichName,SandwichIngredients)"
            Debug.Print sSql   'diagnostic
            CurrentDb.Execute sSql, dbFailOnError
        End If
    Next i


No dice.  At run time I get an error message that says I don't have enough parameters, that it was expecting two parameters.


Thanks for your help!
Noah
noahisaacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

dim j
with me.listboxname
        if .itemsselected.count>0 then
            for each j in .itemsselected
                  currentdb.execute _
                  "insert into tableX(sandwich, ingredients) values ('" & me.comboname &"','"& .itemdata(j) &"')"
            next

        end if
end with
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick_RickardsCommented:
The Tables...
Supose you 2 tables as follows...

tblSandwhich
   SandWhichID     AutoNumber (Long Int)  (1 side of a 1 to Many Relationship with tblSandwhich
   SandWhich        String Variable

tblSandwhichIngredient
   SandwhichIngredient  AutoNumber (Long Int)
   SandwhichID               LongInt (Foreign Key - Many side of 1 to Many Relationship with tblSandwhich)
   SandwhichIngredient
Just so we have a name for you're form I'll call it "frmSample"

The Form...
Supose you have 2 controls as follows...

Form contains - 1 Combo box named "cboSandwhich" with the following Query....

SELECT Sandwhich FROM tblSandwhich ORDER BY Sandwhich;

.ColumnWiths = 0"
.ColumnCount = 2

The AfterUpdate Event of this Combo box should code as follows...

Private Sub cboSandwhich_AfterUpdate()
    Me.lstSandwhichIngredient.Requery
End Sub

Form contains - 1 ListBox named lstIngrediant that has the following Query in it...

SELECT  SandwhichIngredientID, SandwhichIngredient as Ingredient FROM tblSandwhichIngredient WHERE SandwhichID = Forms!frmSample!cboSanwhich.Value ORDER BY SandwhichIngredient ;

.ColumnWiths = 0"
.ColumnCount = 2

***********************************************************

Although there are other ways to do this this one is especially simple as you can see from the single line of code that is needed to make it work.
0
Rick_RickardsCommented:
Sorry 1 correction to my last post (the query for cboSandwhich was missing a field).  Reposted in its entirety for simplicity...


Is this what you were looking for?
Yes No
Rick_Rickards:
The Tables...
Supose you 2 tables as follows...

tblSandwhich
   SandWhichID     AutoNumber (Long Int)  (1 side of a 1 to Many Relationship with tblSandwhich
   SandWhich        String Variable

tblSandwhichIngredient
   SandwhichIngredient  AutoNumber (Long Int)
   SandwhichID               LongInt (Foreign Key - Many side of 1 to Many Relationship with tblSandwhich)
   SandwhichIngredient
Just so we have a name for you're form I'll call it "frmSample"

The Form...
Supose you have 2 controls as follows...

Form contains - 1 Combo box named "cboSandwhich" with the following Query....

SELECT SandwhichID, Sandwhich FROM tblSandwhich ORDER BY Sandwhich;

.ColumnWiths = 0"
.ColumnCount = 2

The AfterUpdate Event of this Combo box should code as follows...

Private Sub cboSandwhich_AfterUpdate()
    Me.lstSandwhichIngredient.Requery
End Sub

Form contains - 1 ListBox named lstIngrediant that has the following Query in it...

SELECT  SandwhichIngredientID, SandwhichIngredient as Ingredient FROM tblSandwhichIngredient WHERE SandwhichID = Forms!frmSample!cboSanwhich.Value ORDER BY SandwhichIngredient ;

.ColumnWiths = 0"
.ColumnCount = 2

***********************************************************

Although there are other ways to do this this one is especially simple as you can see from the single line of code that is needed to make it work.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rick_RickardsCommented:
Sorry for the repost (seemed to have captured more text than anticipated in the cut and paste).  Checked this twice so this should be as it was originally intended...
*********************************
*********************************

The Tables...
Supose you 2 tables as follows...

tblSandwhich
   SandWhichID     AutoNumber (Long Int)  (1 side of a 1 to Many Relationship with tblSandwhich
   SandWhich        String Variable

tblSandwhichIngredient
   SandwhichIngredient  AutoNumber (Long Int)
   SandwhichID               LongInt (Foreign Key - Many side of 1 to Many Relationship with tblSandwhich)
   SandwhichIngredient
Just so we have a name for you're form I'll call it "frmSample"

The Form...
Supose you have 2 controls as follows...

Form contains - 1 Combo box named "cboSandwhich" with the following Query....

SELECT SandwhichID, Sandwhich FROM tblSandwhich ORDER BY Sandwhich;

.ColumnWiths = 0"
.ColumnCount = 2

The AfterUpdate Event of this Combo box should code as follows...

Private Sub cboSandwhich_AfterUpdate()
    Me.lstSandwhichIngredient.Requery
End Sub

Form contains - 1 ListBox named lstIngrediant that has the following Query in it...

SELECT  SandwhichIngredientID, SandwhichIngredient as Ingredient FROM tblSandwhichIngredient WHERE SandwhichID = Forms!frmSample!cboSanwhich.Value ORDER BY SandwhichIngredient ;

.ColumnWiths = 0"
.ColumnCount = 2

***********************************************************

Although there are other ways to do this this one is especially simple as you can see from the single line of code that is needed to make it work.
0
Rey Obrero (Capricorn1)Commented:
noahisaac,

what is the name of your combo box?
place this codes in a click of the commandbutton

private sub command0_click()

    Dim j As variant
   

   With me.List4
         if .itemsselected.count>0 then
            for each j in .itemsselected
                  currentdb.execute _
                  "insert into Sandwiches([name], ingredients) values ('" & me.comboname &"','"& .itemdata(j) &"')"
            next

        end if
    end with
end sub


change me.comboname to the actual name of the combo

your list box list4  Multi select property must be set to simple, so you can select multiple items





0
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.