refer to subform and validate date

[MelB]

I have read several other post on this but I still don't get it. I have a database that is based on the templet with Access 2000 for Contact Management. I have a form Calls that is used to filter all calls by an individual. On that form I have a subform, the control called cntlsubListingSubform, which contains the subform Call Listing Subform. On the Call Listing Subform I have a control called cntlsubDetailsSubform which contain the subform Call Details Subform. When I open Calls I send it a Caller ID so if filters all the calls for an individual. In the Call Listing Subform I enter new calls for an individual and Before Update I check to make sure the fields that are required are filled with the IsNull(Me.cntlname) code. That works fine. The problem is that the subform on Call Listing Subform is the Call Details Subform which also has a field that is required to be filled. No matter what I try I can't get that piece of code to run. Below are some samples of what I have tried. I keep getting errors that either it can't find the form or it was an invalid reference to the property for Form/Report. Help!!!!

Also, is there an easier way? I tried to put Validation of the control on the subform Call Detail Subform.txtNotes that said Is Not Null and a Validation String that said 'The Notes Field must be filler', but that doesn't every seem to run. It seems like that would be the easiest way but it won't run... Why?

Thanks in advance...

What I have tried from the code module on the Call List Subform...

Me!cntlsubDetailsSubform.Form!txtNotes
Forms![call listing subform].[cntlDetailsSubform].Form![txtNotes]
Forms![Calls].[cntlsubListingSubform].form![cntlsubDetailsSubform].Form![txtNotes]

 

[Pat Hartman]

You need to move the validation code to the form where the note is entered.

You have a hierarchical structure - Individual - Call - Note. Hopefully, you have also established relationships that enforce referential integrity between the tables. The situation you are describing sounds like you have entered data at the Call level but not yet saved it and you are trying to validate data at the Note level. There are two problems with this conceptually:
1. The recordsource of the Note form would be empty at this point.
2. Even if you were working with an existing call and trying to modify it, there still would not be a "current" record established by the Note form since you have not yet entered the form. And without having established a "current" record, Access would have no way of knowing which record you were referring to.

When working with hierarchical (or other) relationships, the rows need to be added to the tables in a specific order. In your case, you can't have a Note unless you have a Call and you can't have a call unless you have an Individual. Therefore, you must insert the rows in Individual-Call-Note order. Access helps with this by saving the current record of a main form (assuming that it is dirty or new) whenever you move focus to a subform. Therefore, if you add a Call and then attempt to place the cursor in the Note subform, Access will save the Call record for you and at that point will execute any edits you have placed in the form's BeforeUpdate event. As you can see, it is NOT possible to edit fields in the Note form at this point because they cannot have been created yet.