If help

[deb]

I need help with an if statement.

I have a field called TLNo in the subform called fTLNo.
The TLNo must not be null or "TBD" if the sub-subform called fCS, 2 fields
called
CustTL and ReceivedDt are not null.
also
They cannot enter a CustTL without a ReceivedDt or
They cannot enter a ReceivedDt without a CustTL
and neither the CustTL or the ReceivedDt can be entered if the Parent.TLNo
is null or "TBD".

I hope I explained it correctly. I am at loss on how to do this.

 

[BruceM]

You could make the subform control for fCS visible only if TLNo on fTLNo is
not null or equal to TBD. In the After Update event for txtTLNo (the text
box bound to TLNo):

If Nz(Me.txtTLNo,"TBD") <> "TBD" Then
Me.fCS.Visible = True
Else
Me.fCS.Visible = False
End If

I expect you would need the same code in the form's Current event.

fCS is the name of the subform control (the "container" that holds the
subform). It may or may not have the same name as the subform itself.
Click the subform control to select it, then click View >> Properties. The
Property Sheet should have Subform/Subreport in the title bar. It so, click
the Format tab to see the name of the control.

See Help for more information about Nz. It substitutes the value of your
choosing for Null. If txtTLNo is null, Nz substitutes "TBD", otherwise it
returns the TLNo value. If txtTLNo is null or contains "TBD", the
expression returns "TBD".

You should be able to shorten the expression to:

Me.fCS.Visible = (Nz(Me.txtTLNo,"TBD") <> "TBD")

In fCS you can use the form's Before Update event to check ReceivedDt and
CustTL:

If IsNull(Me.ReceivedDt) Or IsNull(Me.CustTL) Then
MsgBox "You must enter both Received Date and Customer TL"
Cancel = True
End If

This could be made more specific so that it identifies the blank field and
sends the user to the appropriate text box or other control, but I'll leave
it at that for now.

If ReceivedDt and CustTL are required any time TLNo is not null or not equal
to "TBD" the simplest may be to send the user to the ReceivedDt or CustTL
text box on fCS after updating txtTLNo, but I'll wait to hear if that is
necessary. One thing at a time.

 

[deb]

Thank you for your response.
I would rather not make the fields invisible. I want the user to see all
fields so he will know the data he must eventually enter.

I need to have the parent form checked first for TLNo. If it is not null or
"TBD" and
if user enters either child fields, CustTL or RecievedDt, I want a msg to
say that TLNo must be entered first. "OK to change TLNo or CANCEL to leave as
is."

if CustTL is null and RecievedDt is not then msg "click YES to enter CustTL
or NO to remove Recieved date".

if RecievedDt is null and CustTL is not then msg "click YES to enter
RecievedDt or NO to remove CustTL".

That would be my ideal situation.
Thanks again...