Prohibit End-User From Entering a pre-existing Value

[jmitchelldueck]

I have a table for (EmployeeT) and a form for a user to enter new employees into the table (EmployeeF). The form requests the employees first and last names, and the table concatenates the two to display as "Last Name, First Name".



How do I make sure my end user doesn't input two of the same employee into the table? I know this can be accomplished by using my primary key, but currently my pk field is my employee ID number and it is linked to several other tables. My other thought was to set my indexed field property to Yes (No Duplicates), but I can't do that since it is a calculated field. What about something in the calculation of the field?

Thanks,
jmd

[ranman256]

This is the bane of all db managers. People have the same names, so people cant be unique.
If You are SURE you will never hire a person with the same name, then make a query, qsEmpNames, that has the combined name [LAST]&[FIRST] as NameID
then on Save click , do a check 1st...

the form would have a hidden field ,txtName where it combines the txtFirstN and txtLastN

if Dlookup("[NameID]","qsEmpNames","[NameID]='" & txtName & "'") = txtName then
    msgbox "Name already exists"
     cancel = true
else
     'save data here
endif

[jmitchelldueck]

After asking the question, I found this method. I tested it out, and it works for my purposes. On the incredibly small chance that an employee shows up that has the same name as a pre-existing one, is there a bypass that can be written?

Or, is there a way to display a warning message if a duplicate entry is entered and allow my end-user to go ahead with the entry anyways?

[June7]

Cannot bypass composite index.

Can certainly display warning and give user options. Just more code.

If Dlookup("[NameID]","qsEmpNames","[NameID]='" & txtName & "'") = txtName then
   If MsgBox("Name already exists. Is this really a different person and you want a new record entered?", vbYesNo) = vbNo Then
      Cancel = True
   End If
End If

[jmitchelldueck]

Where would I write this code? On the form or in the table?

[June7]

Cannot write code behind table (except for Data Macros).

The real trick is figuring out what event to put code into. Since entry into two fields (first and last names) is required, little more complicated. Suggest that last name control be disabled until entry of first name. Use Conditional Formatting to manage this. Then try suggested code in BeforeUpdate event of last name textbox.

[jmitchelldueck]

Hi June

How do I accomplish this with Conditional Formatting?

[June7]

Conditional Formatting is only to enable/disable the LastName textbox.

Only textboxes and comboboxes have Conditional Formatting. Conditional Formatting is found on the ribbon Design tab.

The rule for disabling LastName textbox would be: [FirstName] Is Null or maybe IsNull([FirstName])

I thought this would allow simpler validation code because only the LastName textbox will need the VBA code to look for existing name value. The code I suggested is really incomplete. If existing name is found, then the firstname and lastname fields/boxes need to be set null and user starts over.

Review https://msdn.microsoft.com/en-us/lib.../ff845199.aspx

[jmitchelldueck]

I used VBA code on GotFocus event to force the user to enter firstName before lastName:

Private Sub lastName_GotFocus()
If IsNull(firstName) Then
    firstName.SetFocus
    MsgBox "You must enter the physician's first name before their last name."
Else
    lastName.SetFocus
End If
End Sub

Let me know if you see any potential issues with this.

Will post the validation code I end up with on completion for your review as well.

Thanks!

[June7]

I don't think need the Else but try it and find out.

[jmitchelldueck]

You are correct, thanks.

I'm encountering a runtime error upon setting the the lastName field to null.

Private Sub lastName_BeforeUpdate(Cancel As Integer)
If DLookup("[fullName]", "PhysicianT", "[fullName]='" & fullName & "'") = fullName Then
    If MsgBox("Name already exists. Continue with this entry?", vbYesNo) = vbNo Then
        firstName = Null
        lastName = Null
        Cancel = True
        End If
End If
End Sub

Let me know if you know why.

[June7]

Try the Undo method demonstrated in the example code in referenced link.

[jmitchelldueck]

I had to do it AfterUpdate.

Private Sub lastName_AfterUpdate()
If DLookup("[fullName]", "PhysicianT", "[fullName]='" & fullName & "'") = fullName Then
    If MsgBox("Name already exists. Continue with this entry?", vbYesNo) = vbNo Then
        firstName = Null
        lastName = Null
        firstName.SetFocus


                    
        End If
        
End If
End Sub

It makes both of my text boxes blank and flips the focus back to the firstName field.

[June7]

So is resolved?

[jmitchelldueck]

Yes, thanks. Just wanted to post solution for others' reference.