By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Update selected items in a List Box

P: n/a
Hi All...

I have an unbound form.
The form includes:
A ComboBox (from table Callers) "cmbTMCode "
A ListBox MultiSelect=Extended, (from table Leads) "lstSelect"
A CommandButton "btnAssign"

I would like to
1) Select a value from "cmbTMCode"
2) Select multiple records from "lstSelect"
3) Click "btnAssign"
4) Update the TeleMCode field in the "Leads" table with the value
in "cmbTMCode" for the selected records in "lstSelect"
Private Sub btnAssign_Click()
Dim db As Database
Dim rec As Recordset
Dim varSelected As Variant
Dim strSQL As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("Leads")

For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = ""
strSQL = strSQL & " Update leads SET TMCode = " & Me.cmbTMCode
strSQL = strSQL & " WHERE ID = " & Me!lstSelect.ItemData(varSelected)
CurrentDb.Execute strSQL ..Access complains here << Run-Time error
'3061': Too few parameters. Expected 1.>>
Next varSelected

Set db = Nothing ' Clear db...
msgbox ("This data is now in your table..")
End Sub

Any Help?
EiEiO

Oct 15 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
How to debug your code....
I would stick a debug.print in the loop to see if any values are being
produced and to see what sql is created
wack the sql into a query and run it. The error message should be clearer.

For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = ""
strSQL = strSQL & " Update leads SET TMCode = " & Me.cmbTMCode
strSQL = strSQL & " WHERE ID = " & Me!lstSelect.ItemData(varSelected)
'Add this line of code....
debug.print "List Value= " & Me!lstSelect.ItemsSelected & " MySQL= " &
strSQL
CurrentDb.Execute strSQL ..Access complains here << Run-Time error
'3061': Too few parameters. Expected 1.>>
Next varSelected

"EiEiO" <de****@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
Hi All...

I have an unbound form.
The form includes:
A ComboBox (from table Callers) "cmbTMCode "
A ListBox MultiSelect=Extended, (from table Leads) "lstSelect"
A CommandButton "btnAssign"

I would like to
1) Select a value from "cmbTMCode"
2) Select multiple records from "lstSelect"
3) Click "btnAssign"
4) Update the TeleMCode field in the "Leads" table with the value
in "cmbTMCode" for the selected records in "lstSelect"
Private Sub btnAssign_Click()
Dim db As Database
Dim rec As Recordset
Dim varSelected As Variant
Dim strSQL As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("Leads")

For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = ""
strSQL = strSQL & " Update leads SET TMCode = " & Me.cmbTMCode
strSQL = strSQL & " WHERE ID = " & Me!lstSelect.ItemData(varSelected)
CurrentDb.Execute strSQL ..Access complains here << Run-Time error
'3061': Too few parameters. Expected 1.>>
Next varSelected

Set db = Nothing ' Clear db...
msgbox ("This data is now in your table..")
End Sub

Any Help?
EiEiO

Oct 15 '06 #2

P: n/a
I think you must add a ; at the end of the sql statement
strSQL = ""
strSQL = strSQL & " Update leads SET TMCode = " & Me.cmbTMCode
strSQL = strSQL & " WHERE ID = " & Me!lstSelect.ItemData(varSelected) &
";"

Oct 15 '06 #3

P: n/a
On the contrary, the semi colon is optional.

"Svetlana" <Ac**********@yahoo.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
>I think you must add a ; at the end of the sql statement
strSQL = ""
strSQL = strSQL & " Update leads SET TMCode = " & Me.cmbTMCode
strSQL = strSQL & " WHERE ID = " & Me!lstSelect.ItemData(varSelected) &
";"

Oct 15 '06 #4

P: n/a
Ive added the line <<Debug.Print "List Value= " &
Me!lstSelect.ItemsSelected & " MySQL= " & strSQL >>as you suggested .
Access complains <<Tun-time error 450: Wrong number of arguments or
invalid property assignment
EiEiO

Oct 15 '06 #5

P: n/a
The code for the listitem is correct, so it could the the combobox bit
Me.cmbTMCode (is ir a string value ?)
If the combo or list value is a string, you will need to wrap the SQL in
'single' quotes as below
strSQL = "Update leads SET TMCode = '" & Me.cmbTMCode & "' WHERE ID = '" &
Me!lstSelect.ItemData(varSelected) & "'"


"EiEiO" <de****@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
Ive added the line <<Debug.Print "List Value= " &
Me!lstSelect.ItemsSelected & " MySQL= " & strSQL >>as you suggested .
Access complains <<Tun-time error 450: Wrong number of arguments or
invalid property assignment
EiEiO

Oct 15 '06 #6

P: n/a
EiEiO wrote:
Hi All...

I have an unbound form.
The form includes:
A ComboBox (from table Callers) "cmbTMCode "
A ListBox MultiSelect=Extended, (from table Leads) "lstSelect"
A CommandButton "btnAssign"

I would like to
1) Select a value from "cmbTMCode"
2) Select multiple records from "lstSelect"
3) Click "btnAssign"
4) Update the TeleMCode field in the "Leads" table with the value
in "cmbTMCode" for the selected records in "lstSelect"
Private Sub btnAssign_Click()
Dim db As Database
Dim rec As Recordset
Dim varSelected As Variant
Dim strSQL As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("Leads")

For Each varSelected In Me!lstSelect.ItemsSelected
strSQL = ""
strSQL = strSQL & " Update leads SET TMCode = " & Me.cmbTMCode
strSQL = strSQL & " WHERE ID = " & Me!lstSelect.ItemData(varSelected)
CurrentDb.Execute strSQL ..Access complains here << Run-Time error
'3061': Too few parameters. Expected 1.>>
Next varSelected

Set db = Nothing ' Clear db...
msgbox ("This data is now in your table..")
End Sub

Any Help?
EiEiO
I might make reference to the value in the listbox like this
For Each varItem In Me.ListBox.ItemsSelected
lngID = Me.ListBox.Column(0, varItem)
msgbox lngID
Next

And I don't know if your code value is char or numeric. If char,
surround by quotes.

And if worse got to worse, I'd do a
Debug.Print strSQL
and take/cut that value printed in the debug window and attempt to
execute the SQL by opening a new query in design mode, add no tables,
click on View from the menu, select SQL, and paste the SQL string to the
window.
Oct 15 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.