Recordset Question

[missymarie1014]

I have worked around this problem by including criteria in each statement (QuantityOnHand=0), but I was trying to implement an if..then approach and I can't seem to get it to work. I want to understand what is going on here and what I am doing wrong. This is all designed to eliminate divide by zero errors when the quantity on hand is zero. I keep getting an error that the field cannot be found even though it is in the form on which the recordsetclone is based. Below is a section from my code and the problem is in the lower part where the looping action is being done to the recordset. The first bold area is the source of the problem. If I could get this to work I could remove all the quantityonhand=0 or quantityonhand<>0 clauses in each individual statement. Thanks for any help!

    Dim stDocName As String
    Dim stLinkCriteria As String

    Dim CurDB As Database
    Dim rs As recordset
    Dim rst As recordset
    Dim LabResp As Integer
    Dim Response As Integer
    Dim Resp As Integer
    Dim RawQty As Double
    Dim QtyUsed As Double
    Dim OldQty As Double

    Set CurDB = CurrentDb

    Response = MsgBox("Yes To Process This Batch, No To Continue Editing Batch", vbYesNo + vbQuestion _
    + vbDefaultButton2, "Are You Sure?")

If Response = vbNo Then
    DoCmd.Close acForm, "ProcessBatchFromEditForm", acSaveNo
    Exit Sub
End If

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE ProductionBatchMasterTable SET IsProcessed = -1" & _
" WHERE (([ProductionBatchMasterTable]![BatchID])=[Forms]![ProcessBatchFromEditForm]![BatchID])"

DoCmd.RunSQL "UPDATE ProductionBatchMasterTable SET ProcessedDate = Now()" & _
" WHERE (([ProductionBatchMasterTable]![BatchID])=[Forms]![ProcessBatchFromEditForm]![BatchID])"

DoCmd.RunSQL "UPDATE ItemTable SET LastQuantityOnHand = QuantityOnHand" & _
" WHERE (([ItemTable]![Item])=[Forms]![ProcessBatchFromEditForm]![ItemProduced])"

DoCmd.RunSQL "UPDATE ItemTable SET QuantityOnHand = QuantityOnHand + [Forms]![ProcessBatchFromEditForm]![QuantityProduced]" & _
" WHERE (([ItemTable]![Item])=[Forms]![ProcessBatchFromEditForm]![ItemProduced])"

DoCmd.RunSQL "UPDATE ItemTable SET LastTotalCostOnHand = TotalCostOnHand" & _
" WHERE (([ItemTable]![Item])=[Forms]![ProcessBatchFromEditForm]![ItemProduced])"

DoCmd.RunSQL "UPDATE ItemTable SET TotalCostOnHand = TotalCostOnHand + [Forms]![ProcessBatchFromEditForm]![TotalBatchCost]" & _
" WHERE (([ItemTable]![Item])=[Forms]![ProcessBatchFromEditForm]![ItemProduced])"

DoCmd.RunSQL "UPDATE ItemTable SET CurrentCost=0" & _
" WHERE ((([ItemTable]![Item])=[Forms]![ProcessBatchFromEditForm]![ItemProduced]) and (QuantityOnHand=0))"

DoCmd.RunSQL "UPDATE ItemTable SET CurrentCost = TotalCostOnHand/QuantityOnHand" & _
" WHERE ((([ItemTable]![Item])=[Forms]![ProcessBatchFromEditForm]![ItemProduced]) and (QuantityOnHand<>0))"

DoCmd.RunSQL "UPDATE ProductionBatchMasterTable SET ProcessedBatchCost = [Forms]![ProcessBatchFromEditForm]![TotalBatchCost]" & _
" WHERE (([ProductionBatchMasterTable]![BatchID])=[Forms]![ProcessBatchFromEditForm]![BatchID])"

DoCmd.RunSQL "UPDATE ProductionBatchMasterTable SET ProcessedBatchUnitCost = [Forms]![ProcessBatchFromEditForm]![BatchItemCost]" & _
" WHERE (([ProductionBatchMasterTable]![BatchID])=[Forms]![ProcessBatchFromEditForm]![BatchID])"

