Text Box validation in a form on OnExit or OnLostFocus

[makso35]

I need to create a macro or a code to check if data entered in the txtCase Text Box of frmContacts exists in a Clients table.<br>Should I use OnExit or OnLostFocus events?<br>

 

[elizabeth]

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.

 

[makso35]

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>

 

[elizabeth]

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.