ListBox Coding Problem: Too few parameters. Expected 1.

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

0
robboll
1/20/2008 3:31:23 AM
access.formscoding 7493 articles. 0 followers. Follow

5 Replies
561 Views

Similar Articles

[PageSpeed] 58

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.

"robboll" <robboll@hotmail.com> wrote in message
news:1826a700-4b5a-43ae-af5f-2097e47923f6@s19g2000prg.googlegroups.com...
>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
> 

0
Allen
1/20/2008 3:41:15 AM
"robboll" wrote in message 
news:1826a700-4b5a-43ae-af5f-2097e47923f6@s19g2000prg.googlegroups.com...
>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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/20/2008 3:43:27 AM
"Dirk Goldgar" <dg@NOdataSPAMgnostics.com.invalid> wrote in message 
news:00578E26-9097-4E82-99D1-E1EDBBA02D74@microsoft.com...
>
> 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.

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
1/20/2008 4:36:14 AM
On Jan 19, 9:41=A0pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> 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:
> =A0 =A0http://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. =A0Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "robboll" <robb...@hotmail.com> wrote in message
>
> news:1826a700-4b5a-43ae-af5f-2097e47923f6@s19g2000prg.googlegroups.com...
>
>
>
> >I am trying to append multiple selected values from a listbox to a
> > table. =A0Property 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 =3D "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 =3D "INSERT INTO selections ( selected ) VALUES (TEN);"
>
> > Generates error: Too few parameters. Expected 1.
>
> > Any suggestions appreciated!
>
> > RBollinger- Hide quoted text -
>
> - Show quoted text -

Excellent response, Excellent reference site.  Thank you.
0
robboll
1/20/2008 9:34:04 PM
On Jan 19, 9:43=A0pm, "Dirk Goldgar"
<d...@NOdataSPAMgnostics.com.invalid> wrote:
> "robboll" wrote in message
>
> news:1826a700-4b5a-43ae-af5f-2097e47923f6@s19g2000prg.googlegroups.com...
>
>
>
>
>
> >I am trying to append multiple selected values from a listbox to a
> > table. =A0Property 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 =3D "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 =3D "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. =A0If your=

