Can't trasfer data into Excel using loop

[noorani]

Please check this code i have a problem with initializing the loop in my code. please give me some advise thanks in advance.

My problem is, that i want to export data from Access into my Excel worksheet using organized format, where Product Code and Name Of product will be trasfered vertically and then i want to check the remaining Stock quantity and compare it's code with already transferred Products code in my worksheet. and after that i want to compare the sold products code items for every client and transfer the data in the same way but in different columns. at the moment i'm still far from the client part and it's not included in my code.


Initially i'm succeed exporting the product list but i have problems with comparing and exporting the other data .


Private Sub hpseltoexcel_Click()
Dim xlBudgetSchedule As Excel.Application
Dim rwIndex As Integer
Dim currentRow As Integer
Dim currentColumn As Integer
Dim prodlist As Recordset'product list
Dim realstock As Recordset'stock quantity
Dim dbs As Database
Dim title As String
Set dbs = CurrentDb
title = "BackOffice!"
Set xlBudgetSchedule = New Excel.Application
xlBudgetSchedule.Visible = True
xlBudgetSchedule.Workbooks.add
xlBudgetSchedule.ActiveWindow.DisplayGridlines = True 'show grid lines

'Here i define the heading of initial data.

With xlBudgetSchedule.ActiveSheet
.Cells(2, 1).Value = "Inernal Code"
.Cells(2, 2).Value = "Vender Code"
.Cells(2, 3).Value = "Product Name"
End With
currentRow = 3
currentColumn = 1
'Now i'm retrieving products code and sending 'data to Excel.

Set prodlist = dbs.OpenRecordset("select * from products where (VenderID = 4)ORDER BY Hp_ProductName", dbOpenDynaset)
If prodlist.RecordCount > 0 Then

'Variable i is defined for future comparison 'in the whole row of product code.

Dim i As Integer
i = 0
Do Until prodlist.EOF
With xlBudgetSchedule.ActiveSheet
.Cells(currentRow, currentColumn).Value = prodlist!productid
.Cells(currentRow, currentColumn + 1).Value = prodlist!Hp_ProductName
.Cells(currentRow, currentColumn + 2).Value = prodlist!product_name
.Cells(currentRow, currentColumn).HorizontalAlignment = xlLeft
currentRow = currentRow + 1
currentColumn = 1
i = i + 1
End With
prodlist.MoveNext
Loop
End If
'Here i'm seting up the forth colum of my worksheet.

With xlBudgetSchedule.ActiveSheet
.Cells(2, 4).Value = "Stock Remaining"
End With
currentRow = 3
currentColumn = 4
Set realstock = dbs.OpenRecordset(" SELECT HPresterquery.ProductID, HPresterquery.SumOfquantity FROM HPresterquery", dbOpenDynaset)
If realstock.RecordCount > 0 Then
Do Until realstock.EOF

'HERE I HAVE PROBLEM MY CODE GOES TO 'INDEFINITE LOOP. Basicaly i want to compare 'in realstock RecordSet the product Code with 'already transferred product code if this 'code exist in my Excel file then i want to 'tranfer the Stock quantity in the 4th column 'and the appropriate row of my worksheet
'and move next to check the next code until 'my realstock RecordSet reach to EOF.


For rwIndex = 1 To i
With xlBudgetSchedule.ActiveSheet.Cells(rwIndex, 1)
If .Value = realstock!productid Then .Cells(rwIndex, currentColumn).Value = realstock!SumOfquantity
End With
Next rwIndex
realstock.MoveNext
Loop
End If
realstock.Close
prodlist.Close
dbs.Close
'Caption stuff
xlBudgetSchedule.ActiveSheet.Name = "HPSellout"
xlBudgetSchedule.ActiveWindow.Caption = "HPSellout"
xlBudgetSchedule.Application.Caption = "HPSellout"
xlBudgetSchedule.Application.DisplayFormulaBar = True
xlBudgetSchedule.ActiveWindow.DisplayFormulas = True
xlBudgetSchedule.ActiveWindow.DisplayHeadings = True
xlBudgetSchedule.ActiveWindow.DisplayZeros = True
End Sub

'end of code