Do loop

[Guest]

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

 

[John Nurick]

I'm suspicious of all those calls to CurrentDB, and especially of this:

Set db3 = CurrentDB()
...
db3.Close

Just create one database object, and use
Set rs = db.OpenRecordset(blah)
...
Set rs2 = db.OpenRecordset(flah)
...
Set rs3 = db.OpenRecordset(klah)

It's never a bad idea to explicitly close recordsets when you finish
with them, and if you wear braces as well as a belt set the object
variables to Nothing as well:
...
rs3.Close
Set rs3 = Nothing

But if you've set an object variable to CurrentDB(), don't close it,
just release it
Set db = Nothing.

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