Prompt to Ask If User Wants to Create a Duplicate Record

For example, let's say there's already a record for Mary Shelly, and I
have a new Mary Shelly that I'm trying to add to my database. Can I
make it so that Access will prompt me and say "There is already a
record matching this description. Do you wish to continue?"

0
DoveArrow
10/23/2007 9:20:07 PM
access.forms 6864 articles. 2 followers. Follow

3 Replies
473 Views

Similar Articles

[PageSpeed] 36

Hi Dove,

I'm assuming first and last name are in the same field. Create a
button or Label and add this code to the On Click event but make the
following changes to the code:

NAMEFIELD= The name of the field where the name is kept on the form
TABLEWITHUSERNAMES = The name of the table where the username is kept

Code:

Dim db As Database
Dim Rst As Recordset

Set db = CurrentDb
Set Rst = db.OpenRecordset("TABLEWITHUSERNAMES", dbOpenDynaset,
dbSeeChanges)

Rst.MoveFirst
Do Until Rst.EOF

If Rst!NAMEFIELD = Forms![FORMNAME]![NAMEFIELD] Then GoTo line200

Rst.MoveNext
Loop

200
Msg = "That name already exists in the database. Do you want to
continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "DUPLICATE NAME"  ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then goto 300
If Response = vbNo then Me.Undo
Me.refresh

300
End Sub


Try that. I'm kinda newbie at VBA too and I used this code to check
for duplicates aginst the source table.





0
Richnep
10/23/2007 10:00:58 PM
Dove, -I FORGOT A GOTO STATMENT the code below is correct

I'm assuming first and last name are in the same field. Create a
button or Label and add this code to the On Click event but make the
following changes to the code:


NAMEFIELD= The name of the field where the name is kept on the form
TABLEWITHUSERNAMES = The name of the table where the username is kept



 Dim db As Database
 Dim Rst As Recordset

 Set db = CurrentDb
 Set Rst = db.OpenRecordset("TABLEWITHUSERNAMES", dbOpenDynaset,
 dbSeeChanges)

 Rst.MoveFirst
 Do Until Rst.EOF

 If Rst!NAMEFIELD = Forms![FORMNAME]![NAMEFIELD] Then GoTo line200

 Rst.MoveNext
 Loop
goto 300


 200
 Msg = "That name already exists in the database. Do you want to
 continue?"
 Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
 Title = "DUPLICATE NAME"  ' Define title.
 Response = MsgBox(Msg, Style, Title)
 If Response = vbYes Then goto 300
 If Response = vbNo then Me.Undo
 Me.refresh

 300
 End Sub

0
Richnep
10/23/2007 10:06:56 PM
On Oct 23, 3:06 pm, Richnep <Rich...@gmail.com> wrote:
> Dove, -I FORGOT A GOTO STATMENT the code below is correct
>
> I'm assuming first and last name are in the same field. Create a
> button or Label and add this code to the On Click event but make the
> following changes to the code:
>
> NAMEFIELD= The name of the field where the name is kept on the form
> TABLEWITHUSERNAMES = The name of the table where the username is kept
>
>  Dim db As Database
>  Dim Rst As Recordset
>
>  Set db = CurrentDb
>  Set Rst = db.OpenRecordset("TABLEWITHUSERNAMES", dbOpenDynaset,
>  dbSeeChanges)
>
>  Rst.MoveFirst
>  Do Until Rst.EOF
>
>  If Rst!NAMEFIELD = Forms![FORMNAME]![NAMEFIELD] Then GoTo line200
>
>  Rst.MoveNext
>  Loop
> goto 300
>
>  200
>  Msg = "That name already exists in the database. Do you want to
>  continue?"
>  Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
>  Title = "DUPLICATE NAME"  ' Define title.
>  Response = MsgBox(Msg, Style, Title)
>  If Response = vbYes Then goto 300
>  If Response = vbNo then Me.Undo
>  Me.refresh
>
>  300
>  End Sub

That is amazing! Thank you.

0
DoveArrow
10/23/2007 10:33:02 PM
Reply:

Similar Artilces:

How to change Machine name without Prompt for credentials
Hi I'm hoping there's a way to do this, but I'm not sure. I have an OU configured which contains some Computer accounts and a couple of user accounts. To one of the accounts I've delegated permissions to add and delete "computer" objects in that OU. When I'm logged into one of the computers in the OU and I try to rename the Computer, I'm prompted for credentials, even though I'm already logged on using the delegated account. I enter the username/password and all is good but I'd like not to be required to enter the user/pass. Is the...

create a outlook task
i am trying to create a daily task, to be sent each day. when i do this it only sends it out for one day and then stops. i have tryed different ways, i actually had it correct but had a problem with outlook and dont remember how to do it anymore. can this be done? ...

Exception when using Notify User alerts in custom view
Hello all! One of our users have found an error when he tried to setup notify user alerts from a custom list whenever there are changes on the list items included in a custom list view. Exception occured. (Exception from HRESULT: 0x80020009 (DIS_E_EXCEPTION)) Looking at the logs, we found the corresponding entry: Watson collection started: Windows SharePoint Services 3, ULSException12, w3wp.exe, 6.0.3790.3959, 45d6968e, onetutil.dll, 12.0.6327.5000, 48a405c4, NA, b51c6, c0000005, 837l Any clue to what have triggered this problem? Thanks! ...

