Changing Stock levels of multiple product components by one command

[Mr_Si]

Hi all,

I have the following form and subform combination:



The subform is linked to the main form by way of the "ProductID" control. (It's hidden in the subform).

Basically, each product (the finished article) is made up of many components.

What I would like to do is this:

1. Increase the stock of a "Product".
2. The Stock level control on the main form will then increment as much as selected
3. Contrary to that, I would like each component to decrement the relevant amount based on how much of the component is used up to make the product.

E.g. If I have Product A and it contains 2 components of which component A is used once per product but component B is used twice, then when I increase stock of product by 1, then component A goes down by 1 and component B goes down by 2. But If I put two of a product in to stock, I would obviously need component A to go down by 2 but component B to go down by 4.

I am thinking I need some way to loop through each record in the sub form for the current form, such as "Do While Not", or a "For Each". And then stick an Update query within the loop, but to be honest I can't think of how I need to address it!

I would be grateful of some help.

Many thanks,
Simon

 

[Mihail]

This is a calculation. Is no need to store the result.
More, is a very bad practice to do this (to store the result).

So, your form looks good and should not be modified.

Use it to see (or input) the components for ONE product and to input the stock level.

Create a query in order to calculate the necessary quantities of components
= stocklevel * ComponentQuantity

 

[Mr_Si]

Hi thanks for your reply.
I'm not trying to store a result, I'm trying to update a value in multiple records.

I've been doing some research too, whilst seeing if any replies would happen and then I found I need to work with a recordset and loop through it.

 

[Mihail]

Hi thanks for your reply.
I'm not trying to store a result, I'm trying to update a value in multiple records.

I've been doing some research too, whilst seeing if any replies would happen and then I found I need to work with a recordset and loop through it.

Yes, you can do this.
Here is a good tutorial about recordsets:
Recordsets for Beginners - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com

but this is a VERY bad practice. Believe me: the query is what you need.

 

[Mr_Si]

Yes, you can do this....

..but this is a VERY bad practice.

Thank you.
May I ask why it's bad practice to store a value in a database and then update that value? It seems a pretty normal idea to me.

 

[Mihail]

Because the main concept of databases:
Database Normalization and Table Structures - Microsoft Access / VBA

Never never for ever store something in the DB if you can calculate that "thing".

 

[Mr_Si]

I saw nothing in that article about not storing a value if it can be calculated, that's only about database normalisation, but I appreciate your concern that I do it correctly.

 

[Mr_Si]

Hi all,

So I have my code like this so far:

Private Sub btnOK_Click()
    Dim rstProduct As DAO.Recordset
    Dim numQTY As Integer
    Dim strSELECT As String
    Dim strWHERE As String
    Dim strSQL As String
        
    Select Case OpenArgs
        Case Is = 1
            numQTY = InputBox("How many of this product would you like to put in to stock?")
            MsgBox "You entered: " & numQTY 'for debugging purposes
            
            strSELECT = "SELECT * FROM qryProduct_Component"
            strWHERE = " WHERE ProductID = " & Me.cboProductSelector
            strSQL = strSELECT & strWHERE
            
            MsgBox strSQL 'for debugging purposes
            
            Set rstProduct = CurrentDb.OpenRecordset(strSQL)
            
            With rstProduct
                Do While Not .EOF
                    .Edit
                    .Fields("ComponentStockLevel") = ComponentStockLevel - (QuantityUsedForProduct * numQTY)
                    .Update
                    .MoveNext
                Loop
            End With
    End Select
    
    DoCmd.Close
    
End Sub

Before the code runs, the stock levels are like this.




During, this happens (which is fine)




Afterwards all the relevant stock items are down to 0, regardless of how many there were to start with.



It's editing the correct items only, which is good, but I'm not sure if it's my loop that's wrong or what, but I would value your input to my code logic as to why each of the items are all decreasing down to 0.

The top one should be down by 2 in this example.
The next one should be down by 4 in this example.
The next one should be down by 2 in this example.
The next one should be down by 2 in this example.

Please note that in the last image, two of the items are irrelevant.

 

[thechazm]

I believe the reason is because both of these variables would be 0.

ComponentStockLevel
QuantityUsedForProduct

You would need to do .fields(ComponentStockLevel) to refer to a value in a table not just the name.

 

[Mr_Si]

Hi there,

Thanks for the reply - do you mean take it out of the quote marks, so

.fields(ComponentStockLevel) = ComponentStockLevel - (QuantityUsedForProduct * numQTY)

instead of

.Fields("ComponentStockLevel") = ComponentStockLevel - (QuantityUsedForProduct * numQTY)

or do you mean

.Fields(ComponentStockLevel) = .fields(ComponentStockLevel) - (.fields(QuantityUsedForProduct) * numQTY)

Thanks,

Edit, I've just tried it and my code is now as follows.

Private Sub btnOK_Click()
    Dim rstProduct As DAO.Recordset
    Dim numQTY As Integer
    Dim strSELECT As String
    Dim strWHERE As String
    Dim strSQL As String
        
    Select Case OpenArgs
        Case Is = 1
            numQTY = InputBox("How many of this product would you like to put in to stock?")
            MsgBox "You entered: " & numQTY 'for debugging purposes
            
            strSELECT = "SELECT * FROM qryProduct_Component"
            strWHERE = " WHERE ProductID = " & Me.cboProductSelector
            strSQL = strSELECT & strWHERE
            
            MsgBox strSQL 'for debugging purposes
            
            Set rstProduct = CurrentDb.OpenRecordset(strSQL)
            
            With rstProduct
                Do While Not .EOF
                    .Edit
                    .Fields("ComponentStockLevel") = .Fields("ComponentStockLevel") - (.Fields("QuantityUsedForProduct") * numQTY)
                    .Update
                    .MoveNext
                Loop
            End With
    End Select
    
    DoCmd.Close
    
End Sub

Thank you ever so much!

 

[thechazm]

No worries.