Hi experts out there,
I'm populating records from access db. The condition is that if the first column has been filled with 5 pairs of records (10 rows and a row of gap between each record), then move to next column and so on. How do i accomplish this? Kindly help anyone. I tried as per below code for one column, i couldn't continue thereafter...
Function createRpt(ByVal boxid As Integer, ByRef ws As Worksheet)
Dim iSwitchAt As Integer
iSwitchAt = getSwitchAt(boxid)
Dim sql As String
Dim rs As New ADODB.Recordset
Dim iRow, iColsSerialNo, iRec As Integer
Dim prev, cur, iNo As Integer
Dim iSwitch As Boolean
iRow = 14
iSwitch = False
iColsSerialNo = 2
iNo = 0
iRec = 0
prev = 0
Call OpenConnection
sql = "Select * from tbl where boxid =" & boxid & " order by sequenceno,cartonid"
With rs
.Open sql, conndb, adOpenStatic, adLockReadOnly
Do While Not .EOF
cur = !cartonid
If cur <> prev Then
iRow = iRow + 1
iNo = iNo + 1
End If
If iNo = iSwitchAt And iSwitch = False Then
iRow = 15
iColsSerialNo = 4
iSwitch = True
End If
ws.Cells(iRow, iColsSerialNo).Value = !field01
iRow = iRow + 1
prev = !cartonid
iRec = iRec + 1
.MoveNext
Loop
End With
Call CloseConnection
End Function