Hi All

There is a userform where you have to enter the batchno in the textbox . and then VBA code will check the Access database for that batchno.
if found then check tht batchno in Sheet1 , column A . if its present then display cases,pages and policy no to the corresponding batch number found.

I have written the code but it doesn't work. I hope anyoone make the changes accordingly.

strsql = "select BatchNo,cases,Pages,PolicyNo from Jabberwocky where BatchNo=" & TextBox1.Value & ""
rs.Open strsql, cn
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
If rs.BOF = True Or rs.EOF = True Then
MsgBox "Not found"
Exit Sub
End If
For i = 2 To lastrow
If ws.Cells(i, "A").Value = rs.Fields(0).Value Then
If ws.Cells(i, "C").Value = "" Then
ws.Cells(i, "C").Value = rs.Fields(1).Value
ws.Cells(i, "D").Value = rs.Fields(2).Value
ws.Cells(i, "E").Value = rs.Fields(3).Value
End If
Exit For
End If
Next i
Thanks