my admin users are not shown on the welcome screen
hi. good time. first i delete the last administrator user (not built-in). then i disable the built-in administrator. and i logoff the windows. now i cannot login to widows. please help me, i dont want to re-install windows. good luck. Shirazian wrote: > hi. > good time. > first i delete the last administrator user (not built-in). > then i disable the built-in administrator. > and i logoff the windows. > now i cannot login to widows. > please help me, i dont want to re-install windows. Try logging on to Safe Mode with the Administrator account. To get ...

Maps User.ArrangeData
I'm working on trying to use the US State maps in Visio 2000. I've determined that the User.Mapdata cell is the minimum and maximums for each latitude and longitude for the state, but I have been unable to figure out what User.Arrangedata is. Does anyone have any ideas? I'm trying to use given lat&long coordinates to mark cities on a map and am also learning visio at the same time. Thanks I figured I'd add in a sample... here is the Arrangedata for Ohio: ="0.101337|0.110899|2.298160|6.538787 and the Mapdata: ="-84.812119|38.400429|-80.520203|41.986816|1"...

current user returns admin
I would like to log in one of the databases the username of the person who is making changes to a record in a database, but I noticed that access 2207 doesn't have user level security anymore and the currentuser method just returns admin. Is there a way to do this? On Jan 3, 10:16 am, icccapital <icccapi...@discussions.microsoft.com> wrote: > I would like to log in one of the databases the username of the person who is > making changes to a record in a database, but I noticed that access 2207 > doesn't have user level security anymore and the currentuser method ju...

SQL Select Query Prompting for NonExistent Parameter
I'm using Access 97. I've entered the following SQL Query into the Query Tab. =================== SELECT * FROM tblVehicles WHERE fldVehicleTag = ABC123; ==================== When I double click the Query to execute: Instead of returning the recordset Access prompts me for a Parameter. When I enter the tag # --> ABC123 it returns the correct record. Why am I being prompted for a Parameter and how do I stop it? Thanks David On Wed, 17 Mar 2010 22:25:13 -0400, "David" <NoWhere@earthlink.net> wrote: >I'm using Access 97. &g...

Creating a form #3
Can I creat a Form in Publisher? I am able to create a form in MS Word 2000 that people can just check off boxes. I am using Publisher 98, and I'm wondering if any later versions are able to create these same forms. Thank you. Publisher 98 has business form templates. Try adapting one of them for your own needs. All versions of Publisher have business form templates. There are others on the Office web site, but Publisher 98 is not compatible with those. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.ip...

Prompting for customer information
Does anyone know of a way to force the POS to prompt for an E-mail address at each transaction? I would like to start sending coupons and such using E-mail. Thanks in advance, Scott Scott, There is no such feature out of the box with RMS, however we have two applications that are very similar in nature that could be easily modified to suit your needs. If you are interested please drop me a line at 1-888-267-RITE or drop me a line at rsakry@rite.us. -- Thank you, Ryan Sakry Program Manager Retail Information Technology Enterprises (RITE) rsakry@rite.us http://www.rite.us 320-230...

TimeStamp & User Modified for Budget
There are no Date/TimeStamp or User Modified fields for Budget data. There is no way of knowing who/when numbers were posted. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mi...

Specified Users only can access one common mailbox
I am using exchange 2003 server on win2003 platform. I have win2003 DC. I have some common mailboxes to the relevant departments. That mailboxes can be accessed only by the specified users. For Example : Department : Purchase Mailbox name : purchase Users name : abc.pur, xyz.pur, fgh.pur When the specified users try to see the mails, they can only view their department email id. Is it possible to prohibit? If so please let me know. A fast response would be appreciated Shanthi Shanthi - To clarify, you are attempting to grant these users access to an additional mailbox? If so, open...

Deactivate a rule created by rule deployment wizard
Hello, I am a begginer into CRM customization and I would truly appreciate your help with the following: I was experimending with rule deployment wizard and I created a rule that forwards every e-mail that arrives in a mailbox in the same mailbox. The result is that we receive every e-mail twice! I have tried everything I could imagine to deactivate this rule but I can't find it anywhere. Could you please help? regards Nikos Open Workflow Manager Select correct address of CRM server Select Entity Type as E-mail. Wait for list of rules to come up on grid below. Select the desired r...

How do I create a list of tables or figures for APA?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, <br><br>I'm trying to create a list of figures and a list of tables for a dissertation (required, can't get around it) that follows APA formatting. <br><br>My problem is that every time I try to do it using the caption and insert index and tables functions, it lists the word Figure for each one and transfers the italicization of 'Figure 1' as well. <br><br>The list must include the number of each figure, as well as the title, but in normal font. <br><br...

Ask any question about Excel 2003/2007!
Ask any question in comments of blog: http://excel4audit.wordpress.com and you will receive the answer during couple of days. ...

