Data entry/edit design issue

[DLG]

I have a table of clients with SSN as the key.  I have set up a form to accept new clients.  I would like to have this single form do data entry for new and edit for old.  After entering the SSN i have the SSN looked up in the table to make sure it doesn't already exist.  If it exists the code (at this time) doesn't allow me past the SSN field. I don't want to fill in the entire form only to find out the SSN already exists.  If it does exist I would like it to move into edit mode if the user desired.  How would be the best way to have this happen? Or would 2 sep forms be better; one for entry/one for edit? Thanks

 

[famousb]

i've done similar work, except using telephone # as the primary key - same concept. <br>what i found worked good for me, was to have the form used first for editing, and then for entry.<br>basically, after the field (combo-box) was filled in, the form was populated with the data of the customer.&nbsp;&nbsp;in the NotInList property of the combo-box i put a statement that asked the user to input the new client.<br>the code i used was this:<br><br><b>&nbsp;&nbsp;&nbsp;&nbsp;Dim iAnswer As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;iAnswer = MsgBox(&quot;Person is not currently in list.&nbsp;&nbsp;Please Add&quot;, _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;vbOKCancel + vbQuestion)<br>&nbsp;&nbsp;&nbsp;&nbsp;If iAnswer = vbOK Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me.TelephoneCombo.Value = Null<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.GoToRecord acActiveDataObject, , acNewRec<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<b><br><br>this worked good for me.<br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[DLG]

Thanks Brian.&nbsp;&nbsp;I tried what you suggested.&nbsp;&nbsp;i get an error message: Run time error '2105'<br>You can't go to the specified record.<br>You may be at the end of a recordset.<br>My cbf follows:<br>Private Sub ClientSSN_NotInList(NewData As String, iAnswer As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;iAnswer = MsgBox(

 

[famousb]

ok, do you have your form set to edit mode, or add mode?<br>the error message is coming because the form isn't allowed to accept new records, but the <br><b>DoCmd.GoToRecord acActiveDataObject, , acNewRec</b><br>is telling it to go to a new record.<br><br>if you have a switchboard opening the form, make sure that it opens the form in add or data entry mode.&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp; <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>