Problem with updating parts table from a loop 1

[Ricky1946]

Hello all of you helpers,
I have a problem trying to update a table from a list of parts in a sub form. I will try to explain:
I have a table of parts.
I have an assembly which contains some of the parts in this table which shows a quantity to be extracted from the parts table via a query.
I have created code to count through these items in the query which works on the form.
What I now need to do is enter code on each loop to look at the item description, find it in the parts table. Then find the stock level in the parts table and change the quantity according to the number in the assembly form - thus extracting the stock from the main stock records.
I hope you understand this I have tried to keep it as simple as possible.

Here is the loop code:

'Procedure to withdraw assembly items from the main stock levels
Private Sub CmdMoveStock_Click()
Dim ReadQuantityMove As Integer
Dim ReadStockLevel As Integer
Dim NewStockLevel As Integer
Dim MinStockLevel As Integer
Dim AssemblyPartNo As String
Dim AssemblyDescription As String

'Set up to count through records
Dim dbSpazio As DAO.Database
Dim rcdStockQty As DAO.Recordset
Set dbSpazio = CurrentDb
Set rcdStockQty = dbSpazipenRecordset ("AssemblyExtractQRY")


'Loop
Do Until rcdStockQty.EOF

ReadQuantityMove = Val(Quantity)
ReadStockLevel = Val(StockLevel)
NewStockLevel = ReadStockLevel - ReadQuantityMove

StockLevel.Value = NewStockLevel


DoCmd.GoToRecord , , acNext 'Go to next record

rcdStockQty.MoveNext

Loop

End Sub


End Of Code

Can anybody make suggestions how I can do this?

Regards
Ricky

"To say 'thankyou' encourages others."

 

[hkaing79]

Is AssemblyExtractQRY an updateable query? You can try rs.edit and rs.update, but it might not work if the query is not updateable. Also, you need Val(rcdStockQty.Fields("Quantity"), no?

Have you thought about using an Update query?

Join the two tables. Drag and drop StockLevel. Update to [StockLevel]-[Quantity].

 

[Ricky1946]

Hello hkqing79,
I will take a look at your comments and get back to you.
Thanks for your help.
Regards
Ricky

"To say 'thankyou' encourages others."

 

[Zion7]

If I've understood correctly?????

'Loop
Do Until rcdStockQty.EOF
rcdStockQty.Edit
ReadQuantityMove = rcdStockQty!Quantity
ReadStockLevel = rcdStockQty!StockLevel
NewStockLevel = ReadStockLevel - ReadQuantityMove

rcdStockQty!StockLevel.Value = NewStockLevel


DoCmd.GoToRecord , , acNext 'Go to next record
rcdStockQty.Update
rcdStockQty.MoveNext

Loop

rcdStockQty.Close: set rcdStockQty = Nothing

Me.Requery: Me.Refresh

 

[Zion7]

ooops, remove

DoCmd.GoToRecord , , acNext 'Go to next record

I forgot...

good luck!

 

[Ricky1946]

Thankyou Zion7,
I will try it out and let you know.
Regards
Ricky

"To say 'thankyou' encourages others."

 

[Zion7]

Ricky1946, I may have JUST, understood your objective.

Your subform, based on AssemblyExtractQRY, holds parts thaty are needed/used from the main inventory tblParts.

You want to loop through the AssemblyExtractQRY, to find all requests, for a specific part, (or maybe it's one part per record?). Either way, this total is reduced from the Main "Inventory" table.
Here's what I propose, open recordset, on MAIN INVENTORY TABLE...

 Dim dbSpazio As DAO.Database
    Dim rcdStockQty As DAO.Recordset
    Set dbSpazio = CurrentDb
    Set rcdStockQty = dbSpazio.OpenRecordset ("tblInventory")
    
               
            'Loop
            Do Until rcdStockQty.EOF
rcdStockQty.Edit

rcdStockQty!Quantity = rcdStockQty!Quantity - DSUM("Quantity", "AssemblyExtractQRY" , "ItemDescription = '" & rcdStockQty!ItemDescription & "'")

rcdStockQty.Update

rcdStockQty.MoveNext

Loop

 

[Ricky1946]

Hello again zion7,
Yes I think you do understand what I am trying to do.
I have tried your code but on the line:
rcdStockQty!Quantity = rcdStockQty!Quantity - DSUM etc etc. I get an error which states 'Item Not Found In This Collection'.
Also when typing in the code it does not like the "'" parts, did you mean these marks?
Regards
Ricky
Thanks for your help.

To say 'thankyou' encourages others.

 

[Zion7]

I'm assuming, ItemDescription is a string data Type, as opposed to an integer? thus, the need to surround with quotes.
And, I made assumptions with names of tables & controls.

The loop is done on the MAIN inventory Table, DSUM uses fields on the Assembly extract query.

Are the names changed accordingly?

 

[Ricky1946]

Hello again zion7,
I have not visited for a few days due to the Christmas celebrations, but here I am back to the stress.
Your code is nearly working, here it is with updated fields etc.
CODE:
Dim dbSpazio As DAO.Database
Dim rcdStockQty As DAO.Recordset
Set dbSpazio = CurrentDb
Set rcdStockQty = dbSpazipenRecordset ("PartsTBL")


'Loop
Do Until rcdStockQty.EOF
rcdStockQty.Edit

rcdStockQty!StockLevel = rcdStockQty!StockLevel - DSum("Quantity", "AssemblyExtractQRY" , "Item = '" & rcdStockQty!Item & "'")

rcdStockQty.Update

rcdStockQty.MoveNext

Loop

END OF CODE:

It is nearly working. The 'rcdStockQty!StockLevel' is reading the stock (eg 100), but it does not seem to like the DSum segment. The 'rcdStockQty!Item' is reading the stock item (eg Door), Now I am getting an error saying the there are two fields called Item in the query. (One if from the PartsTBL and the other from the AssemblyItemTBL. How do I tell the code to look at the PartsTBL?

Once again thankyou for your very appreciated help.
I hope your new year is a very prosperous and peaceful one.
Regards
Ricky

To say 'thankyou' encourages others.

 

[Zion7]

Seasons greetings....

try this...

rcdStockQty!StockLevel = rcdStockQty!StockLevel - DSum("Quantity", "AssemblyExtractQRY" , "PartsTBL.Item = '" & rcdStockQty!Item & "'")

Good luck!