Security prompt also
I have Verizon ISP Have Windows Vista w/ sevice pack 2. Windows mail is asking for ID and password, so I tried, SORRY. Now won't work properly, can't receive or send mail. I am trying to get my info on Acct. cuz forgot it (password). Please bare with me as I am trying to fix this. I would like to cancel Windows Mail and use Windows Live instead. I did try to add the Hotmail address to WM but it wouldn't let me. This problem did happen all of a sudden. I've been using WM since '08 and never any issues til now (prompt). Thank you for your time and any suggestions. ...

User Form Landscape
Hi All On a userform I have the following code Private Sub CmdPrint1_Click() UserForm1.PrintForm End Sub Is it possible without changing the print option in the control panel to have the UserForm print in Landscape by adding to or changing the above code. Thanks for any help Cheers Mully ...

E-mail duplicates
Hi, how can I remove more than thousand e-mail duplicates easily? (freeware plugin) Thank in advance: luupocok@hotmail.com select them and hit the delete key. real easy. "LuuPocok" <luupocok@hotmail.com> wrote in message news:%23$fE7eTmFHA.3380@TK2MSFTNGP12.phx.gbl... > Hi, > > how can I remove more than thousand e-mail duplicates easily? > (freeware plugin) > > Thank in advance: > luupocok@hotmail.com > > I would have do this for years... 1170 email duplicates "Jack Handey" <nothere@overthere.com> wrote in message news:...

2003 Small Business Edition
My wife and i have 2 laptops in the house. I have 2003 Small Business Edition loaded on my computer, we would like to load it on her computer so that she has access to Business Contact Manager. We've been told that because its 2003 edition that you can purchase additional licenses. We're not even sure that we need an additional license yet. We do not want to load it into her computer until we know for sure that we can use it. Any help/suggestions would be appreciated. Thank you. If you read the EULA, you may find the permitted installations. JStephens wrote: &...

Start Menu folders no longer connected to corresponding user folders
Before (unsuccessfully so far) upgrading to Windows 7 In deleted (OK. stupid, I know) both the Music and Pictures folders in my account. I have now replaced them from my backup but now the shortcuts in the Vista start menu no longer recognise them and remain empty. I have tried to enter the location into the shortcuts but that's actually blocked. I have checked the properties of the restored folders and they are System folders just as before. Any suggestions how I can reconnect the two? Would love to ask Windows 7 questions but won't... Thanks A. ...

denying access to default users/all users folders
Hi, we use delprof to delete inactive profiles. However users started storing files in all users/default users. Can we use GPO to prevent access? These are standard users. Thanks "wuzzle" <wuzzle1@gmail.com> screv in news:0a7e32d7-1ace-400e-9c94-90ab94b3d076@y10g2000prg.googlegroups.com... > Hi, we use delprof to delete inactive profiles. However users > started > storing files in all users/default users. Can we use GPO to prevent > access? These are standard users. Thanks See the replies you received in the Server newsgroup, then have a look...

count distinct records
I am trying to count the numberof disctinct transactions that a sales person has worked on. It seems that the distinctcount function in Crystal Reports does not work in RMS. Does anyone have an alternative for this function? If by sales person you mean CASHIER, paste this on a notepad, save it as something like "Custom - Transaction Count by Cashier", run it from store ops mgr - reports - custom.....hope this helps //--- Report Summary --- // Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Transactions Count by Cashier" PageOrientation = ...

Calendar dates in all users outlook
G'Day All, I am running E2K and all users are running Outlook XP on their desktops. Without visiting all users computers how can I update all users' Outlook Calendar to show the days/dates the office will be closed? Thanks I have the same problem. The only workaround I could find was to send out an email meeting invitation to everyone and instruct them to click accept (which they eagerly do if it's a holiday). If you find a better way let me know! Chris ...

Ask user for input during marco
I create a marco for user to run a report for display any site with inventory less than 15%, but user want to define the percentage for the site. I was able to define the box for the user input. How can I use the value to excute the marco? Here is the marco code: Sub test1() ' ' test1 Macro ' Macro recorded 11/16/2005 by County of Orange ' ' Keyboard Shortcut: Ctrl+Shift+A ' 'Get target value from user Message = "Change values less than or equal to ..." Target = InputBox(Message) Target = Val(Target) Selection.AutoFilter ...

Protecting cells for different users
I have a excel sheet that will be processed by different users. Is i possible to protect cells to be used only by one user? Thanks -- AndyOn ----------------------------------------------------------------------- AndyOne's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2021 View this thread: http://www.excelforum.com/showthread.php?threadid=38563 Why not just give that user the password to Unprotect? Tools>Protection>Allow users to edit rang -- juli ----------------------------------------------------------------------- julie's Profile: http://ww...

Creating a line in Word 2003
I can't figure out why I don't seem to be able to create a line (actually a signature line) in Word 2003. The underline key [_____] usually does the job but there seems to be something about the style I am using that prevents that. All I get instead are blank spaces. Same with creating blank spaces and trying to underline them. What I want to create is a signature block like this: DEWEY CHEATHAM & HOWE By: _______________________ Snively Whiplash If I use a style with line spacing at 22.5 the underlining works--but of course the spacing is wrong....