The OnChange event fires every time a character is entered in a Textbox, which is not really appropriate for this kind of thing; the determining Control's AfterUpdate event is normally used, when it's done at the Form-level.
JoeM's suggestion of doing it in a Query is a good one, or you could do it using the Control Source Property of the Control where the determining data is being entered. But you need to explain a little more about the specifics, here, especially the "based on the other fields of the other tables" part. How many 'other fields' in how many 'other tables?' The answers to these questions may determine the best approach in this specific case.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007