ListBox Coding Problem: Too few parameters. Expected 1.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

robboll - 20 Jan 2008 03:31 GMT

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

Reply to this Message

Allen Browne - 20 Jan 2008 03:41 GMT

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.

Signature

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.

>I am trying to append multiple selected values from a listbox to a
> table. Property set to Multi Select (extended).
[quoted text clipped - 37 lines]
>
> RBollinger

Reply to this Message

robboll - 20 Jan 2008 21:34 GMT

> You need to loop through the ItemsSelected collection of your mulit-select
> list box, concatenating the values into the SQL string.
[quoted text clipped - 54 lines]
>
> - Show quoted text -

Excellent response, Excellent reference site. Thank you.

Reply to this Message

Dirk Goldgar - 20 Jan 2008 03:43 GMT

>I am trying to append multiple selected values from a listbox to a
> table. Property set to Multi Select (extended).
[quoted text clipped - 37 lines]
>
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Dirk Goldgar - 20 Jan 2008 04:36 GMT

> 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 ('),
[quoted text clipped - 5 lines]
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

robboll - 20 Jan 2008 21:35 GMT

On Jan 19, 9:43 pm, "Dirk Goldgar"
<d...@NOdataSPAMgnostics.com.invalid> wrote:

> >I am trying to append multiple selected values from a listbox to a
> > table. Property set to Multi Select (extended).
[quoted text clipped - 54 lines]
>
> - Show quoted text -

Thanks! That did it.

Reply to this Message