complicated if on form

[deb]

Forms are setup as follows:
Main form - fProject
subform - fDeliverable
sub-subform - fTLNo
fTLNo has 3tabs with forms: f001WithinS, f002SCustomer and f003CustomerS

Now the fun part...
If fTLNo.TLNo <> "TBD" then
f001WithinS.WSDateReceive, f002SCustomer.SCDateSent cannot be null.
The validations are much more complicated so I shortened them for this
question.

because they are all on different tabs, I tried putting some of the
validations in fTLNo and some in the lowest forms and I get msgs popping up
every time a field is entered.

How can I make it check for this validation when all data is entered and the
user is ready to move on to a new fDeliverable or new fProject? I am not
sure the best place to check for validations.

thanks

 

[Jeanette Cunningham]

Hi deb,
validations are most useful when put on the BeforeUpdate event of a form.
When the data is changed on a form (edit or new record), the form's before
update event always runs before the record is saved.

Note that controls also have a before update event, skip this and use the
before update for the form.

You can put these validations in the main form and also in any subforms'
before update events.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[deb]

Thank you for your reply...

I entered the below in the subform called fDeliverable. I want to validate
if user adds or displays another Deliverable. The fields that need the
validation are on forms below the Deliverable form.
It does not fire when I click AddNew button or when I move to another record.
Is this because the validations are regarding fields on sub-subforms and
sub-sub-subforms?

They do however, fire when I re-display a record with the validation issue.
If I move to another record and then back to the problem record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
''If Sent/Received Date is assigned and TLNo is TBD then delete dates or
change TLNo.
If ((Not IsNull(Me.fTLNo.Form.f001WithinS.Form.WSDateReceive)) Or _
(Not IsNull(Me.fTLNo.Form.f002SCustomer.Form.SCDateSent)) Or _
(Not IsNull(Me.fTLNo.Form.f003CustomerS.Form.CSDateReceive))) And
(Me.fTLNo.Form.TLNo = "TBD") Then
msg = "TL Number cannot be ""TBD"" if Sent or Received dates are
assigned.D1" _
& vbCr & vbCr & "Select Ok to change TL Number" _
& vbCr & vbCr & "or Cancel to remove ALL Sent and Received Dates."
ans = MsgBox(msg, vbOKCancel, "Attention")
If ans = vbOK Then
MsgBox "Please, enter correct TL Number."
Me.fTLNo.Form.TLNo.SetFocus
' Cancel = True
' Exit Sub
Else
MsgBox "If assigned, the Sent and/or Received Dates on ALL TABS" _
& vbCr & vbCr & "and Customer TL Number will be removed."
Me.fTLNo.Form.f001WithinS.Form.WSDateReceive = Null
Me.fTLNo.Form.f002SCustomer.Form.SCDateSent = Null
Me.fTLNo.Form.f003CustomerS.Form.CSDateReceive = Null
Me.fTLNo.Form.f003CustomerS.Form.CSCustomerTLNo = Null
End If
End If

 

[Jeanette Cunningham]

The validation routine must run in the before update event of the form which
has fields that must be filled in before the record can be saved.
If I had an order form and the quantity ordered needed to be filled in
before the record was saved, I would run a validation that checked if there
was an entry in the quantity ordered textbox. If the textbox was empty, then
I would cancel the update (which means the record can't be saved) and tell
the user with a msg box.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


If this is not what you want, please explain in more detail what you are
trying to do.