Update selected items in a List Box

[EiEiO]

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

[tombsy]

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

[Svetlana]

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) &
";"

[tombsy]

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) &
";"

[EiEiO]

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

[tombsy]

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

[salad]

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.