M
Mark
Hello all,
I seem to have a problem with my do loop. I have 250 records in the table,
but my do loop runs 251 times. It somehow picks the first record up again
and processes it 1 more time. Do you see anything wrong with my logic here
(below)?
I know it is picking the first record twice because it is doing some
calculation and when I step through it is correct the first time, but the
last loop it changes again.
Hopefully you can see an issue here in my snippet, but if not the full code
is below line.
vSql = "select * from tbImport"
Set db = CurrentDb()
Set rs = db.OpenRecordset(vSql)
Do While Not rs.EOF
(Lot’s of code here)
rs.Update
rs.MoveNext
Loop
----------------------------------------------------------------------------------------
Do While Not rs.EOF
' Get Part Parameters for update
vSql1 = "SELECT sku, sku_desc, wgt1, hgt1, wid1, dpth1 FROM viaware_pm_f
" & _
" WHERE sku_type = ""NORM"" AND sku = """ & _
Right$("00000000" & rs!PartNum, 8) & """"
Set db1 = CurrentDb()
Set rs1 = db.OpenRecordset(vSql1)
' Get barcode number for update
vSql2 = "SELECT PART_DESC FROM VIAWARE_USER_XPM_F " & _
" where num = 13 and sku = """ & _
Right$("00000000" & rs!PartNum, 8) & """"
Set db2 = CurrentDb()
Set rs2 = db.OpenRecordset(vSql2)
rs.Edit
' Convert Partnumer to correct format
vRsHld = Right$("00000000" & rs!PartNum, 8)
' Get Inventory on hand Qty
vQtyOH = "0"
vQtyAlloc = "0"
vSql15 = "SELECT sum(QTY) as SQty, sum(ALLOC_QTY) as SAQty FROM
VIAWARE_IV_F " & _
" where sku = """ & _
Right$("00000000" & rs!PartNum, 8) & """"
Set db3 = CurrentDb()
Set rs3 = db.OpenRecordset(vSql15)
If rs3!sqty <> "0" Then
vQtyOH = rs3!sqty
End If
If rs3![saqty] <> "0" Then
vQtyAlloc = rs3!saqty
End If
rs3.Close
db3.Close
' Get Wave number
vSql14 = "select FCwave from tbWave where id = 1"
Set Cntdb = CurrentDb()
Set Cntrs = db.OpenRecordset(vSql14)
' Update Qty if Rapid Replen
If txImpType = "Rapid Replen" Then
If rs!unitqty <= txRange Then
vRRQty = rs!unitqty * txMinTimes + txMinPlus
ElseIf rs!unitqty > txRange Then
vRRQty = rs!unitqty * txMaxTimes + txMaxPlus
End If
End If
'----------------------------- Updated Fields.
If Not rs1.EOF Then
rs.ImportUser = txLogName
rs!fcwave = Cntrs!fcwave
rs!PartNum = vRsHld
rs!EHeight = rs1!hgt1
rs!EWidth = rs1!wid1
rs!ELength = rs1!dpth1
rs!EWeight = rs1!wgt1
rs!Title = rs1!sku_desc
rs!QtyOH = vQtyOH
rs!QtyAlloc = vQtyAlloc
End If
If Not rs2.EOF Then
rs!Barcode = RTrim(rs2!Part_Desc)
End If
If txImpType = "Rapid Replen" Then
rs!unitqty = vRRQty
End If
'----------------------------- /End
rs.Update
rs.MoveNext
Loop
I seem to have a problem with my do loop. I have 250 records in the table,
but my do loop runs 251 times. It somehow picks the first record up again
and processes it 1 more time. Do you see anything wrong with my logic here
(below)?
I know it is picking the first record twice because it is doing some
calculation and when I step through it is correct the first time, but the
last loop it changes again.
Hopefully you can see an issue here in my snippet, but if not the full code
is below line.
vSql = "select * from tbImport"
Set db = CurrentDb()
Set rs = db.OpenRecordset(vSql)
Do While Not rs.EOF
(Lot’s of code here)
rs.Update
rs.MoveNext
Loop
----------------------------------------------------------------------------------------
Do While Not rs.EOF
' Get Part Parameters for update
vSql1 = "SELECT sku, sku_desc, wgt1, hgt1, wid1, dpth1 FROM viaware_pm_f
" & _
" WHERE sku_type = ""NORM"" AND sku = """ & _
Right$("00000000" & rs!PartNum, 8) & """"
Set db1 = CurrentDb()
Set rs1 = db.OpenRecordset(vSql1)
' Get barcode number for update
vSql2 = "SELECT PART_DESC FROM VIAWARE_USER_XPM_F " & _
" where num = 13 and sku = """ & _
Right$("00000000" & rs!PartNum, 8) & """"
Set db2 = CurrentDb()
Set rs2 = db.OpenRecordset(vSql2)
rs.Edit
' Convert Partnumer to correct format
vRsHld = Right$("00000000" & rs!PartNum, 8)
' Get Inventory on hand Qty
vQtyOH = "0"
vQtyAlloc = "0"
vSql15 = "SELECT sum(QTY) as SQty, sum(ALLOC_QTY) as SAQty FROM
VIAWARE_IV_F " & _
" where sku = """ & _
Right$("00000000" & rs!PartNum, 8) & """"
Set db3 = CurrentDb()
Set rs3 = db.OpenRecordset(vSql15)
If rs3!sqty <> "0" Then
vQtyOH = rs3!sqty
End If
If rs3![saqty] <> "0" Then
vQtyAlloc = rs3!saqty
End If
rs3.Close
db3.Close
' Get Wave number
vSql14 = "select FCwave from tbWave where id = 1"
Set Cntdb = CurrentDb()
Set Cntrs = db.OpenRecordset(vSql14)
' Update Qty if Rapid Replen
If txImpType = "Rapid Replen" Then
If rs!unitqty <= txRange Then
vRRQty = rs!unitqty * txMinTimes + txMinPlus
ElseIf rs!unitqty > txRange Then
vRRQty = rs!unitqty * txMaxTimes + txMaxPlus
End If
End If
'----------------------------- Updated Fields.
If Not rs1.EOF Then
rs.ImportUser = txLogName
rs!fcwave = Cntrs!fcwave
rs!PartNum = vRsHld
rs!EHeight = rs1!hgt1
rs!EWidth = rs1!wid1
rs!ELength = rs1!dpth1
rs!EWeight = rs1!wgt1
rs!Title = rs1!sku_desc
rs!QtyOH = vQtyOH
rs!QtyAlloc = vQtyAlloc
End If
If Not rs2.EOF Then
rs!Barcode = RTrim(rs2!Part_Desc)
End If
If txImpType = "Rapid Replen" Then
rs!unitqty = vRRQty
End If
'----------------------------- /End
rs.Update
rs.MoveNext
Loop