Text Box validation in a form on OnExit or OnLostFocus

More likely the Before Update event, as you can avoid updating if it's an invalid value. Another common (and easier) way to do this is to use a combo box for that field, with a rowsource of a query/SQL stmt that gets all the unique values for that filed from the Clients table. You can change the text box to a combo on the form itself or (better) in the table design for that filed under Lookup properties.

First, Elizabeth thanks a lot for your quick response. I am new in the Access field and your help is very much appreciated.<br>I just realized I was not precise enough when I was writing my questions. I forgot you guys are professionals.<br>1. The reason I did not use a combo box is that I have more than 13.000 unique case numbers in the Clients table (does it matter?).<br>2. The Contacts for is rather big and I would like to stop the validation process sooner to save some time for a data entry clerk; that was the reason I was thinking of something else instead the Before Update event.<br>3.&nbsp;&nbsp;I do not know any other way how (beside a combo box) to resolve this.<br><br><br><br><br><br><br><br><br><br><br><br>

I don't have the expertise to advise you about the erformance issues on this. However I can give you more options and you can test them yourself, or you can post another thread in this forum and/or the access:general discussion one with including the word &quot;performance&quot; in it that may attract the attention of someone with more expertise in this area. BTW, these ideas are not mutally exclusive.

Other alternatives:
1) set up a 1:many relationship with referential integrity. This will not allow you to add a record to the child table that doesn't exist in the parent table.
2) use the DLookup function to look for the client number.
3) use a form with the Clients table as the main source and your child table as the subform. Have the user select the client form the existing table before entering the info.