VBA to Check Exceeding Quantity

[ChrisSedgwick]

Hi,

I'm trying to create and AfterUpdate event that will display a MsgBox if a Quantity entered into Box A, is greater than the Quantity shown in Box B.

I've managed to find a piece of code on this forum that sort of looked like it was what I needed. However, in my circumstances, it isn't just a flat form that I'm using the code in.

I have 1 form that contains 3 sub-forms within each other. I've called them

Main Form - FitterOrderAdd
1st Subform - TEST_Level2_Phase
2nd Subform - LEVEL_3_PRODUCT
3rd Subform - TEST_Level_4_Fitter

(Don't know why I named them this way. I think it was a rush job, so apologies).

The Text Box that we're entering the quantity in to is called "Actual Quantity" and is from the 3rd Subform. The Text Box that I want it to check against is called "Revised Quantity" and is from the 2nd Subform.

The code that I found is below. When I enter a quantity into "Actual Quantity" and tab along, I receive a Run-time error 2465 "Microsoft Access can't find the field "LEVEL_3_PRODUCT" referred to in your expression"

I'm presuming this is because I have different levels of subforms?

Private Sub Actual_Quantity_AfterUpdate()
If Me.Actual_Quantity > Form("LEVEL_3_PRODUCT")!["Revised Quantity"] Then
MsgBox "The quantity entered is greater than the Phase quantity.", vbInformation, "Phase Quantity"
Else
MsgBox "Quantity Accepted", vbInformation, "Phase Quantity"
End If

End Sub

Could some offer a helping hand.

Man thanks,

Chris.

 

[ChrisSedgwick]

Just to update you. I've managed to do what I was originally looking to do, by creating a Macro Event After Update.

I created the macro by opening the LEVEL_3_PRODUCT form and tested it and it worked. However, when I closed the form and opened the main form which contains all of the sub-forms, ran the same test, I got this message...

"Microsoft Access cannot find the referenced form 'LEVEL_3_PRODUCT'

* The form you referenced may be closed or may not exist in this database.* Microsoft Access may have encountered a compile error in a Visual Basic module for the forum."

Do you think this could be because the Level 3 Form isn't open exclusively? Although it is open through the subform.

I really do need a work around for this... I'm tearing my hair out!

Thanks,
Chris.

 

[ChrisSedgwick]

Yes, this is the case. I've left the Level_3_Product form open in the background and opened the main form. Run the same test and I get the preferred results. As soon as I close the form in the background. It stops working.

This is where I'm now stuck...

 

[arnelgp]

you should probably use the BeforeUpdate event of the control, so you can stop further entry of user when the condition is not met.

Private Sub Actual_Quantity_BeforeUpdate(Cancel As Integer)
If Me.Actual_Quantity > Me.Parent![LEVEL_3_PRODUCT].Form![Revised Quantity] Then
Cancel = True
MsgBox "The quantity entered is greater than the Phase quantity.", vbInformation, "Phase Quantity"
Else
MsgBox "Quantity Accepted", vbInformation, "Phase Quantity"
End If

End Sub

 

[ChrisSedgwick]

Hi arnelgp,

Thanks for your reply.

I'm still receiving the referencing error. See images attached.

Thanks,
Chris

 

[arnelgp]

a subform inside a subform, is that it?

Private Sub Actual_Quantity_BeforeUpdate(Cancel As Integer)
If Me.Actual_Quantity > Me.Parent.Form![Revised Quantity] Then
Cancel = True
MsgBox "The quantity entered is greater than the Phase quantity.", vbInformation, "Phase Quantity"
Else
MsgBox "Quantity Accepted", vbInformation, "Phase Quantity"
End If

End Sub

 

[ChrisSedgwick]

Perfecto! Worked a treat!

I'm new to VBA so haven't come across referencing a parent subform this way. I always assumed you had to use the forms index and go in that way.

Anyway, lesson learnt. Thanks again!