Preventing duplicates during data entry (and immediately display a custom message, avoiding the general Access duplicates message).

With my Access database I have a table containing products (named Products) with a Product Code field (named ProdCode) - this is an indexed text field with no duplicates allowed as I do not want duplicate entries of a product code number and need an immediate message to be displayed after data entry in this field not the access duplicates warning that appears after attempting to leave the entire record.

I have a data entry form based on the “Products” table and have attached the following code to be before update property of the field called Product Code (its control is ProdCode in the table).

Being very new to code, I have attempted to copy an example and change names as appropriate to my database however I get a compile error expected:list separator or) with the highlighted section in the line below in red.

I would be greatful if anybody could help as I really have no idea of what’s going wrong or how to fix it.

Private Sub ProductCode_BeforeUpdate(Cancel As Integer)

Const MESSAGETEXT = "A record with this value already exists."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl

strCriteria = ProductCode = """ & ctrl & """"

If Not IsNull(ctrl) Then
If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & ctrl & """") Then
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
Cancel = True
End If

End If

End Sub

Any help/ tips to get this working please:

--------------Solutions-------------

A few things.

Firstly, you don't need to call ActiveControl, because you're inside the control's BeforeUpdate event - thus the control name is known.

Secondly, while the BeforeUpdate event is running, the control's value is only accessible from its Text property. The Value property hasn't been updated yet.

Thirdly, you'd omitted the leading quote mark from the criteria string.

Lastly, if you intend to Cancel the event, I'd recommend explicitly undoing the value entered into the control.

Private Sub ProductCode_BeforeUpdate(Cancel As Integer)
Const MESSAGETEXT = "A record with this value already exists."

If Len(Me!ProductCode.Text) > 0 Then
If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """") Then
; MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
; Cancel = True

Me!ProductCode.Undo
End If

End If
End Sub

"mylechris" wrote in message news:*** Email address is removed for privacy ***...
With my Access database I have a table containing products (named Products) with a Product Code field (named ProdCode) - this is an indexed text field with no duplicates allowed as I do not want duplicate entries of a product code number and need an immediate message to be displayed after data entry in this field not the access duplicates warning that appears after attempting to leave the entire record.
I have a data entry form based on the �??Products�?� table and have attached the following code to be before update property of the field called Product Code (its control is ProdCode in the table).
Being very new to code, I have attempted to copy an example and change names as appropriate to my database however I get a compile error expected:list separator or) with the highlighted section in the line below in red.
I would be greatful if anybody could help as I really have no idea of what�??s going wrong or how to fix it.
Private Sub ProductCode_BeforeUpdate(Cancel As Integer)
Const MESSAGETEXT = "A record with this value already exists."
Dim ctrl As Control
Dim strCriteria As String

Set ctrl = Me.ActiveControl
strCriteria = ProductCode = """ & ctrl & """"
If Not IsNull(ctrl) Then
If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & ctrl & """") Then
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
Cancel = True
End If
End If
End Sub
Any help/ tips to get this working please:


Regards, Graham R Seach Sydney, Australia

Many thanks Graham,

I have tried it and still get errors, not too sure how to go about fixing them if I could call upon your help once again please. I realise I have a lot of learning to do so please excuse any idiotic mistakes. Also I see your from Sydney, can you recommend any Advanced Access training or VB training.

The code is copied below:

Private Sub ProductCode_BeforeUpdate(Cancel As Integer)
Const MESSAGETEXT = "A record with this value already exists."

If Len(Me!ProductCode.Text) > 0 Then
If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """") Then
; MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
; Cancel = True

Me!ProductCode.Undo
End If
End If
End Sub

I hope you can help me again with this.

Hmmm. For some reason in the code I gave you there are semi-colons in front of the MsgBox and Cancel lines. They shouldn't be there and I have no idea where they came from. They're not in the post I sent. Nevertheless, remove them and all should be well with the world.

"mylechris" wrote in message news:*** Email address is removed for privacy *** .com...

Many thanks Graham,

I have tried it and still get errors, not too sure how to go about fixing them if I could call upon your help once again please. I realise I have a lot of learning to do so please excuse any idiotic mistakes. Also I see your from Sydney, can you recommend any Advanced Access training or VB training.

