Changing Stock levels of multiple product components by one command (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 18:31
Joined
Dec 8, 2007
Messages
163
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

Registered User.
Local time
Today, 20:31
Joined
Jan 22, 2011
Messages
2,373
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

Registered User.
Local time
Today, 18:31
Joined
Dec 8, 2007
Messages
163
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

Registered User.
Local time
Today, 20:31
Joined
Jan 22, 2011
Messages
2,373
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

Registered User.
Local time
Today, 18:31
Joined
Dec 8, 2007
Messages
163
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.
 

Mr_Si

Registered User.
Local time
Today, 18:31
Joined
Dec 8, 2007
Messages
163
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

Registered User.
Local time
Today, 18:31
Joined
Dec 8, 2007
Messages
163
Hi all,

So I have my code like this so far:

Code:
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

VBA, VB.net, C#, Java
Local time
Today, 14:31
Joined
Mar 7, 2011
Messages
515
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

Registered User.
Local time
Today, 18:31
Joined
Dec 8, 2007
Messages
163
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.

Code:
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!
 

Users who are viewing this thread

Top Bottom