Subform Values

  • Thread starter Thread starter Tom A
  • Start date Start date
T

Tom A

I have made a field on a subform giving me a sum from the
expression OrderQty-ReceivedQty also on the subform. If
its sum is 0 then I want a Status field on the form to
read "Order Completed" if its less than 0 to read "Order
Incomplete" more than 0 "Overage". I import and update
through a update query the ReceivedQty's from an Excel
spreadsheet once a day. I thought I could get Status field
to change on 'On Open' in the form properties. It doesn't
make any changes. I can make a macro and from a button on
the form get the results I need but if I make either the
macro or VB from the form properties it doesn't work. I
don't want to click on the macro button on every record.
The following is the VB.



Function mcrQtySum()
On Error GoTo mcrQtySum_Err

If (Forms!frmPO!frmPoline.Form!QtySums = 0) Then
Forms!frmPO!STATUS = "Order Completed"
End If
If (Forms!frmPO!frmPoline.Form!QtySums < 0) Then
Forms!frmPO!STATUS = "Incomplete"
End If
If (Forms!frmPO!frmPoline.Form!QtySums > 0) Then
Forms!frmPO!STATUS = "Overage"
End If


mcrQtySum_Exit:
Exit Function

mcrQtySum_Err:
MsgBox Error$
Resume mcrQtySum_Exit

End Function
 
Tom,

You could use an unbound textbox on the form, with its Control Source
set to something like this...
Switch([frmPoline]![QtySums]=0,"Order
Completed",[frmPoline]![QtySums]<0,"Incomplete",[frmPoline]![QtySums]>0,"Overage")

Otherwise, you could try using your code to set the value of Status on
the form's Load event rather than the Open event.
 
Back
Top