Message upon duplicate first, last, MI (1 Viewer)

P

pseudonymn

New member
Local time
Today, 14:34
Joined
Apr 9, 2012
Messages
3
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.
 
B

Beetle

Duly Registered Boozer
Local time
Today, 15:34
Joined
Apr 30, 2011
Messages
1,808
Now seems as good a time as any to learn a little VBA, no?;)

Instead of using Expression Builder, use Code Builder from your form's Before Update event. When the code window opens, the following lines will be pre-existing;

Private Sub Form_BeforeUpdate (Cancel As Integer)

End Sub

Between those lines, place the following code. Note that the items highlighted in red are the ones that may/will need to be modified to match the actual names of the corresponding objects in your database (table/field names, etc.);

Code: 
'create a criteria string for the DCount. Note that references
'to the form controls (text boxes) are outside the quotes.
Dim strWhere As String

strWhere = "[COLOR="Red"][Last Name][/COLOR]=""" & Me![COLOR="red"][Last Name][/COLOR] & _
           """ And [COLOR="red"][First Name][/COLOR]=""" & Me![COLOR="red"][First Name][/COLOR] & _
           """ And [COLOR="red"][Middle Initial][/COLOR]=""" & Me![COLOR="red"][Middle Initial][/COLOR] & """"

'Use DCount to find any matching records.
'Note we wrap it in the Nz function here in case DCount returns a Null.
If Nz(DCount("*", "[COLOR="red"]YourTable[/COLOR]", strWhere),0) > 0 Then
    'If DCount > 0 then a matching record was found
    'so display a Yes/No message box.
    If MsgBox("Duplicate name entered, continue?", vbYesNo, "Duplicate") = vbNo Then
        'If the user clicked No, cancel the update.
        Cancel = True
    End If
End If

Give it a shot and post back if you need more help.
 
P

pseudonymn

New member
Local time
Today, 14:34
Joined
Apr 9, 2012
Messages
3
Thank you! This works really well! I very much appreciate how clear and complete your post is.

One thing, however. I found that it doesn't work for duplicates who do not have a middle initial, any suggestions?

Thanks again!
 
B

Beetle

Duly Registered Boozer
Local time
Today, 15:34
Joined
Apr 30, 2011
Messages
1,808
So, if I understand correctly, if the user does not enter a MI for the new record, then you want to ignore the MI in the DCount. If that's the case, you can build two slightly different criteria strings based on whether or not a MI has been entered. Modified example follows;

Code: 
'create a criteria string for the DCount. Note that references
'to the form controls (text boxes) are outside the quotes.
Dim strWhere As String
   
'check for a value in [Middle Initial] to determine the criteria.
If Nz([Middle Initial], "") = "" Then
    'No MI so only check First and Last name
    strWhere = "[COLOR="Red"][Last Name][/COLOR]=""" & Me![COLOR="red"][Last Name][/COLOR] & _
               """ And [COLOR="red"][First Name][/COLOR]=""" & Me![COLOR="red"][First Name][/COLOR] & """"
Else
    'Check First, Last and MI
    strWhere = "[COLOR="Red"][Last Name][/COLOR]=""" & Me![COLOR="red"][Last Name][/COLOR] & _
               """ And [COLOR="red"][First Name][/COLOR]=""" & Me![COLOR="red"][First Name][/COLOR] & _
               """ And [COLOR="red"][Middle Initial][/COLOR]=""" & Me![COLOR="red"][Middle Initial][/COLOR] & """"
End If

'Use DCount to find any matching records.
'Note we wrap it in the Nz function here in case DCount returns a Null.
If Nz(DCount("*", "[COLOR="red"]YourTable[/COLOR]", strWhere),0) > 0 Then
    'If DCount > 0 then a matching record was found
    'so display a Yes/No message box.
    If MsgBox("Duplicate name entered, continue?", vbYesNo, "Duplicate") = vbNo Then
        'If the user clicked No, cancel the update.
        Cancel = True
    End If
End If
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 17:34
Joined
Jun 20, 2003
Messages
6,423
A somewhat simpler DCount hack that works for me, whether the Record has a middle initial or not:

Code: 
If DCount("*", "YourTable", "[First Name] & Nz([Middle Initial]) & [Last Name]  = '" & Me.[First Name] & Nz(Me.[Middle Initial]) & Me.[Last Name] & "'") > 0 Then

And since no one else has mentioned it, please, please, please refrain from having Spaces in Field and Control Names! It can be a royal pain and a prime source of errors!

It is far better to use FirstName, LastName and MiddleIntial, as you did in your original explanation, than using First Name, Last Name and Middle Intial.

Linq ;0)>
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 17:34
Joined
Jun 20, 2003
Messages
6,423
Glad Camp Swampy and I could help!

Good luck on your project!

Linq ;0)>
 
W

wolf10851

Registered User.
Local time
Today, 14:34
Joined
May 30, 2013
Messages
24
Beetle said:
Now seems as good a time as any to learn a little VBA, no?;)

Instead of using Expression Builder, use Code Builder from your form's Before Update event. When the code window opens, the following lines will be pre-existing;

Private Sub Form_BeforeUpdate (Cancel As Integer)

End Sub

Between those lines, place the following code. Note that the items highlighted in red are the ones that may/will need to be modified to match the actual names of the corresponding objects in your database (table/field names, etc.);

Code: 
'create a criteria string for the DCount. Note that references
'to the form controls (text boxes) are outside the quotes.
Dim strWhere As String

strWhere = "[COLOR=Red][Last Name][/COLOR]=""" & Me![COLOR=red][Last Name][/COLOR] & _
           """ And [COLOR=red][First Name][/COLOR]=""" & Me![COLOR=red][First Name][/COLOR] & _
           """ And [COLOR=red][Middle Initial][/COLOR]=""" & Me![COLOR=red][Middle Initial][/COLOR] & """"

'Use DCount to find any matching records.
'Note we wrap it in the Nz function here in case DCount returns a Null.
If Nz(DCount("*", "[COLOR=red]YourTable[/COLOR]", strWhere),0) > 0 Then
    'If DCount > 0 then a matching record was found
    'so display a Yes/No message box.
    If MsgBox("Duplicate name entered, continue?", vbYesNo, "Duplicate") = vbNo Then
        'If the user clicked No, cancel the update.
        Cancel = True
    End If
End If
Give it a shot and post back if you need more help.
Click to expand...
I know this is an old post but thanks it did help me except it did not actually save the record after saying yes I wanted to continue
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 17:34
Joined
Jun 20, 2003
Messages
6,423
wolf10851 said:
...it did not actually save the record after saying yes I wanted to continue...
Click to expand...
"...wanted to continue..." what?

What event did you place this code in? It has to go in the Form_BeforeUpdate event, not in any other event, such as the BeforeUpdate event of of one of the Textboxes!

Once execution has reached the Form_BeforeUpdate event, if it is not Canceled, in this case by selecting No, from the Messagebox, the Record will be saved, assuming that this is a Bound Form.

Linq ;0)>
 
B

bmtsa

New member
Local time
Today, 16:34
Joined
May 16, 2013
Messages
3
I have days trying to solve this problem with no results. Customized form error 3022 message only works when using the DoCmd.Close. All other methods of saving a record display the standard duplicate message, which is of no use to Spanish speakers. The problem with the close command is that I get the proper error message, but the form will close anyway and the user will not have chance to correct the error as the form can not be reopened.
 
missinglinq

missinglinq

AWF VIP
Local time
Today, 17:34
Joined
Jun 20, 2003
Messages
6,423
bmtsa said:
I have days trying to solve this problem with no results. Customized form error 3022 message only works when using the DoCmd.Close. All other methods of saving a record display the standard duplicate message, which is of no use to Spanish speakers. The problem with the close command is that I get the proper error message, but the form will close anyway and the user will not have chance to correct the error as the form can not be reopened.
Click to expand...

What does this have to do with the subject of this thread?
 
B

bmtsa

New member
Local time
Today, 16:34
Joined
May 16, 2013
Messages
3
You are right, absolutely nothing to do with the thread. Unfortunately after days of searching posts on this and various other boards and being unable to start a new thread I took a shot in the dark. My apologies it won't happen again. Regards Karl
 
You must log in or register to reply here.

Similar threads

B
Solved How do I set the focus to a component on the main from an Error Event of a subform
Replies
8
Views
495
Pat Hartman
P
T
Solved Message Box -Clear field when "OK" Is clicked
Replies
16
Views
553
Pat Hartman
P
N
Solved Issue with checking duplicate values
Replies
12
Views
727
nashaz
N
M
Solved Strange error message
Replies
8
Views
485
Pat Hartman
P
A
DCOUNT - Prevent duplicate
Replies
8
Views
572
ayam
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom