What line is the error on?

mmcdonal

Look it up at: http://wrox.books24x7.com

Gives error on the below line:

If lstprdt.ItemsSelected.Count > 0 Then

Also, what tables are each list box based on?

mmcdonal

Look it up at: http://wrox.books24x7.com

That is your fault. That should be:

If lstsrl.ItemsSelected.Count > 0 Then

mmcdonal

Look it up at: http://wrox.books24x7.com

I think i have made one mistake.
In listbox1 the record source is from table(say a sepearte table for City)-Master_city
In Listbox2 the record source is from main table(where the update happens) - Master

May be this leads to an error?Sorry i just now checked it..

You have given following code sayig for text. But lstsrl i have Autonumber field....

'For Text
Set ctrl = Me.lstsrl
For intCurrentRow = 0 To ctrl.ListCount - 1
    If ctrl.Selected(intCurrentRow) Then
        lRecord = ctrl.Column(0, intCurrentRow)
        sSQL = "UPDATE product SET [Print] = Yes WHERE [Serial No] = " & lRecord
        Set rs = New ADODB.Recordset
        rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    End If
Next intCurrentRow

Your post stated Serial No was integer, and City was text, so I did it this way.
Also, if the recordset can't find a field with the name indicated, then it will throw a missing object error. So if it is looking for a field from another table, it will throw this error.
 Please post the tables, their fields, and the fields taken in each list box.


mmcdonal

Look it up at: http://wrox.books24x7.com

Listbox1 = lstsl, City (TEXT) - ROW SOURCE IS FROM MASTER_CITY TABLE
Listbox2 = lstsrl, Serial No (AUTONUMBER) - ROW SOURCE IS FROM PRODUCT TABLE(the main table)
Reportname = Product1
Tablename = Product
The flag to be updated is "PRINT" fieldname in "PRODUCT Table".

PRODUCT is the main table where all fields are.


Have i given the info what you asked for ?

I modded the code to remove products from the lstsrl list, but not cities from the lstsl list:

Dim varItem As Variant
Dim strwhere As String
Dim strreport As String
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim lRecord As Long
Dim sRecord As String
Dim ctrl As Control
Dim intCurrentRow As Integer

strwhere = ""
strreport = "Product1"

'Using Text as Column(0)
If lstsl.ItemsSelected.Count > 0 Then
    strwhere = strwhere & "("
        For Each varItem In lstsl.ItemsSelected
            strwhere = strwhere & "[city] = '" _
            & Me![lstsl].Column(0, varItem) & "' Or "
        Next varItem
        strwhere = Left(strwhere, Len(strwhere) - 4)
          strwhere = strwhere & ")"
End If

If Len(strwhere) > 0 Then strwhere = strwhere & " And "

'Using integer PK as Column(0)
If lstsrl.ItemsSelected.Count > 0 Then
    strwhere = strwhere & "("
        For Each varItem In lstsrl.ItemsSelected
            strwhere = strwhere & "[Serial No] =" & Me![lstsrl].Column(0, varItem) & " Or "
        Next varItem
        strwhere = Left(strwhere, Len(strwhere) - 4)
          strwhere = strwhere & ")"
End If

If Len(strwhere) > 0 Then strwhere = strwhere

'For Text
Set ctrl = Me.lstsrl
For intCurrentRow = 0 To ctrl.ListCount - 1
    If ctrl.Selected(intCurrentRow) Then
        lRecord = ctrl.Column(0, intCurrentRow)
        sSQL = "UPDATE Product SET [Print] = Yes WHERE [Serial No] = " & lRecord
        Set rs = New ADODB.Recordset
        rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    End If
Next intCurrentRow
intCurrentRow = 0

Me.lstsrl.Requery



The resulting WHERE clause produced when I selected the City I created called "Columbia" and the third product in the list (Serial No 2) was:

strwhere = ([city] = 'Columbia') AND ([Serial No] = 2)

This could be expressed without all the () like:

[city] = 'Columbia' AND [Serial No] = 2

Anyway, this worked for me and removed the products from the list. Not the cities. Did you also want cities removed?


mmcdonal

Look it up at: http://wrox.books24x7.com

But i still get error saying

Run-time error '6':

Overflow

in the line:
For intCurrentRow = 0 To ctrl.ListCount - 1

"Anyway, this worked for me and removed the products from the list. Not the cities. Did you also want cities removed?"

I dont understand what you are saying - removed the products from the list.
I Want the Serial No to be cleared from the listbox, not the Cities.