ListBox Coding Problem: Too few parameters. Expected 1.

[robboll]

I am trying to append multiple selected values from a listbox to a
table. Property set to Multi Select (extended).

Access 2007

Form: Main
ListBox Control: List3
Test values: One, Two, Three . . .

Destination Table: Selections
Destination Field: Selected

Control: Command Button.

Syntax:

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Command5_Click()

On Error GoTo Err_Command5_Click
Dim strSQL As String
strSQL = "INSERT INTO selections ( selected ) VALUES (" & Me!
List3.Column(1) & ");"
CurrentDb.Execute strSQL, dbFailOnError
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Running debug the value of strSQL:
strSQL = "INSERT INTO selections ( selected ) VALUES (TEN);"

Generates error: Too few parameters. Expected 1.

Any suggestions appreciated!

RBollinger

 

[Allen Browne]

You need to loop through the ItemsSelected collection of your mulit-select
list box, concatenating the values into the SQL string.

Here's an example of building up the WHERE clause:
http://allenbrowne.com/ser-50.html

That example ends up using the string as the WhereCondition for OpenReport,
but it's exactly the same thing building the WHERE clause for your query
statement.

 

[Dirk Goldgar]

in message

I am trying to append multiple selected values from a listbox to a
table. Property set to Multi Select (extended).

Access 2007

Form: Main
ListBox Control: List3
Test values: One, Two, Three . . .

Destination Table: Selections
Destination Field: Selected

Control: Command Button.

Syntax:

' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Command5_Click()

On Error GoTo Err_Command5_Click
Dim strSQL As String
strSQL = "INSERT INTO selections ( selected ) VALUES (" & Me!
List3.Column(1) & ");"
CurrentDb.Execute strSQL, dbFailOnError
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Running debug the value of strSQL:
strSQL = "INSERT INTO selections ( selected ) VALUES (TEN);"

Generates error: Too few parameters. Expected 1.

Any suggestions appreciated!

RBollinger

You're inserting a text value, so you have to put it in quotes. If your
values will never contain the single-quote or apostrophe character ('), you
can do it like this:

strSQL = "INSERT INTO selections ( selected ) VALUES ('" & _
Me!List3.Column(1) & "');"

You may not easily be able to tell, but the above has quotes embedded in the
string literals to surround the value from the list box.

 

[Dirk Goldgar]

You're inserting a text value, so you have to put it in quotes. If your
values will never contain the single-quote or apostrophe character ('),
you can do it like this:

strSQL = "INSERT INTO selections ( selected ) VALUES ('" & _
Me!List3.Column(1) & "');"

You may not easily be able to tell, but the above has quotes embedded in
the string literals to surround the value from the list box.

Sorry, I didn't notice that you mentioned inserting multiple values from a
multiselect list box. In that case, though my point about the quotes is
important (and the cause of the specific message you're getting), you need
to do more than this. See Allen Browne's post.

 

[robboll]

You need to loop through the ItemsSelected collection of your mulit-select
list box, concatenating the values into the SQL string.

Here's an example of building up the WHERE clause:
   http://allenbrowne.com/ser-50.html

That example ends up using the string as the WhereCondition for OpenReport,
but it's exactly the same thing building the WHERE clause for your query
statement.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
















- Show quoted text -

Excellent response, Excellent reference site. Thank you.

 

[robboll]

in message

















You're inserting a text value, so you have to put it in quotes.  If your
values will never contain the single-quote or apostrophe character ('), you
can do it like this:

    strSQL = "INSERT INTO selections ( selected ) VALUES ('" & _
            Me!List3.Column(1) & "');"

You may not easily be able to tell, but the above has quotes embedded in the
string literals to surround the value from the list box.

--
Dirk Goldgar, MS Access MVPwww.datagnostics.com

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -

Thanks! That did it.