Change Inventory Again - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Change Inventory Again

Adam Schwanz

6 years ago

Hello again Rick, sorry to bug you again on this. I got stuck again, I've gotten really close to having it work rewatching access 326 for the 10th time but it will only update the selected record not all included.

I've posted around on other forums looking for help just to get all different answers, none of which fixed my problem. Can you see anything wrong with this that the loop isn't going through all the records?

Option Compare Database
Private Sub ChangeInventory(PartNum As String, Qty As Long)
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * From PartsT where PartNum='" & Forms!CustomerXPartF!PartNum & "'")
    rs.Edit
    rs!InventoryQty = rs!InventoryQty + Qty
    rs.Update
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Private Function AdjustInventory() As Boolean

Dim CurrentProductQty As Long

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * From CustomerXPartT where OrderID =" & OrderID)
While Not rs.EOF
    CurrentProductQty = Nz(DLookup("InventoryQty", "PartsT", "PartNum='" & rs!PartNum & "'"))
    ChangeInventory rs!PartNum, rs!Qty * -1
    rs.MoveNext
Wend
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Function

Private Sub Command8_Click()
    AdjustInventory
End Sub

Thank You

	Richard Rost @Reply 6 years ago
In my recent years after gaining much wisdom, I don't like passing recordset values as parameters. Try assigning rs!PartNum and rs!Qty to local variables first and then pass those. `Dim PartNum as String, Qty as Long ... PartNum=rs!PartNum Qty=rs!Qty ChangeInventory PartNum, Qty` Why? It just WORKS sometimes.

	Richard Rost @Reply 6 years ago
Everything else looks solid. Your code is good. You must have had a good teacher. :) If that doesn't work, maybe pass your values ByVal: `Private Sub ChangeInventory(ByVal PartNum As String, ByVal Qty As Long)` If those don't work lemme know.

Adam Schwanz OP @Reply

6 years ago

Thanks for the quick response, indeed you've helped me create something far beyond what I imagined when I started :)

I'm still having the issue. Both of your suggestions had the same outcome. Not sure if you'll get anything from this but I'll include a few pictures here that might have something obvious in them of how I'm running it.

In the picture it would only process order 21 and then stop. Let me know if you have any other ideas and thank you.

Richard Rost @Reply

6 years ago

Yeah, I don't see it. Without playing with your database, it's difficult to troubleshoot. Your pictures didn't really help. I have had problems with recordset loops inside of other recordset loops before. Try getting rid of ChangeInventory as a function (just put that code all in one sub) and see if that works. Or, you could try DIMming db as a form-level variable (outside of all subs) and see if that works.

Alex Hedley @Reply

6 years ago

You could add a movefirst
I'd also add loads of Debug.Print statements to see what is happening, what variables are set to when.
Even things like Starting IF, Starting While etc
It might seem like overkill but you can at least see what is happening when.
I also like to build my SQL string as its own variable, then print it, then pass it to the OpenRecordset; that way you can copy it out and try it in a query window to check it returns records.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View