validating a control on a form

[skeddy]

I have a form based on table with primary key = username (skeddy, fbrown1, etc.) Want to have this primary key validated when user moves off it (to another field in the form) rather than waiting until the record is saved. And would like to have my own error message come up (rather than the system message about duplicate value in index). Sorry, this may be so easy - I'm quite new to Access programming. Thanks in advance.

 

[JimHorton]

skeddy<br>
Just use the BeforeUpdate event of the control. Then, if whatever validation you have fails, set the passed-in variable Cancel to True. Put up any message you like. If you're specifically looking to avoid the 'duplicate' message, then open a table-type recordset over the table in question, set the Index to PrimaryKey, and do a Seek to see if the newly entered value exists, then put your message up:<br>
<br>
sub txtKeyField_BeforeUpdate(cancel as integer)<br>
Dim rst as recordset<br>
set rst = currentdb.openrecordset(&quot;mytable&quot;,dbOpenTable)<br>
rst.Index = PrimaryKey 'or the name of the PK index if you renamed it from this default<br>
rst.seek &quot;=&quot;,me!txtKeyField<br>
if rst.nomatch then<br>
&nbsp;&nbsp;&nbsp;&nbsp; Exit Sub<br>
else<br>
&nbsp;&nbsp;&nbsp;&nbsp; msgbox &quot;The value &quot; & me!txtKeyField & &quot; already exists, blah, blah.&quot;<br>
&nbsp;&nbsp;&nbsp;&nbsp; Cancel = True<br>
End if<br>
End Sub