The code is copied below:

Private Sub ProductCode_BeforeUpdate(Cancel As Integer)
Const MESSAGETEXT = "A record with this value already exists."

If Len(Me!ProductCode.Text) > 0 Then
If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """") Then
; ; MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
; ; Cancel = True

&nbs p; Me!ProductCode.Undo
End If
End If
End Sub

I hope you can help me again with this.


Regards, Graham R Seach Sydney, Australia

Almost there, now its coming up with syntax error in this line. Can you see it, I've been looking and cannot see it so if you could help once again please:

If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """") Then

Thanks again Graham

Yes, of course - I left off a closing bracket. Sorry.

If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """")) Then

"mylechris" wrote in message news:*** Email address is removed for privacy *** .com...

Almost there, now its coming up with syntax error in this line. Can you see it, I've been looking and cannot see it so if you could help once again please:

If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """") Then

Thanks again Graham


Regards, Graham R Seach Sydney, Australia

Thanks again, its so close but so far away- Im back to my original error message- a compile error.

The error displayed is: compile error expected:list separator or)

With this line:

If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """")) Then

Now this really has me beat so if your once again kind enough to help out.

OK, I've triple-checked the code and it looks fine, so my thoughts now turn to you regional settings. As a test, try replacing the commas with semicolons.

If Not IsNull(DLookup("ProdCode"; Products"; "ProductCode = """ & Me!ProductCode.Text & """")) Then

If that doesn't work, please look at your regional settings and let me know what your list separatror character is.

"mylechris" wrote in message news:*** Email address is removed for privacy *** .com...

Thanks again, its so close but so far away- Im back to my original error message- a compile error.

The error displayed is: compile error expected:list separator or)

With this line:

If Not IsNull(DLookup("ProdCode", Products", "ProductCode = """ & Me!ProductCode.Text & """")) Then

Now this really has me beat so if your once again kind enough to help out.


Regards, Graham R Seach Sydney, Australia
The opening quotes character before the table name is missing. It should be:

If Not IsNull(DLookup("ProdCode", "Products", "ProductCode = """ & Me!ProductCode.Text & """")) Then


Ken Sheridan, Stafford, England

Man, how did I miss that? I checked and checked. Thanks Ken.

- Blind as a bat!

"Ken Sheridan" wrote in message news:*** Email address is removed for privacy *** .com...

The opening quotes character before the table name is missing. It should be:

If Not IsNull(DLookup("ProdCode", "Products", "ProductCode = """ & Me!ProductCode.Text & """")) Then


Ken Sheridan, Stafford, England


Regards, Graham R Seach Sydney, Australia

Thanks Ken and Graham. I'm going to go and do some formal training as now nothing happens and I still get the standard primary key duplicates message at the "end" of the record entry.

I'll keep this code for the time when I understand it all a little more.

Many thanks for your help with this.

Category:Office Time:2012-06-11 Views:1

Related post

  • Data entry only form, display problems. 2012-11-13

    I want 2 forms. One that you can only use to enter new data, the other you can look through records and find the one you want, but can't change anything. For the data entry only form, if i try to make it through the wizard, it sort of combines the tw

  • Data Entry Forms and Displaying Date Ranges 2012-12-07

    In Excel 2010, can I enter data in a start date and end date field and have my description of my project occurring during that time frame appear on a worksheet on a week by week basis Any guidance on this would be great! --------------Solutions------

  • Data entry in a tabular form 2014-11-25

    I have an Access 2010 tabular form which displays all records (in rows) and allows the user to make changes in those records. I also want the user to create new records. When I set the data properties to Data Entry = Yes, the form no longer displays

  • Data Entry=False 2014-06-18

    Hello, I have a command button called "EditForma" on a form called "frm_ListView" that I use to open a form called "Frm_Form Informe Mensual Page 1". When I click the "EditForma" button the form "Frm_Form Informe Mensual Page 1" usually opens in "Dat

  • My data entry form is showing false duplicates I can't delete 2014-06-27

    I have a form I use to view & add records to a single table. I imported data for one field into the table, and am now adding in all the extra information, but once I have completed a record, the form shows it twice or even three times. Each 'reco

  • Prevent multiple data entries in MySQL 2011-02-25

    So here is the deal. I have looked around everywhere, and all other techniques relate to refreshing the browser, and methods to prevent the php page from resubmitting the post data. I am new to this (obviously :p) But anyways, my questions I believe

  • Magento Backendgrid: Click on a data entry and display its data 2012-04-11

    I used this tutorial (especially Lesson 6 and 7) to create my own backend grid for Magento: http://www.pierrefay.com/magento-developper-guide-howto-tutorial-5 Everything works fine. I can create new data entries for my grid. If I click on an entry th

  • Data entry forms are not displaying correctly...How do I fix this? 2012-03-29

    Hi all! I am trying to create a 'menu' of sorts. I am starting with a blank form with two tabs. The tabs will have nothing but command buttons on them. One tab is for data entry and the other is for reports. Some of the command buttons are accessing

  • identify duplicate date entries for each client 2014-03-19

    I am trying to highlight when a client ID has a duplicate date entry. My data is as follows. Column A Column B Column C Client # Date Product 1 01/01/2012 A 2 01/01/2012 B 2 01/01/2012 C 2 02/01/2012 E 3 01/01/2012 A 3 01/01/2012 B 3 02/01/2012 C 3 0

  • Help me design a Silverlight App (specifically, the Master/Detail Data Entry Form) 2011-04-11

    I am looking for some Design/Architect advice for a Silverlight Application. This will be a TimeTicket (TimeSheet) application, where employees may go in and enter their daily time. We have an existing ASP .Net application and database that this new

  • MVVM WPF datagrid data entry problem 2011-08-01

    I have a datagrid in my MVVM application which, because of the way the client wants the data displayed, needs use template columns. They want some typical data-entry functionality though (pressing enter performs data checking, commits the row if vali

  • Office 2013 - Data Entry & Editing Delay 2012-04-13

    Hi team, I got an issue to install office 2013. The data entry and editing is delayed more than 5 minutes. I repaired a few times online and reinstalled 2 times, but it still does not work. My Dell laptop is the 64 bit version on windows 7, and I hav

  • In a data entry subform, how to show varying number of of new subform records linked to one parent record. 2012-10-15

    I've searched the forum using search terms dynamic subform records source, subform, form design, recordset, and I haven't been able to accomplish my goal. Using Access 2003 with windows XP pro, I'm modeling a process called titration. We have a group

  • Window Live Photo Gallery "An error is preventing the photo or video from being displayed" 2013-02-03

    I have just downloaded Windows Live. All of my pictures were imported, but if I click on one to edit it I get the error message that an error is preventing the photo or bideo from being displayed. I can run slide shows but I can not fix pictures or m

  • Help with desktop shortcut for data entry form for access 2007 2013-02-21

    Hello, Please help me! I need to know how to create a desktop shortcut to an Microsoft Access 2007 form which does not open MS Access. I want to put the shortcut on each staff member's desktop so they can open a form and enter data on the form and re

  • need a data entry form that can have more than 32 fields 2013-05-16

    I am trying to create a data entry form using Excel 2010. Using the built in data entry form is limited to 32 fields. I need 43 fields. I can create one with the UserForm found in the Insert menu, but I'm not familiar with VB for Excel. I have some k

  • Access 2010 - form for data entry - automatic conversion of text case 2013-06-12

    Access 2010 Windows 7 I have a data entry form. Most of the fields should be upper case. If someone enters one of those fields in lower case I don’t want them to have to re-enter the data in upper case, I want it to be automatically converted to uppe

  • What applications do you use for data entry and retrieval via ODBC? 2009-03-26

    What apps or tools do you use for data entry into your database? I'm trying to improve our existing (cumbersome) system that uses a php web based system for entering data one ... item ... at ... a ... time. My current solution to this is to use a spr

  • How to build dynamic data entry forms in a WPF application? 2009-04-24

    I'm planning a WPF application which will be able to create dynamic data entry forms (meaning the form gets the fields to display, their order, etc. from data in the database, not from the XAML) use the MVVM pattern if possible Here is how I plan to

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.496 (s). 13 q(s)