How to run an update query on a multiselect list box

[Adam Tippelt]

What I'm trying to do is to update a numerical value on all the records selected in the multiselect listbox.

I'm not entirely sure if what I'm trying to do is the correct way of doing it, but the code I'm trying is:

Private Sub Save_Click()
Dim varItem As Variant
 
For Each varItem In Me.ProgramList.ItemsSelected
strSQL = "UPDATE tblPrograms SET tblPrograms.Client_ID =" & Me.ClientList.Column(1) & _
         " WHERE tblPrograms.Program =" & varItem
DoCmd.RunSQL strSQL
Next varItem

The code keeps triggering a Data mismatch error, even though both data types used in this query are numerical - changing the code as though they were text fields still triggers a data mismatch error.

Any ideas how to fix this?

Thanks.

[Mariostg]

Probably because varItem is declared as a Variant. You probably need to cast it as in integer: cint(varItem)

[Adam Tippelt]

Gives an error if I change it to integer, saying that it must be either Variant or Object.

Perhaps there's another way to code it? All I'm trying to do is update a column for all the selected options in the list box.

[Mariostg]

Maybe I should have been clearer.
The casting should be in

" WHERE tblPrograms.Program =" & cint(varItem)
 

. To iterate over the for each, varItem must be a variant, but your query is expecting an integer I presume.

[Adam Tippelt]

Oh right, that makes more sense.

Turned out my problem was that I was referencing the wrong column in the table - Program instead of Program_ID, so was looking at a text field.

Thanks for your help - I get the feeling you've pre-emptively fixed another problem I would have run into anyway.