help with my recordsetcode

[aron.ridgway]

Im trying to get a recordset loop code working, i have my bellow code which keeps inputing a zero. My second piece of code is identical code but displaying the qty in a message box. The number it is displaying is the number i want to input into my field. I just cant seem to get it to put the number in the field !Qty?

Dim Val As Integer
Dim rs As DAO.Recordset
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
With rs
Do While Not rs.EOF

rs.Edit
Val = Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK]))
rs!Qty = Val
rs.Update

rs.MoveNext
Loop
End With
Set rs = Nothing

Dim val As Integer

Dim rs As DAO.Recordset
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
With rs
Do While Not rs.EOF
val = Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK]))

MsgBox val

rs.MoveNext
Loop
End With
Set rs = Nothing

 

[JHB]

Try to use a different name for the variable - Val is a function in MS-Access.

Why not putting the return value directly into the field?

rs!Qty = Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK]))

 

[aron.ridgway]

thank you for your suggestion, i have tried putting it directly into the field but its still not working? do you think i may need to do something in SQL?

 

[aron.ridgway]

This has been solved i made a quick change to the Qty format and it seems to have done the trick

Dim val As Integer


Dim rs As DAO.Recordset
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
With rs
Do While Not rs.EOF


rs.Edit
val = Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK]))
rs("Qty") = Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK]))
rs.Update

rs.MoveNext
Loop
End With
Set rs = Nothing

 

[namliam]

Nooooooooooooooooo, Dlookup mania.

DLookups are BAD, the devil I tell you. Avoid them like the plague and scourge that they are!
Why would you store the Dlookup into VAL and then re-execute the DLookup again?
Each DLookup acts as a seperate search in your database which can cause SIGNIFICANT slow down of your processes. Even if the Dlookup only takes 0.2 seconds, using the VAL variable costs 0.00000001 second.
Consider using this piece of code 1.000 times on a day

Also please please please, indent, readable code is maintainable code

Dim val As Integer


Dim rs As DAO.Recordset
Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
With rs
    Do While Not rs.EOF
    
    
        rs.Edit
        val = Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK]), 0)
        rs("Qty") = Nz(DLookup("[RemainingQty]", "tblQtySoFarTEMP", "[OrderDetailPK]= " & rs![OrderDetailFK]))
        rs.Update
        
        rs.MoveNext
    Loop
End With
Set rs = Nothing

 

[aron.ridgway]

Ha i have heard Dlookup is a bad habit!, but the table its looking up is a temp so there are only a maximum of 15 lines, so i am hoping Dlookup should be fine to use.

the val was an error and i have deleted that line now, All points noted i will try and get into the habit of indenting, it defiantly makes it more readable!

Thanks for the pointers!

p.s what process would you use instead of dlookup?

 

[namliam]

brrrr... temp tables another bad habit !

I dont know if in this case there is a better solution. Not saying there is always a better solution than a dlookup, you could use a fullblown recordset but that is no worse nor better than a DLookup PROVIDED you use the DLookup ONLY once. Which in this case you are.

In most cases though you can make a join in a query or something do get a simular or same result without much trouble.... again some exceptions prove the point!