> values will never contain the single-quote or apostrophe character ('), yo=
u
> can do it like this:
>
> =A0 =A0 strSQL =3D "INSERT INTO selections ( selected ) VALUES ('" & _
> =A0 =A0 =A0 =A0 =A0 =A0 Me!List3.Column(1) & "');"
>
> You may not easily be able to tell, but the above has quotes embedded in t=
he
> 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.
0
robboll
1/20/2008 9:35:51 PM
Reply:

Similar Artilces:

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

How do I get Powerpoint 2003 fade in 1 by 1 in 2007
I frequently used the 'fade in one by one' feature in Powerpoint 2003, which allowed me to fade in individual bullet points by way of a click of a mouse. Not only is this feature now not standard in 2007, but I can't work out how to create it: anything that I find, tends, instead, to fade the bullet points in on some kind of automatic time schedule, rather than - as I want - on a mouse click. I want only to use text, no sounds, no pictures, and nothing fancy. Currently, I am reduced to copying my material into an old Powerpoint 2003 presentation, but this is silly....

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

How can I print on 8 1/2"x17" paper?
I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to change the paper size to accept and print anything bigger than 14." any suggestions would be greatly appreciated First make the size settings in the Printer Setup. You may need to use the Custom selection. -- Don Vancouver, USA "mickilena" <mickilena@discussions.microsoft.com> wrote in message news:1AD99AFE-7FE7-4A07-9D20-1B1AFF9856BD@microsoft.com... >I have cut down an 11x17 paper to 8 1/2" x 17". I cannot figure out how to > change the paper size to accept a...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...

=?ISO-8859-1?Q?DivX=AE_5.component_causes_crashes_of_Excel_an?= =?ISO-8859-1?Q?d_PowerPoint_during_launch_of_Project_Gallery?=
If you are experiencing crashing on startup of Excel X and PowerPoint X just prior to the display of the Project Gallery, the fault may lay with a QuickTime library component installed by DivX. When removed, the Project Gallery appears just fine. The Project Gallery also apears to launch properly in Word X. Either remove the file or set your Excel and PowerPoint preferences to not display the Project Gallery at startup. The file can be found at Macintosh HD --> LIbrary --> QuickTime --> DivX� 5.component. This was also a problem with an earlier version of a Toast beta component for Qu...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Report: Cell #1, Cell #2, Cell #3, Cell #4
I am stuck again and would love som help :( I would like to repeat all words found inside ~25 cells, separated only by ", ", ignoring empty cells. Data: A1: [Apple ] A2: [Orange] A3: [Banana] A4: [Tomato] A5: [Syrup ] A6: [ ] A7: [ ] A8: [ ] The result should be something like: [Apple, Orange, Banana, Tomato, Syrup] -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26945 View this thread: http://www.excelforum.com/showthread.php?thre...

Printing problem in booklet format
When I try to print my booklet document, on the screen it looks perfect, but when i go into print preview it only has half of each page on the paper. When i print, it comes out exactly like that. I have Publisher 2003 and run it on XP home. My printer is an Epson stylus Photo RX650. I contacted Epson and they say its a Publisher problem. Any suggestions. Driver? http://tech.epson.com.au/downloads/product.asp?sCategory=Multi_Functional&id=stylusphotorx650&FileType=1&EmailAdd=&MetricIDReturned=624915&platform=winxp Are you selecting booklet in page setup? Do you ...

How to show query parameters on an Excel page header or worksheet?
Is it possible to display query parameters on a page header or on a worksheet? I have a worksheet that uses a query to retrive data from an ODBC database. The Query prompts for the Start Date and End Date. I would like to be able to print the worksheet and display the Start Date and End Date the user typed in. Thank you for your assistance. ...

Outlook 2003
I get an error every time I open Outlook 2003 that says: "The add-in "C:\WINDOWS\System32\AdMailOK.1" could not be installed or loaded. This problem may be resolved by using Detect and Repair on the Help menu." I've tried "Detect and Repair" and it didn't kill the error from appearing every time I load Outlook. Any ideas? I searched Google and came up empty on this. Thanks! Have you tried reinstalling the program? ----- Thundersmash wrote: ---- I get an error every time I open Outlook 2003 that says "The add-in ...

Money deluxe 2007 password problem
When I update my bank account transactions I get a message saying that my account was not updated because my password is incorrect. I then go into account info and have to update my password. I save the update, but then it happens again sporadically when I open Money at later times. Is there a way to fix this glitch? ...

problem opening up program/file
Hello, All. I am having some trouble opening up Word. It was working okay until I loaded some fonts to FontBook. Okay, now I removed all my fonts but I still get this error message about corrupt fonts I have on my computer. When I open Word, I see the blue starting-up window: Word:Mac 2004. Right underneath the product ID #, I see that it is initiating... when it gets to the point "Optimizing font menu performance" I start getting this error windows popping up one after another. (E.g. The font M TimesSmallText is corrupt and should be removed.) I keep on clicking okay for a whole lot...

printing problems with memorized reports
We have recently been running into printing problems with our memorized reports. We will print preview a report and it will look fine on screen, then we will send to the printer, and the report print extremely small in the corner of the page. However if we go back in and re-print the report then it will print normal size on the page. We are not making any settings changes at all, and when it does this appears to just be at random. We are printing to an HP laserjet 4000 series printer that is on our network. We've used RMS with the same configuration for approx. 5 years now and ...

Reports and sub-reports problems
Dear Sirs,I have a report with 4 sub-reports.How can it be that 2 sub-reports are showing the data triple.Can someone help?ThanksKlaus I just realized. It's not due to being a subreport - it's already showing on the normal report triple data. It was not before and I did not change anything. I hope that someone can help me.Thanks Klaus"Amateur" wrote:> Dear Sirs,> I have a report with 4 sub-reports.> How can it be that 2 sub-reports are showing the data triple.> Can someone help?> Thanks> Klaus...

1 QUESTION about OUTLOOOK EXPRESS!
You know the little flags that are on the Wright side off the open INBOX I thinks is a paper clip, a down arrow and then somethi9ng I never saw before today a pair of sunglasses! What the hell! Maybe it was there but today I got a letter with the sunglasses next to it does anyone have a clue what this means I looked all over Microsoft's site all the help files in XP Some one please tell me what this means its bugging the shit out of ME! Thanks, Tom ...