Probelm Creating Recordset

[benkingery]

I’m trying to create a function to keep track of inventory levels and I’m having a little problem with the VBA code I’m putting in.

I have a 2 tables that look like this

tbl.Transactions
Fields (TransactionID (PK), Date, Item (FK-tbl.Item.[Child_SKU]), Type (‘Credit’ or ‘Debit’), QTY)

tbl.Item
Fields (Child_SKU, Title, CurrentOnhand)

What I want to do is calculate the current onhand inventory of an item and update it to [CurrentOnhand] in tbl.Item. At first I created an aggregrate function query to calculate the available onhand. The SQL is below:

SELECT AZ_Credits.Item, Sum([AZ_Credits.QTY]*IIf([Type]="Credit",1,-1)) AS CurrentOnhand
FROM AZ_Credits
GROUP BY AZ_Credits.Item;

I’ve verified that this query is returning the values that I’d like and the math is being done properly. My only problem is I need the results of this query to be put into tbl.Item in the CurrentOnhand field so I can use it for all the purposes I need it for. Not being able to call the results of a sum query into an update query to update, I looked into creating a recordset with VBA. The VBA function below is what I have built in:


Public Sub Code()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySql As String
mySql = "SELECT AZ_Credits.Item, Sum([AZ_Credits.QTY]*IIf(Type='Credit',1,-1)) AS CurrentOnhand FROM AZ_Credits GROUP BY AZ_Credits.Item"

myRecordSet.Open (mySql)
While Not myRecordSet.EOF
sSQL = "UPDATE AZ_Children SET [CurrentOnhand]=myRecordSet![CurrentOnhand]"
DoCmd.RunSQL (sSQL)
myRecordSet.MoveNext
Wend

End Sub


I think this code is mostly working, but its asking me to enter the Parameter Values for myRecordSet!CurrentOnhand and I’m not sure what’s going on with that.


Anyone have any ideas about what might not be working properly or what I've missed?

 

[pbaldy]

The direct answer to your question is:

sSQL = "UPDATE AZ_Children SET [CurrentOnhand]=" & myRecordSet![CurrentOnhand]

That said, why try and store a value that can be easily calculated, as you are doing with your query? You could simply join that query to your items table and you'd have a dynamic inventory count at all times, without having to worry about keeping it updated.

 

[benkingery]

Thank you for the reply. I will certainly try this out. The answer to your question about why not join my item table to the query is I had not idea you can do that.

How would I go about joining the two?

 

[pbaldy]

Here's the SQL from one of mine:

SELECT tblPartNumbers.PartNumber, tblPartNumbers.PartDesc, vueCurInventory.OnHand
FROM tblPartNumbers LEFT JOIN vueCurInventory 
  ON tblPartNumbers.PartNumber = vueCurInventory.PartNumber

 

[benkingery]

I tried the original fix that you gave me and now its updating ALL the records in the item table to the same value. I need it to only update the amounts for each item. Do I have to add something to the SQL statement that would create a relationship between the Recordset and the Item table?

 

[benkingery]

I tried the SQl you gave me and that creates something that can be useful. I'd still like to try the recordset option if possible. Do you have any idea how to fix the problem I'm seeing now?

 

[pbaldy]

I was going to mention that problem would occur, but figured you were going the other way anyway. Yes, you have to add a WHERE clause to your SQL. Something like this if item is a numeric data type:

sSQL = "UPDATE AZ_Children SET [CurrentOnhand]=" & myRecordSet![CurrentOnhand] & " WHERE Item = " & myRecordSet!Item

But I would really recommend not trying to save that value and keep it updated.

 

[benkingery]

Thanks for this. I'll try it out. I like to keep inventory snapshots every once in a while so I can track the movement of inventory over time. That's the reason for saving the values. Our system is set up around evaluating inventory allocated to multiple online selling channels at a time, so seeing how that inventory moves and changes over time is very helpful to us.

Thanks for your help. You've answered all the questions I had exactly as I needed.

 

[benkingery]

Actually, what if my item field is NOT number data type? Its actually TEXT data type.

 

[pbaldy]

No problem. I'd point out that since you're not saving it with a date, you don't really have a history anyway, unless you export that table periodically, and you could export the query just as easily. You can get a "point-in-time" inventory by adding a date criteria to the query. That said, it's your baby, so you should do what you're comfortable with. Post back if you get stuck on anything.

 

[pbaldy]

Try this for text:

sSQL = "UPDATE AZ_Children SET [CurrentOnhand]=" & myRecordSet![CurrentOnhand] & " WHERE Item = '" & myRecordSet!Item & "'"

 

[benkingery]

Great this worked perfectly. I'm sure I could search it out, but now it asks me if I want to continue and update the 1 record for each line in the item table. Anyway I can get it to always answer "yes" to that question?

 

[pbaldy]

Try this

http://www.mdbmakers.com/forums/showthread.php?t=2679

 

[gemma-the-husky]

just one thing

to distinguish between goods in and goods out, i would be inclined to store the true effect of the movement, rather than store each movement as a plus qty

so goods in , positive adjustments - store as plus quantities
sales, negative adjustments - store as negative quantities

otherwise you will find it increasingly difficult to easily calculate the net movement, as you add new stock movement types - eg stock take adjustments/opening balances etc, and have to take into account the true effect of the movement