By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Warn user if duplicate FirstName, LastName, MiddleInitial

P: 1
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.
Apr 10 '12 #1
Share this Question
Share on Google+
10 Replies


P: 45
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.
Apr 10 '12 #2

P: 45
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].
Apr 10 '12 #3

NeoPa
Expert Mod 15k+
P: 31,487
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.
Apr 11 '12 #4

100+
P: 759
I think that the simplest solution is to declare an index, Unique, on all three fields (in table design). Access will do the rest.
Apr 11 '12 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   'Check that no user exists with the same name
  3.  
  4.   'Only perform check for new records
  5.   If Me.newRecord then
  6.     Dim lngUserCount as Long
  7.     lngUserCount=DCount("*","[tbl_Person]", _
  8.       "[tx_FirstName]='" & Me.tb_FirstName & "' AND " & _
  9.       "[tx_LastName]='" & Me.tb_LastName & "' AND " & _
  10.       "[tb_MiddleInitial]'" & Me.tb_MiddleInitial & "'")
  11.  
  12.     If lngUserCount>0 then
  13.       Dim intReply as VbMsgBoxResult
  14.       intReply=Msgbox("Warning: Possible Duplicate Entry detected!" & vbnewline & _
  15.               "Press Yes to continue Saving," & vbnewline & _
  16.               "Press No to return and edit" & vbnewline & _
  17.               "Press Cancel to discard all changes",vbYesNoCancel+vbExclamation)
  18.  
  19.       Select Case intReply
  20.          Case vbYes
  21.             'Allow save
  22.             Cancel=false
  23.          Case vbNo
  24.             'Stop the save
  25.             Cancel=True
  26.          Case vbCancel
  27.             'Stop the save and undo the form
  28.             Cancel=True
  29.             Me.Undo
  30.       End Select
  31.     End If
  32.   End If
  33. End Sub
Apr 11 '12 #6

NeoPa
Expert Mod 15k+
P: 31,487
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 ;-)
Apr 11 '12 #7

100+
P: 759
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 :))))
Apr 11 '12 #8

NeoPa
Expert Mod 15k+
P: 31,487
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?
Apr 11 '12 #9

100+
P: 759
@NeoPa
such a situation is unacceptable, but there may be exceptions

Well... now is clear. Thank you !
Apr 11 '12 #10

NeoPa
Expert Mod 15k+
P: 31,487
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 ;-)
Apr 11 '12 #11

Post your reply

Sign in to post your reply or Sign up for a free account.