validation help

[deb]

I have a text field called TLNo in a subform called fTLNo
I have a text field called ReceivedDate in a sub-subform called fWS.

TLNo cannot be blank or null.
If TLNo ="TBD" then RecievedDate must be null
IF TLNo has any other text then the ReceivedDate must NOT be null.

I need this to fire if data in either form is changed.

I tried putting msg boxes in the form update of both forms but I got myself
in a loop and once I changed one field it fired an error for the other field.
Ugly...

How can I enter it so it fires no matter which form is changed and allows
the ability to change the data in either form before it fires again?

Thanks,

 

[Clifford Bass]

Hi Deb,

Quick questions: Are there multiple rows in the sub-subform? Or is it
limited to one?

Clifford Bass

 

[Sninkle]

Let me know if this doesn't work

1) If you want TLNo to be a required field, go to the table it belongs to
and change the field setting Required to Yes. This may not affect any
current blank fields.

2) You can try entering the following code in the Form_Current event and the
TLNo_AfterUpdate() event: Please note Z = [MAINFORM], A = [SUBFORM 1], B =
[SUBFORM 2]


If Forms!Z!A.Form!TLNo = "TBD" Then
Forms!Z!A.Form!B.Form!ReceivedDate.Enabled = False
Else
Forms!Z!A.Form!B.Form!ReceivedDate.Enabled = True
End If

This will disable ReceivedDate if 'TBD' is entered in TLNo

 

[deb]

Default view is Single form.

Thank you in advance for your help!!

 

[deb]

I am having problems following your instructions...
My main form is f000Deliverable the sub form is tTLNo and the sub-subform is
f001WS

I put this in OnCurrent of the main form called f000Deliverable and the
error says it does not find the form f000Deliverable


If Forms!f000Deliverable!fTLNo.Form!TLNo = "TBD" Then
Forms!f000Deliverable!fTLNo.Form!f001WS.Form!WSDateReceive.Enabled =
False
Else
Forms!f000Deliverable!fTLNo.Form!f001WS.Form!WSDateReceive.Enabled =
True
End If

I have not even put the code in the subform text box called TLNo after
update yet. It does not look like it will work there either.
What am I doing wrong?

--
deb

Let me know if this doesn't work

1) If you want TLNo to be a required field, go to the table it belongs to
and change the field setting Required to Yes. This may not affect any
current blank fields.

2) You can try entering the following code in the Form_Current event and the
TLNo_AfterUpdate() event: Please note Z = [MAINFORM], A = [SUBFORM 1], B =
[SUBFORM 2]


If Forms!Z!A.Form!TLNo = "TBD" Then
Forms!Z!A.Form!B.Form!ReceivedDate.Enabled = False
Else
Forms!Z!A.Form!B.Form!ReceivedDate.Enabled = True
End If

This will disable ReceivedDate if 'TBD' is entered in TLNo
--
Carrie

I have a text field called TLNo in a subform called fTLNo
I have a text field called ReceivedDate in a sub-subform called fWS.

TLNo cannot be blank or null.
If TLNo ="TBD" then RecievedDate must be null
IF TLNo has any other text then the ReceivedDate must NOT be null.

I need this to fire if data in either form is changed.

I tried putting msg boxes in the form update of both forms but I got myself
in a loop and once I changed one field it fired an error for the other field.
Ugly...

How can I enter it so it fires no matter which form is changed and allows
the ability to change the data in either form before it fires again?

Thanks,

 

[deb]

I was thinking thru what you are suggesting and ...
if the user has a TLNo input, and a received date input
then
changes the TLNo to TBD. It disables the Received date with the date still
in the field. I need a way to prevent this.
If TLNo is TBD there cannot be a received date. and if there is a Received
date there must be a TLNo that is not TBD

Thank you agin for helping me solve this!!
--
deb

Let me know if this doesn't work

1) If you want TLNo to be a required field, go to the table it belongs to
and change the field setting Required to Yes. This may not affect any
current blank fields.

2) You can try entering the following code in the Form_Current event and the
TLNo_AfterUpdate() event: Please note Z = [MAINFORM], A = [SUBFORM 1], B =
[SUBFORM 2]


If Forms!Z!A.Form!TLNo = "TBD" Then
Forms!Z!A.Form!B.Form!ReceivedDate.Enabled = False
Else
Forms!Z!A.Form!B.Form!ReceivedDate.Enabled = True
End If

This will disable ReceivedDate if 'TBD' is entered in TLNo
--
Carrie

I have a text field called TLNo in a subform called fTLNo
I have a text field called ReceivedDate in a sub-subform called fWS.

TLNo cannot be blank or null.
If TLNo ="TBD" then RecievedDate must be null
IF TLNo has any other text then the ReceivedDate must NOT be null.

I need this to fire if data in either form is changed.

I tried putting msg boxes in the form update of both forms but I got myself
in a loop and once I changed one field it fired an error for the other field.
Ugly...

How can I enter it so it fires no matter which form is changed and allows
the ability to change the data in either form before it fires again?

Thanks,

 

[Clifford Bass]

Hi Deb,

I need to rephrase the question. Can the user add multiple rows in the
sub-subform for any particular one row in the subform? If not, you only have
to deal with one row. But if so and if the user changes TLNo from TBD to
something else and there are three rows in the sub-subform, then you are
going to have to require that all three be changed. This impacts how you go
about implementing the requirement.

Clifford Bass

 

[deb]

only one row.

Thank you for your help
--
deb

Hi Deb,

I need to rephrase the question. Can the user add multiple rows in the
sub-subform for any particular one row in the subform? If not, you only have
to deal with one row. But if so and if the user changes TLNo from TBD to
something else and there are three rows in the sub-subform, then you are
going to have to require that all three be changed. This impacts how you go
about implementing the requirement.

Clifford Bass

 

[Clifford Bass]

Hi Deb,

Since it is only one row, how about this? Create a query that joins
the child and grandchild tables, using a left outer join.

SELECT tblChild.RecordID, tblChild.TLNo, tblGrandchild.ReceiveDate
FROM tblChild LEFT JOIN tblGrandchild ON tblChild.RecordID =
tblGrandchild.ParentRecordID;

Use that as the record source of the subform. Move the fields from the
sub-subform to the subform and remove the sub-subform. Then you can verify
the stuff all in the before update event of the subform.

Clifford Bass