How to change a field value depending on the values of other fields in a form

[ManuelLavesa]

Hello
I have a form with a continuous subform. The form is for receiving the Purchase Orders, the subform shows the records (items) for the order. So each record contains 5 fields, namely Productname, QtyPurchased, QtyReceived, Purchase Date and Status. Also I am keeping the same PurchaseOrderID for the different items within one order. I am trying to change the value of the Status of a Purchase Order depending on whether or not the quantity received is equal to the quantity Purchased. On the quantity after update event I have the following code:

Private Sub QtyReceived_Afterupdate()
If Me.QtyReceived = 0 Then
Me.Status_Name = "Ordered"
Else
If Me.QtyReceived = Me.QtyPurchased Then
Me.Status_Name = "Posted to Inventory"
Else
If Me.QtyReceived < Me.QtyPurchased Then
Me.Status_Name = "Incomplete"

End If
End If
End If
End Sub

This code seems to work but when there are more than one record (item) in the order the value of the last record affects the value of all the records within the order. I just one to affect the single record. Is there a way of accomplishing this? thank you for your help.

[ItsMe]

The Value property for an unbound control will be the same for each instance of said control within the continuous form.

[June7]

So your options:

1. expression in textbox ControlSource property

2. bind control to field and use code to populate the field, however, saving this calculated value is not advisable

[ManuelLavesa]

Thank you for your reply ItsMe. The field Status is not unbound as far as I know is just a field from a table PurchaseOrder. But you might be right. I will check it.

[ManuelLavesa]

Hello June7, Thank you for your reply. I did try to do it in the control Source with the expression builder. But I am not very good at multiple IIf statements in the expression builder. So I decided to go with option 2. I know that saving calculated values is not a good idea but in this case I think it will be useful, because it will say whether the order is in progress, Posted to inventory or incomplete. I will use this values to find the orders depending on their status. So the problem I think I have is that I am using a continuous form and somehow the fields are not bound. I thought I had all the fields bound to the tables. The Query that I used for this continuous subform has the following sql:

SELECT PurchaseOrder.fkProductID, PurchaseOrder.fkPurchaseOrderID, PurchaseOrderStatus.StatusID, Product.ProductName, PurchaseOrder.QtyPurchased, PurchaseOrder.QtyReceived, PurchaseOrder.PurchaseDate, PurchaseOrderStatus.StatusName
FROM Product INNER JOIN (PurchaseOrderStatus INNER JOIN PurchaseOrder ON PurchaseOrderStatus.[StatusID] = PurchaseOrder.StatusID) ON Product.ProductID = PurchaseOrder.fkProductID;

How do you determine if the fields are bound or unbound in the form. Thanks for your help.

[June7]

If control is bound, its ControlSource property will be set with a field name.

[ManuelLavesa]

Then all the fields in the continuous subform are bound to their controls. Then why is not working the code I wrote in message #1. How can I modify it to get each entry with their own respective value of Status name.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[ManuelLavesa]

Attachment 22308 In this database go to Purchase Order Receiving Button, then select order 22 or which ever you want because it does it in all of them. Then change the qtyReceived value to see the changes in the status name. You will see that the last of the values is the one that goes to most or all the other records... I just don't know what I am doing wrong?? thanks for your help.

[June7]

Change code to:

If Me.QtyReceived = 0 Then
    Me.Status_Name = "Ordered"
ElseIf Me.QtyReceived = Me.QtyPurchased Then
    Me.Status_Name = "Posted to Inventory"
ElseIf Me.QtyReceived < Me.QtyPurchased Then
    Me.Status_Name = "Incomplete"
End If

Use indentation in your code and it will be easier to read.

Or expression in textbox:

=Switch([QtyReceived]=0,"Ordered", [QtyReceived]=[QtyPurchased],"Posted to Inventory", [QtyReceived]<[QtyPurchased],"Incomplete")


NOTE: misspelled RecievedDate

[ManuelLavesa]

Thank you june7. The switch statement works very well. Case solved.