validation macro with If statement not working

[rsmhawaii]

I created a macro with an "If" statement using expression builder and it doesn't work and I don't receive an error.

If ([Forms]![Inventory_OutboundRequest]![SF_inventorydetailsitems].[Form]![QtyAdjustOut])> Forms![Inventory_OutboundRequest]![OnHand] Then
MessageBox
Message Insufficient inventory, please adjust your quantity
Beep No
Type Warning!
Title Inventory check

I set the property sheet value of [QtyAdjustOut] to run the macro "After Update". So far no luck. Any help is greatly appreciated...running out of hair

 

[deanamiles86]

Try this

=IIf([SF_inventorydetailsitems].[Form]![QtyAdjustOut]>[OnHand],MsgBox("Message Insufficient inventory, please adjust your quantity",0,"Title Inventory check"))

 

[rsmhawaii]

Hi deanamiles86,

Thanks for the suggestion. I get the following:

The expression After Update you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'SF_inventorydetailsitems.

The expression may not result in teh name of a macro, the name of a user-defiend fuction or [Event Procedure].

 

[deanamiles86]

Not sure why this hasn't worked, I have created a replica DB and it worked

Am I right in thinking

You are using form Inventory_OutboundRequest and You are using the field OnHand

And within the same form you are using a sub form called SF_inventorydetailsitems with the field QtyAdjustOut

 

[rsmhawaii]

yes, you are correct. I can't seem to figure it out either. The logic is correct.

 

[deanamiles86]

i cant see why you are getting this error, have you got any other events happening in this form?

 

[arnelgp]

do it in Before Update:

If ([Forms]![Inventory_OutboundRequest]![SF_inventorydetailsitems].[Form]![QtyAdjustOut])> Forms![Inventory_OutboundRequest]![OnHand] Then
MessageBox
Message Insufficient inventory, please adjust your quantity
Beep No
Type Warning!
Title Inventory check

CancelEvent

 

[deanamiles86]

ive now been able to recreate this error and i fixed it with the following

=IIf([QtyAdjustOut]>[Forms]![Inventory_OutboundRequest]![OnHand],MsgBox("Message Insufficient inventory, please adjust your quantity",0,"Title Inventory check"))