Question - Overriding SSN in NotInList

[mommom]

The user found a problem with the code listed below. If they happen to type in the SSN in the form where another document was already opened. When it ask if they want to add to the current list it overrides the SSN that was originally there and put the new number in place of the data. Is there a way when it saves to the current list the rest of the fields become blank?


Private Sub SSN_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available SSN " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new SSN to the current list?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new SSN?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("SSN", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SSN = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub

 

[belovedcej]

This isn't really a "problem" - it's doing what it should.

Instead, you should consider making the form uneditable until the user clicks an "edit" or "add" button. Then they have to choose to add a new record before they add a new social security number and it won't overwrite any old data.

 

[mommom]

How do you make a form uneditable?

 

[belovedcej]

On the onOpen Event of the Form, put the following code:

Me.AllowEdits = False

Then create a button on the form called "Edit" and another called "Save"

on the onclick event of the Edit button, put Me.AllowEdits = True

on the onclick event of the Save button, put Me.AllowEdits = False

Check out the form properties - you may find several that will come in handy.

 

[mommom]

That works but the problem still when you say edit and you type in a SSN that is not in the database it saves a new SSN but it is not making the rest of the form blank. It is keeping whatever information that was in there there and overriding the other SSN.

For example:

In database have

SSN: 111-11-1111
LastName: Doe
FirstName: John
POB: Anywhere
DOB: 1/1/21

While that information is up a user may put in another SSN and it says it is not in the list do you want to add. When you click yes it overrides 111-11-1111 and put in the new number 222-22-2222 but still have
LastName: Doe
FirstName: John
POB: Anywhere
DOB: 1/1/21

 

[belovedcej]

The problem is that they are trying to enter a new record overtop of an old record. Of course it is overwriting the data!

Instead, add another button, called "Add new record."

You can use the button wizard for that - just follow the steps to create a new record using the button.

Then, instruct the users that if they want to create a new record, they must first press the "add new record" button. Otherwise, all they are doing is editing the data they are on.

 

[mommom]

I already have a new record button, but unfortunately some users are not using that all the time. I just thought that maybe there is a way to make the field blank so even though they put the information in and when asked to add to list it wouldn't override the other SSN.

 

[belovedcej]

Sure - there is a way - but you will run into nasty if/thensituations. For instance, what if they DID press new record and then it's not in the list. You don't want it to start over again. Or what if they were changing an incorrect SSN? It is better to train the users to always hit "new record."

But if you really want to do this, just bear in mind the consequences and think through the fact that your logic will need to be something like this:

on notinlist:

MsgBox "The SSN is not on the list. Do you want to create a new record?"

if yes:
1.  clear the combo box (Me.Undo will probably work.)
2.  create a new record  (copy the code from the add button.)
3.  add the SSN to the list (use the code you already have)

if no:
1.  msbbox "do you want to add this SSN to the list and overwrite the current SSN" --use the code you already have.