DoCmd.RunSQL "UPDATE ProductionBatchDetailTable SET IsProcessed = -1" & _
" WHERE (([ProductionBatchDetailTable]![BatchID])=[Forms]![ProcessBatchFromEditForm]![BatchID])"

DoCmd.RunSQL "UPDATE ProductionBatchDetailTable SET ProcessedDate = Now()" & _
" WHERE (([ProductionBatchDetailTable]![BatchID])=[Forms]![ProcessBatchFromEditForm]![BatchID])"

DoCmd.OpenForm "ProcessBatchSubForm1", acNormal, , "BatchID=" & BatchID
Forms.ProcessBatchSubForm1.Visible = False

Set rs = Forms.ProcessBatchSubForm1.RecordsetClone
Set rst = CurDB.OpenRecordset("SELECT * from UnprocessedBatchQuery")

Do Until rs.EOF

[b]OldQty = Forms!ProcessBatchSubForm1!RecordsetClone.QuantityOnHand
QtyUsed = Forms!ProcessBatchSubForm1!RecordsetClone.QuantityUsed
RawQty = OldQty - QtyUsed[/b]

    DoCmd.RunSQL "UPDATE ItemTable Set LastQuantityOnHand=QuantityOnHand" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set LastTotalCostOnHand=TotalCostOnHand" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set LastItemCostOnHand=ItemCostOnHand" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set LastFreightCostOnHand=FreightCostOnHand" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (IsLabor = No))"
    
    DoCmd.RunSQL "UPDATE ItemTable Set QuantityOnHand=" & _
    "QuantityOnHand-Forms!ProcessBatchSubForm1.RecordsetClone.QuantityUsed" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (IsLabor = No))"

[b]If RawQty = 0 Then[/b]

    DoCmd.RunSQL "UPDATE ItemTable Set CurrentCost=0" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand=0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set CurrentItemCost=0" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand=0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set CurrentFreightCost=0" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand=0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set ItemCostOnHand=0" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand=0) and (IsLabor = No))"
    
    DoCmd.RunSQL "UPDATE ItemTable Set FreightCostOnHand=0" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand=0) and (IsLabor = No))"
    
    DoCmd.RunSQL "UPDATE ItemTable Set TotalCostOnHand=0" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand=0) and (IsLabor = No))"

[b]Else[/b]

    DoCmd.RunSQL "UPDATE ItemTable Set TotalCostOnHand=" & _
    "TotalCostOnHand-Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsedExtendedTotalCost" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand<>0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set ItemCostOnHand=" & _
    "ItemCostOnHand-Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsedExtendedItemCost" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand<>0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set FreightCostOnHand=" & _
    "FreightCostOnHand-Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsedExtendedFreightCost" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand<>0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set CurrentCost=TotalCostOnHand/QuantityOnHand" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand<>0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set CurrentItemCost=ItemCostOnHand/QuantityOnHand" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand<>0) and (IsLabor = No))"

    DoCmd.RunSQL "UPDATE ItemTable Set CurrentFreightCost=FreightCostOnHand/QuantityOnHand" & _
    " WHERE ((Item = Forms!ProcessBatchSubForm1.RecordsetClone.ItemUsed) and (QuantityOnHand<>0) and (IsLabor = No))"
    
[b]End If[/b]
    
    rs.MoveNext

Loop

rs.Close
Set rs = Nothing

 

[PHV]

Why not simply this ?
OldQty = Forms!ProcessBatchSubForm1!QuantityOnHand

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missymarie1014]

I thought about doing that since the quantity on hand value has been updated in a statement above that and I will try that and see what happens. However I would still like to know why these lines produce the error cannot find field when all the runsql commands use the same syntax and they run properly.

OldQty = Forms!ProcessBatchSubForm1!RecordsetClone.QuantityOnHand
QtyUsed = Forms!ProcessBatchSubForm1!RecordsetClone.QuantityUsed

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886