Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-10-2012, 10:14 AM   #1
pseudonymn
Newly Registered User
 
Join Date: Apr 2012
Posts: 3
Thanks: 3
Thanked 0 Times in 0 Posts
pseudonymn is on a distinguished road
Unhappy Message upon duplicate first, last, MI

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.

pseudonymn is offline   Reply With Quote
Old 04-10-2012, 01:23 PM   #2
Beetle
Duly Registered Boozer
 
Join Date: Apr 2011
Location: Camp Swampy (Denver, CO)
Posts: 1,806
Thanks: 13
Thanked 458 Times in 449 Posts
Beetle will become famous soon enough Beetle will become famous soon enough
Re: Message upon duplicate first, last, MI

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 = "[Last Name]=""" & Me![Last Name] & _
           """ And [First Name]=""" & Me![First Name] & _
           """ And [Middle Initial]=""" & Me![Middle Initial] & """"

'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("*", "YourTable", 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.
__________________
Sean Bailey

Dim Thirsty As String
Thirsty = "Insert Into Glass (Select Beer From Fridge)"
DoCmd.Execute Thirsty, dbPourAnotherOnError
Beetle is offline   Reply With Quote
The Following 2 Users Say Thank You to Beetle For This Useful Post:
pseudonymn (04-10-2012), RJM3750 (04-10-2012)
Old 04-10-2012, 01:38 PM   #3
pseudonymn
Newly Registered User
 
Join Date: Apr 2012
Posts: 3
Thanks: 3
Thanked 0 Times in 0 Posts
pseudonymn is on a distinguished road
Re: Message upon duplicate first, last, MI

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!

pseudonymn is offline   Reply With Quote
Old 04-10-2012, 02:20 PM   #4
Beetle
Duly Registered Boozer
 
Join Date: Apr 2011
Location: Camp Swampy (Denver, CO)
Posts: 1,806
Thanks: 13
Thanked 458 Times in 449 Posts
Beetle will become famous soon enough Beetle will become famous soon enough
Re: Message upon duplicate first, last, MI

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 = "[Last Name]=""" & Me![Last Name] & _
               """ And [First Name]=""" & Me![First Name] & """"
Else
    'Check First, Last and MI
    strWhere = "[Last Name]=""" & Me![Last Name] & _
               """ And [First Name]=""" & Me![First Name] & _
               """ And [Middle Initial]=""" & Me![Middle Initial] & """"
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("*", "YourTable", 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
__________________
Sean Bailey

Dim Thirsty As String
Thirsty = "Insert Into Glass (Select Beer From Fridge)"
DoCmd.Execute Thirsty, dbPourAnotherOnError
Beetle is offline   Reply With Quote
The Following 2 Users Say Thank You to Beetle For This Useful Post:
pseudonymn (04-11-2012), wolf10851 (05-29-2013)
Old 04-10-2012, 07:59 PM   #5
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,312
Thanks: 11
Thanked 719 Times in 669 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Message upon duplicate first, last, MI

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)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
The Following User Says Thank You to missinglinq For This Useful Post:
pseudonymn (04-11-2012)
Old 04-11-2012, 09:23 AM   #6
pseudonymn
Newly Registered User
 
Join Date: Apr 2012
Posts: 3
Thanks: 3
Thanked 0 Times in 0 Posts
pseudonymn is on a distinguished road
Re: Message upon duplicate first, last, MI

This is fabulous, it works perfectly. Thank you guys!
pseudonymn is offline   Reply With Quote
Old 04-11-2012, 11:56 AM   #7
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,312
Thanks: 11
Thanked 719 Times in 669 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Message upon duplicate first, last, MI

Glad Camp Swampy and I could help!

Good luck on your project!

Linq ;0)>

__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 05-29-2013, 05:28 PM   #8
wolf10851
Newly Registered User
 
Join Date: May 2013
Posts: 24
Thanks: 3
Thanked 0 Times in 0 Posts
wolf10851 is on a distinguished road
Re: Message upon duplicate first, last, MI

Quote:
Originally Posted by Beetle View Post
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 = "[Last Name]=""" & Me![Last Name] & _
           """ And [First Name]=""" & Me![First Name] & _
           """ And [Middle Initial]=""" & Me![Middle Initial] & """"

'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("*", "YourTable", 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.
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
wolf10851 is offline   Reply With Quote
Old 05-29-2013, 07:11 PM   #9
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,312
Thanks: 11
Thanked 719 Times in 669 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Message upon duplicate first, last, MI

Quote:
Originally Posted by wolf10851 View Post

...it did not actually save the record after saying yes I wanted to continue...
"...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)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 06-10-2013, 01:35 PM   #10
bmtsa
Newly Registered User
 
Join Date: May 2013
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
bmtsa is on a distinguished road
Re: Message upon duplicate first, last, MI

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.
bmtsa is offline   Reply With Quote
Old 06-10-2013, 06:37 PM   #11
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,312
Thanks: 11
Thanked 719 Times in 669 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Message upon duplicate first, last, MI

Quote:
Originally Posted by bmtsa View Post
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.
What does this have to do with the subject of this thread?
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 06-12-2013, 07:10 AM   #12
bmtsa
Newly Registered User
 
Join Date: May 2013
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
bmtsa is on a distinguished road
Re: Message upon duplicate first, last, MI

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

bmtsa is offline   Reply With Quote
Reply

Tags
dcount , duplicate , msgbox , unique

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Message box if duplicate value skwilliams Forms 16 09-29-2009 10:07 AM
Duplicate Error message vipersmind Tables 5 09-11-2005 10:18 PM
Duplicate error message help goju General 3 07-06-2005 06:35 AM
Duplicate value message sloaner14 Forms 6 03-12-2004 01:10 AM
duplicate - error message deekras Forms 0 02-10-2001 11:36 PM




All times are GMT -8. The time now is 06:07 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World