How many records are there in that list box? Try changing:

Dim intCurrentRow As Integer

To:

Dim intCurrentRow As Long

mmcdonal

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

Thanks a lot
After changing to Long am able to filter and update table. But the Serial no is not getting removed from the listbox.If it cannot be removed anyother option of preventing the same serial no getting printed second time?

Basically i want to avoid duplicate print of that serial No.

In order to remove the Serial No from the list box, you need to go to Design view and open the properties dialog box for the list. Then select the data tab. Then select the query designer. Then make sure that the Print Field is in the query, and that you put No in the criteria so that the list box only displays records where Print = No. Close the designer and click Yes to save the changes.
Then go to the Format Tab and make the the column count reflects the new column, and set the column widths so that the last column (Print) has a width of 0".

Make sure to do an Update Query when the form opens to set all the print column values to No.


mmcdonal

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

Thanks a lot for your help,it is working fine.

Will come back if i have any doubts...
Again thanks a lot for your time.

Whew. Well that is good. I had it working on my version of the data structure. Just little typos and overlooked variables. That must be a huge list in that list box to overflow a PK variable.

See, there is a lesson I forgot since I work with smaller datasets. When taking an Autonumber field into a variable, always use Long since Integer is likely to overflow - especially with SQL Server. Yikes.

mmcdonal

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

Iam Now removing the serial no from listbox after printing.But i have same serial no for more than one city, so when i select serial no from [lstsrl] with one city from [lstsl], the other city with same serial no is also getting updated in table.Can it be resolved by adding city also so that if the serial no and city is selected that particular record should be updated.

Can you help on this....

Iam Now removing the serial no from listbox after printing.But i have same serial no for more than one city, so when i select serial no from [lstsrl] with one city from [lstsl], the other city with same serial no is also getting updated in table.Can it be resolved by adding city also so that if the serial no and city is selected that particular record should be updated.

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?
---------
I have same serial no for more than one city, so even i choose one city with serial no the other city is also getting updated, can you tell me how to make table update only when the serial no and city is selected.Is it possible.Can you help on this please...

It seems you only want one list box then, with both the city and serial no.

Create a local table with the proper columns (cityID, City, Serial No, Print, I think) and then when the form opens, delete all the data in the table with a delete query, then populate the table with an append query that pulls in all the combinations of cities and serial numbers (simple Select Distinct etc).

Then take your parameters from that one list box.

The alternative is to create a cascading list box, and then have the user select a city first, and then based on the city they select, show them the remaining serial numbers that are associated with that city.

mmcdonal

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

Can you please explain how to code for cascading listbox...