How do i do a check on two input fields from main form and sub form?

[xwnoob]

I have a mainform and a subform.

My main form contains a field called mainamount and subform contains a field called subamount. I want to make sure subamount does not excedd mainamount and i used this code in beforeupdatevent:


If [subamount] > [mainamount] Then
MsgBox ("WARNING:You have entered an amount larger that total amount!")
Cancel = True
Me.subamount.Undo
End If


but it does not work..Which part of the code do i change?

[Rainlover]

MainAmount does not exist in the subform so it it is not been recognised.

Your code should first address the Main Form then the field.

You could use the expression builder in a blank query to help build the code.

[June7]

Like:

If Me.subamount > Forms!mainformname.mainamount Then
MsgBox ("WARNING:You have entered an amount larger than total amount!")
Cancel = True
Me.subamount.Undo
End If

[xwnoob]

Like:

If Me.subamount > Forms!mainformname.mainamount Then
MsgBox ("WARNING:You have entered an amount larger than total amount!")
Cancel = True
Me.subamount.Undo
End If

Hm...sorry i forgot to mention that its a relationship between subform and subsubform not mainform and subform...i have used your code but there was a error 2465(could not find facilitysubform):

Private Sub LenderCommitment_BeforeUpdate(Cancel As Integer)

If Me.LenderCommitment > Forms!facilitysubform.FacilityAmount Then
MsgBox ("WARNING:You have entered an amount larger than total amount!")
Cancel = True
Me.LenderCommitment.Undo
End If
End Sub

Do i have the put the brackets in??

Edit:

I tried this too but it doesnt work:

Private Sub LenderCommitment_BeforeUpdate(Cancel As Integer)

'Forms!AddRollover.rolloversubform.Form.ListLender Rollover

If Me.LenderCommitment > Forms!AddCommitmentDetail.facilitysubform.Facility Amount Then
MsgBox ("WARNING:You have entered an amount larger than total amount!")
Cancel = True
Me.LenderCommitment.Undo
End If
End Sub

AddCommitmentDetail is the mainform, while facilitysubform is subform, and lender commitment is the field in the subsubform

[Rainlover]

http://www.mvps.org/access/forms/frm0031.htm

Have a look at this link.

[June7]

The code is in BeforeUpdate event of which control on which form?

[xwnoob]

sorry guys it worked

[xwnoob]

Edit: Sorry but i need help again..i tried my code this afternoon and it gave an error"2465". Previously i tested the code and it worked..but today it just gave an error.

This is the code:
Private Sub LenderCommitment_BeforeUpdate(Cancel As Integer)

If Me.LenderCommitment > Forms!AddCommitmentDetail.facilitysubform.Form.Fac ilityAmount Then
MsgBox ("WARNING:You have entered an amount larger than total amount!")
Cancel = True
Me.LenderCommitment.Undo
End If
End Sub

This code is placed in the beforeupdate event of LenderCommitment in a subsubform. Im comparing this value against a field in the subform, thus i have used the code

Forms!AddCommitmentDetail.facilitysubform.Form.Fac ilityAmount.( AddCommitmentDetail is mainform while facilitysubform is subform. FacilityAmount is the field in the subform)

[Rainlover]

You could use the expression builder in a blank query to help build the code.

Create a Blank Query.

Use the expression Builder to find each value in the Forms. Do this for each Value in separate Fields.

Once you have the correct values showing you can then rebuild your VBA.

This is not the normal way of doing things but will help you to learn the correct syntax.

[Rainlover]

Your Form should be open when creating the query.

[June7]

When I have subforms I always give the subform container a name different from the form it holds, like: ctrFacility. Then the syntax to refer to controls on subform I use:

If Me.LenderCommitment > Forms!AddCommitmentDetail.ctrFacility.Form.Facilit yAmount Then

[Rainlover]

June7

You are correct. A Sub Form is actually a Control so giving it a separate Name is good practice.