Update multiple records selected from list box

[tfulmer]

Not sure if this should be in the programming forum or this forum. I have seen similar postings in both forums.

I am a novice trying to design a form that will update a field in multiple selected records from a list box. The update data comes from a text box on the same form.

I am using code behind a command button to try to accomplish this. The idea is that once the user 1) inputs the desired text data into the text box; 2) selects multiple records from the list box; 3) clicks the command button - the updates are completed.

My problem is successfully updating only the selected records, versus all of the records that are in the table.



I would appreciate any assistance.

Tom in Austin, TX

Here is my code:
Option Compare Database
Option Explicit
Private Sub Command6_Click()
Dim frm As Form
Dim ctl As Control
Dim varitem As Variant
Dim strsql As String

Set frm = Forms!form2
Set ctl = frm!List4

If Forms!form2!List4.ItemsSelected.Count <> 0 Then
For Each varitem In ctl.ItemsSelected
strsql = "UPDATE checks2 SET " & _
"checks2.adv3_cr = forms!form2!cr_number.value " & _
"WHERE 'checks2.check_number' = " & _
"'forms!form2!List4.itemdata(varitem)'; "

DoCmd.RunSQL strsql
Next varitem

Else
msgbox "Nothing was selected from the list", vbInformation
Exit Sub 'nothing was selected
End If

End Sub

[pbaldy]

Try

strsql = "UPDATE checks2 SET " & _
"checks2.adv3_cr = '" & forms!form2!cr_number.value & "' " & _
"WHERE checks2.check_number = '" & _
forms!form2!List4.itemdata(varitem) & "'; "

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

[June7]

Need to concatenate variables in the construction of SQL string. Treat reference to controls on form as variables.

strsql = "UPDATE checks2 SET adv3_cr = '" & forms!form2!cr_number & "'" & _
" WHERE check_number = '" & forms!form2!List4.itemdata(varitem) & "'"

If adv3_cr or check_number are number fields, remove apostrophe delimiters - date data would use # as delimiter.

[tfulmer]

Thank you for the suggestions and help - they work great. I can't say enough for experienced users like yourselves who are willing to assist novices on this forum.

[pbaldy]

Happy to help!