Using Validation Rules on Forms & Solving Error Message

[JoeBruce]

Hey folks. Just posted an introduction thread in that forum if anyone is interested.

I have a form/subform - frmCreditNewProgram and sfrmTeacherAttendance. These forms are built from a query that includes most of the fields from all of my tables. On the subform, users enter the name of schools and the names of teachers who attended the program.

I want to make sure that when a user enters this data, they enter a school and teacher as they exist in the database (there are separate forms for adding new schools and teachers). This will also prevent duplication from someone entering "XXX Elementary School" when it's really just "XXX Elementary" or "Pam" when the teacher exists as "Pamela." I even put unbound comboboxes in the header a person can use as a reference for spelling and format.

For the bound textbox where people enter the school name, I thought the simple validation rule "IN (tblSchools.SchoolName)" would work just fine, entered in the property sheet for this textbox, but it returns this error:

The expression [tblSchools] you entered in the form control’s ValidationRule property contains the error The object doesn’t contain the Automation object ‘tblSchools.’.
Microsoft Access can’t parse the ValidationRule expression you entered. For example…

I have tried it using the query the forms are built off of, and a separate query that is just "tblSchools" but I get the same result (insert relevant query/table in to the error message). Any thoughts?

If possible, please give me a solution that does not involve VBA.

 

[pbaldy]

I don't think you can do it that way. Is there a reason you don't use a combo box on the form, with its limit to list property set to yes?

 

[JoeBruce]

That's actually what I ended up doing

I was in the process of testing a few ways to use this form as I typed the OP, and realized the text boxes were a no-go.

Yesterday I had the difficulty of using cascading combo-boxes as part of the sub form, and not being able to store the data or requery the boxes when I changed the main form record.

Now I just have a single combo box that selects the teacher, and all the other data populates as it should. It's kind of a long list, and maybe for DBv2 I can find a better way to do it, but it's working for me.

I am still curious about using data validation rules on forms; it hasn't really been functioning as I thought it would (but I've learned Access rarely does).

 

[pbaldy]

This task didn't lend itself to a validation rule. I'd say the combo is the most common method to do this. You can also set it up with referential integrity and let Access enforce it. You could also have tested in the before update event:

http://www.baldyweb.com/BeforeUpdate.htm

Using a recordset or DCount() to see if the value existed in the other table.