SQL Update from Listbox loop

[Xproterg]

The code below is used in MS Access.

The listbox mentioned below is drawn from a table.

The command button is supposed to update another column in that table based on the items listed in the listbox.

To illustrate this, it's used for inspections. Other functions in the program generate a random list of organizations from the GRANDE TABLE to inspect and make a new SMALL table based on the random list (named table_20110120 or table_whateverthedateis).

There is a search box that you can type in the the date, such as 20110120, and it will populate the listbox with table_whateveryoursearchedfor.

The third part, which is not working, is once the company is inspected, users are supposed to be able to use the search to bring up the companies from that date, and have the code below update the date of inspection on the GRANDE TABLE.

Private Sub Command31_Click()
Dim lstitem As String
    For i = 1 To list.ListCount
    lstitem = list.Column(i)
    date3 = Format(Date, "yyyymmdd")
    strSQL3 = "UPDATE ContractorData SET LastInspection = " & date3 & " WHERE Name = " & lstitem & ";"
    DoCmd.RunSQL strSQL3
    Next i
 
End Sub

But the code generates an error that requires debugging, saying that lstitem is null. I'm not exactly sure why this is. Any input would greatly be appreciated.

I've attached a copy of the application so you can see what I mean.

 

[CBrighton]

Try replacing "lstitem = list.Column(i)" with "lstitem = list.ItemData(i)"

You are cycling through columns rather than records.

I assume the data you want is in the bound column of the listbox?

:edit:

I think listbox .ItemData is 0 based rather than 1 based so you may need to change "For i = 1 To list.ListCount" to "For i = 0 To list.ListCount - 1" too. Try it and see which gives all of the records.

 

[Xproterg]

So the program is pulling the data now; however, when it comes to words with spaces in them, it gives an error:

"Syntax error (missing operator) in query experssion 'Name = Name with a space'.

Additionally, the application asks for "Enter Parameter Value" for each of the items in the list when you press the button. Am I missing something in my query?

 

[CBrighton]

Try changing

strSQL3 = "UPDATE ContractorData SET LastInspection = " & date3 & " WHERE Name = " & lstitem & ";"

to

strSQL3 = "UPDATE ContractorData SET LastInspection = " & date3 & " WHERE Name = '" & lstitem & "';"

' should surround the data pulled from the listbox, assuming it's a text field in the table.

 

[Xproterg]

Thank you very much... that is exactly what I was looking for. That totally makes sense also.