validation rules in form and subform

[Guest]

I have 2 calculated fields, one in a form and one from the subform. I'm
comparing the calculated ending dates as follows:

Private Sub contract_end_date_BeforeUpdate(Cancel As Integer)

If Me.CalcEndDate([Me.Term_type], [me.Term_mos], [me.Acceptance_date]) >=
[Forms]![frmEffective_contract]![Contract_end_date] Then
Cancel = True
MsgBox "Please revise the contract term. The ending date on your subform is
greater than the main contract's date."
End If
End Sub

Why isn't it triggering the msgbox when the subform's ending date is
1/14/2006 and the main form's ending date is 12/31/2005? Note: the
CalcEndDate is a function that calculates the contract's ending date.

Thank you,
gg

 

[Guest]

You'd better off if you assign control values into typed variables, then pass
those variables to your function for the calculation. That way, you will have
easy time to set break point on each step and step through.
Normally, the controls' values are not capable of taking care of the data
type date. I am not sure how you compare two text string with value of
"12/31/2005" and "1/14/2006".

 

[Guest]

Could you please provide a way to compare the 2 dates? One is on the main
form and the other date is on the subform. Would I put the code in the
validation rule properties area instead? I currently have the Is Not Null in
the validation rule. How would I add this other code since it is another
validation that is required?

Thank you!

gg

You'd better off if you assign control values into typed variables, then pass
those variables to your function for the calculation. That way, you will have
easy time to set break point on each step and step through.
Normally, the controls' values are not capable of taking care of the data
type date. I am not sure how you compare two text string with value of
"12/31/2005" and "1/14/2006".

I have 2 calculated fields, one in a form and one from the subform. I'm
comparing the calculated ending dates as follows:

Private Sub contract_end_date_BeforeUpdate(Cancel As Integer)

If Me.CalcEndDate([Me.Term_type], [me.Term_mos], [me.Acceptance_date]) >=
[Forms]![frmEffective_contract]![Contract_end_date] Then
Cancel = True
MsgBox "Please revise the contract term. The ending date on your subform is
greater than the main contract's date."
End If
End Sub

Why isn't it triggering the msgbox when the subform's ending date is
1/14/2006 and the main form's ending date is 12/31/2005? Note: the
CalcEndDate is a function that calculates the contract's ending date.

Thank you,
gg

 

[Larry Linson]

Could you please provide a way to compare the 2 dates? One is on the main
form and the other date is on the subform. Would I put the code in the
validation rule properties area instead? I currently have the Is Not Null
in
the validation rule. How would I add this other code since it is another
validation that is required?

Which date is from the Form in the Subform Control? There is no reference in
your code to a Form in a Subform Control. If it is the second one, you
should be aware that a Form displayed in a Subform Control is not "Open"
and, thus, is not in the Forms Collection. That Form instance exists as the
Form property of the Subform Control.

Reference Text Box txtA in the Form embedded in Subform Control sbfS as:

Me!sbfS.Form!txtA

Larry Linson
Microsoft Access MVP