Referencing Sub Form Items

[darkstar257]

I have a shipping form where the user fills out To, From, Address, Tracking #'s, etc. and in it is a sub form that contains a table of all the items ordered and shipped

I'm trying to do a message box that pops up if not all items are shipped. ie. items shipped < items ordered

Item_Line_sub is the sub form

This code runs when I press the close order button in the main form.

If (Me.Form![Item_Line_sub]![Qty Shipped] < Me.Form![Item_Line_sub]![Qty Ordered]) Or (IsNull(Me.Form![Item_Line_sub]![Qty Shipped]) Or Me.Form![Item_Line_sub]![Qty Shipped] = 0) Then
        MsgBox ("Warning: All units have not been shipped.")
    Else
        'Ask for Close order confirmation
        intResult = MsgBox("Are you sure you want to close order?", vbYesNo)

However, the problem is Me.Form![Item_Line_sub]![...] only calls up the current line item that my mouse cursor is on. If there are 3 items being shipped, it will only run the IF statement on the table item my cursor is sitting. So if my cursor marked sub form item fulfills the IF statement even if 2 out of 3 items don't, the warning message won't show up.

How would I be able to use the IF statement to compare every line item on the sub form's table?

I saw this web page in another thread: http://www.mvps.org/access/forms/frm0031.htm

But i don't know which my case would apply to.

 

[jzwp22]

Although the webpage you reference is a very good source, it won't help you much with your particular issue. Since you are dealing with multiple records, you have to go through each one and evaluate them or treat them as a group. To go through each one, you would need some Visual Basic for Application (VBA) code to open a recordset and loop through each one to evaluate the two fields. You could also treat them as a group which might be a little easier if you do not know VBA, but you will have to utilize a domain aggregate function.

To treat them as a group you would need to sum the quantity of each line item shipped as well as the sum of the quantity of each line item ordered. So in your IF..THEN you need to evaluate the two sums. If the sum of the shipped items is not equal, the you would display the warning.

The IF..THEN might go something like this:

IF DSum("[Qty Shipped]", "tablename","yourorderIDfield=" & me.NameOfControlOnMainFormHoldingOrderID) < DSum("[Qty Ordered]", "tablename","yourorderIDfield=" & me.NameOfControlOnMainFormHoldingOrderID)  THEN
        MsgBox ("Warning: All units have not been shipped.")
    ELSE
        'Ask for Close order confirmation
        intResult = MsgBox("Are you sure you want to close order?", vbYesNo)
...rest of code...