Warn user if duplicate FirstName, LastName, MiddleInitial

[pseudonymn]

Hello everyone,

I am painfully new to Access and have absolutely no VBA knowledge. I've taken on the the burden of creating a database and up until now I've only used the expression creator or the macro creator to add functionality to my forms.

I understand this is a problem that was discussed here before but I really haven't been able to get the aforementioned solutions to work with my form and was hoping to get some suggestions.

I would like to have a message box come up upon entry of a duplicate name. This is complicated due to my names being broken up into FirstName LastName and MiddleIntial. I hope to have a message box that warns users, but does not prevent entry altogether.

I've tried to use the expression builder to create an expression that could be placed in the beforeupdate for the form, but to no avail, I receive an "Invaild number of arguments" notice upon trying to save.

Here is my expression:
= IIf( DCount([ALL]![Last Name])=0 & DCount([ALL]![First Name])=0 & DCount([ALL]![Middle Intial])=0, Null, MsgBox("Duplicate name entered, continue?",1, "Duplicate Name")))


I know this isn't really the optimal method of pursuing this, again my lack of ability to write VBA code has limited my results. Any leads would be appreciated.

[JayF]

Hi,

I have the following expression in a text box on my contact details form:

=IIf(DCount("*","[eqryContactsExtended]","[Contact ID]<>" & Nz([Contact ID],0) & " And [Contact Name] = '" & Replace(Nz([Contact Name]),"'","''") & "'")>0,"POSSIBLE DUPLICATE","")

It displays "POSSIBLE DUPLICATE" if there's a matching name.

I'm too, erm, tired to explain how it works, I wrote it a while back and I'm still learning myself, so I'd need to deconstruct it. What I can tell you is, it works.

You should just be able to replace:
[eqryContactsExtended] with your contacts table.
[Contact ID] with your equivalent primary key.
[Contact Name] is a calculated query field. If you have your name in two fields you can replace this with the two fields with an ampersand between them ("[first] & [last]").

Good luck. I'll check back tomorrow if you need any more info.

Jay


P.S. I just remembered, I took this from the Contact Management Database template from the online template repository for MSO2007. Might be a good place to look.

[JayF]

And I just realised I might have completely missed what you were trying to achieve.

My solution above can't be used to display a MsgBox, it just displays text on the form.

Do you have a specific need for an intrusive MsgBox as opposed to a passive text box in red font? If so, I'll get back to you tomorrow if nobody else has. It's bed time.

Remember you can use a concatenation of fields for comparison purposes, e.g. [FName]&[MInit]&[LName].

[NeoPa]

I'd use the AfterUpdate events of each of the three controls to call a common procedure which does the check. Essentially, if any of the values is changed then the check should be performed.

A simple DLookup() or DCount() with the Criteria parameter set to filter on all three controls should give you what you need.

[Mihail]

I think that the simplest solution is to declare an index, Unique, on all three fields (in table design). Access will do the rest.

[TheSmileyCoder]

Now first I have to make some assumptions, since you lack some details in your original post. As you read my reply please try to think over if you could have included such details in your original post, and try to remember to include them if you start new threads.

Assumptions:
Table Name: tbl_Person
Fields:
KEY_Person: Autonumber
tx_FirstName: Text,50
tx_LastName: Text, 50
tx_MiddleInitial: Text 5

On your form, I assume the controls bound to each field are textboxes and named as such:
tb_PersonID
tb_FirstName
tb_LastName
tb_MiddleInitial

In your FORMS BeforeUpdate I would then add the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  'Check that no user exists with the same name
 
  'Only perform check for new records
  If Me.newRecord then
    Dim lngUserCount as Long
    lngUserCount=DCount("*","[tbl_Person]", _
      "[tx_FirstName]='" & Me.tb_FirstName & "' AND " & _
      "[tx_LastName]='" & Me.tb_LastName & "' AND " & _
      "[tb_MiddleInitial]'" & Me.tb_MiddleInitial & "'")
 
    If lngUserCount>0 then
      Dim intReply as VbMsgBoxResult
      intReply=Msgbox("Warning: Possible Duplicate Entry detected!" & vbnewline & _
              "Press Yes to continue Saving," & vbnewline & _
              "Press No to return and edit" & vbnewline & _
              "Press Cancel to discard all changes",vbYesNoCancel+vbExclamation)
 
      Select Case intReply
         Case vbYes
            'Allow save
            Cancel=false
         Case vbNo
            'Stop the save
            Cancel=True
         Case vbCancel
            'Stop the save and undo the form
            Cancel=True
            Me.Undo
      End Select
    End If
  End If
End Sub

[NeoPa]

Mihail:
I think that the simplest solution is to declare an index, Unique, on all three fields (in table design). Access will do the rest.

This is not an appropriate answer for this thread Mihail, as the OP already said (See post #1) :

pseudonymn:
I hope to have a message box that warns users, but does not prevent entry altogether.

Please understand that I'm not criticising. We can all overlook important sentences sometimes (I certainly do), but it needs to be pointed out ;-)

[Mihail]

Indeed I overlook that, NeoPa.
Also, based on my English skills, I am not sure that I understand well this sentence: "but does not prevent entry altogether". What I understand from that is that the bound controls can be filled with data but duplicates not be allowed at update time. And my suggest should work in this area.

PS
Do not be worried about criticism. I am ( I think :) ) a true male :))))

[NeoPa]

It actually means that the operator can decide the new record is to be saved, but they must only do so after being warned. Normally, such a situation is unacceptable, but there may be exceptions. Does that make it clearer?

[Mihail]

@NeoPa
such a situation is unacceptable, but there may be exceptions
Well... now is clear. Thank you !

[NeoPa]

NeoPa:
Normally, such a situation is unacceptable, but there may be exceptions.

Lol :-D

So... It seems you must be a politician in real life Mihail. You should remember to quote things in context, otherwise, of course, it will say something quite different from what was actually said ;-)