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,SandwichIngr
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
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,SandwichIngr
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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!cboSanwhic
.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.
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.
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!cboSanwhic
.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.
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.
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!cboSanwhic
.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.
**************************
**************************
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.
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!cboSanwhic
.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.
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
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
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
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.
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!cboSanwhic
.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.